Simple Script to Backup Database

  • texpic

    SSCertifiable

    Points: 5882

    Does anyone have a simple script to backup a single datbase?  Would like YYYYMMDDHHMM as part of the file name to make it unique.

    DatabaseName = ABC

    Backup Location = C:\BackUpSql\

    The simpler the better!  Thanks.

     

     

  • John Mitchell-245523

    SSC Guru

    Points: 148652

    You will have to mess with dynamic SQL and fiddly datepart functions.  It can be done, but if you want to keep it simple, probably best to use a database maintenance plan.

    John

  • texpic

    SSCertifiable

    Points: 5882

    I did that and for some reason the backup failed.  I'm trying again with a new plan, hope it works.

  • Aaron Ingold

    SSCrazy Eights

    Points: 9070

    I'd echo John here. Most people will want to use maintenance plans. Something like this should do it for you, but you'll still have to manage removing old backups, etc...

    DECLARE @filename NVARCHAR ( 500 )

    , @hour NCHAR ( 2 )

    , @min-2 NCHAR ( 2 )

    IF DATEPART(hh, GETDATE()) < 10

    SET @hour = '0' + CONVERT(NVARCHAR, DATEPART(hh, GETDATE()))

    ELSE

    SET @hour = CONVERT(NVARCHAR, DATEPART(hh, GETDATE()))

    IF DATEPART(mi, GETDATE()) < 10

    SET @min-2 = '0' + CONVERT(NVARCHAR, DATEPART(mi, GETDATE()))

    ELSE

    SET @min-2 = CONVERT(NVARCHAR, DATEPART(mi, GETDATE()))

    SET @filename = 'C:\BackUpSql\ABC_' + CONVERT(NVARCHAR, GETDATE(), 112) + @hour + @min-2 + '.BAK'

    BACKUP DATABASE ABC TO DISK = @filename

  • sindhigrl

    Default port

    Points: 1446

    You can use what Aaron has posted as your first step in your backup job and use this script as the second step to clean up old backups. This one will clean up anything older than two days. Great script that someone on this site gave me!

    SET NOCOUNT ON

    DECLARE @command VARCHAR(1000)

    DECLARE @currentfile VARCHAR(500)

    -- Create a temporary table

    CREATE TABLE #output (

     txtOutput VARCHAR(1000) )

    -- Get those database backups that are present on disk

    SELECT @command = 'dir X:\Backups\test_BACKUP_*.BAK /B'

    INSERT INTO #output (txtOutput)

    EXEC master.dbo.xp_cmdshell @command

    -- Delete unusful information

    DELETE #output

    WHERE txtOutput IS NULL OR

      txtOutput LIKE 'file not found' OR

      txtOutput LIKE 'test_BACKUP_' + CONVERT(VARCHAR,GETDATE(),112) + '.BAK'

      OR txtOutput LIKE 'test_BACKUP_' + CONVERT(VARCHAR,DATEADD(dd,-1,GETDATE()),112) + '.BAK'

     

    -- Delete old database backups

    IF ( SELECT COUNT(*) FROM #output ) > 0

    BEGIN

     DECLARE c_file CURSOR FOR

      SELECT txtOutput

      FROM #output

     OPEN c_file

     FETCH NEXT FROM c_file INTO @currentfile

     WHILE @@FETCH_STATUS >= 0

     BEGIN

      SELECT @command = 'DEL X:\Backups\' + @currentfile

      EXEC master.dbo.xp_cmdshell @command, no_output

      -- Report to what logfile that has been deleted

      PRINT 'DELETED FILE: X:\Backups\' + @currentfile

      FETCH NEXT FROM c_file INTO @currentfile

     END

     CLOSE c_file

     DEALLOCATE c_file

    END

    ELSE

    BEGIN

     -- Report if no files were deleted

     PRINT 'NO FILES DELETED'

    END

    Thanks!

  • MohammedU

    SSCoach

    Points: 19644

    What about without cursor...to deleted 3 days older  backup....

    select @deldate = getdate()- 3   

       

    WHILE @deldate <= (getdate()-@FileDelDays)   

       

    BEGIN   

     select @cmd = 'del  X:\Backups\test_BACKUP_*'+convert(char(8), @deldate , 112)+'*.trn /S '

     EXEC master.dbo.xp_cmdshell @cmd, no_output   

     select @deldate = @deldate+1   

    END   

     

    MohammedU
    Microsoft SQL Server MVP

  • Michael T

    SSC-Addicted

    Points: 469

    How's about:

    use ABC

    EXEC sp_updatestats

    GO

    DECLARE @rtn int

    EXEC @rtn = master.dbo.xp_sqlmaint N'-D ABC -WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDB -BkUpMedia DISK -BkUpDB C:\BackUpSql\ -DelBkUps 2DAYS'

    IF @rtn = 0

    print 'ABC Backed up.'

    ELSE

    print 'ABC Backup Error.'

    You can take out the updatestats and the error checking for pure simple.

  • Chris Foster-356495

    SSC Veteran

    Points: 214

    Or this nice SP? I created this in master. I use a scheduled VB script to handle old file removal. Works for me.

    Chris

    =========

    /*

    usp_BackupDB

    Backups up a database

    Creates a .bak file in the given path with the name of the database and a datestamp

    */

    CREATE procedure usp_BackupDB

    (

    @INP_DatabaseName varchar(100), --name of the db

    @INP_BackupPath varchar(160) = 'd:\ms-sql\projects\backup\' --where to store backup files (default value)

    )

    as

    --Get the ISO-formatted date (for use in BAK file suffixes)

    declare @ISONow char(8)

    set @ISONow = (select convert(char(10),getdate(),112))

    declare @BackupPath varchar(260)

    set @BackupPath = @INP_BackupPath + @INP_DatabaseName + '_' + @ISONow + '.bak'

    print 'Creating backup device: ' + @BackupPath

    --Start by adding a dump device

    exec sp_addumpdevice 'disk', 'TempDevice', @BackupPath

    --Do the backup

    BACKUP DATABASE @INP_DatabaseName TO TempDevice

    --Drop the device again

    exec sp_dropdevice 'TempDevice'

    GO

  • SQLGuy64

    Ten Centuries

    Points: 1051

    we only keep one backup on disk, the rest are on tape.  I find scheduled backups to a backup devices the way to go for this.

  • vipermkvii

    Valued Member

    Points: 71

    I used the code below to allow my payroll specialist to manually back up a database before she performs a payroll run. It works great but I was wanting to do one other thing to it...

    1. Is there a way I can turn this script into a shortcut on the desktop of my payroll specialist? I'm ultimately trying to make this a simple one-step back up for her. If it is a short cut, will she still need to have SQL client tools installed on her system? Currently I'm using SQL Server 2000 and the client computer is using Windows XP Pro sp2.

    Code currently being used:

    DECLARE @filename NVARCHAR ( 500 )

    , @hour NCHAR ( 2 )

    , @min-2 NCHAR ( 2 )

    IF DATEPART(hh, GETDATE()) < 10

    SET @hour = '0' + CONVERT(NVARCHAR, DATEPART(hh, GETDATE()))

    ELSE

    SET @hour = CONVERT(NVARCHAR, DATEPART(hh, GETDATE()))

    IF DATEPART(mi, GETDATE()) < 10

    SET @min-2 = '0' + CONVERT(NVARCHAR, DATEPART(mi, GETDATE()))

    ELSE

    SET @min-2 = CONVERT(NVARCHAR, DATEPART(mi, GETDATE()))

    SET @filename = '\\thrha-sql\apps\DatabaseBU\v32Live' + CONVERT(NVARCHAR, GETDATE(), 112) + @hour + @min-2 + '.BAK'

    BACKUP DATABASE v32Live TO DISK = @filename

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply