Post reply

Possible to add a timestamp within the BACKUP command?

  • December 18, 2007 at 8:28 am

    #182143

    Our normal SQL Job is to run the backup nightly for our smaller databases

    Something similar to this code

    BACKUP DATABASE LcsLog

    TO DISK = '\\HQ-NAS1\SQLBACKUP$\HQ-APPS2\Data\LcsLog.bak'

    WITH FORMAT

    RESTORE VERIFYONLY FROM DISK =N'\\HQ-NAS1\SQLBACKUP$\HQ-APPS2\Data\LcsLog.bak'

    My question is on the TO DISK entry it would be better if I could do dbname[getdate].bak

    Thus the .bak file is not replacing the prior .bak file nightly (we do have redundancy within our SAN and our tape backups so we have that part covered)

    This would be more cleaner and more accurate.

    Thanks

  • December 18, 2007 at 10:08 am

    #761127

    Use dynamic SQL to generat the backup with a variable to house the date.

    e.g.

    DECLARE @SQL NVARCHAR(200), @dt VARCHAR(20)

    set @dt = rEPLACE(convert(varchar,getdate(),101),'/','')

    set @SQL = N'

    BACKUP DATABASE LcsLog

    TO DISK = ''\\HQ-NAS1\SQLBACKUP$\HQ-APPS2\Data\LcsLog' + @dt + '.bak''

    WITH FORMAT

    RESTORE VERIFYONLY FROM DISK =N''\\HQ-NAS1\SQLBACKUP$\HQ-APPS2\Data\LcsLog' + @dt + '.bak''

    '

    execute sp_executesql @SQL


    My blog: http://jahaines.blogspot.com

  • December 18, 2007 at 10:47 am

    #761143

    Adam,

    Thanks for the help works like a charm!

  • December 18, 2007 at 11:24 am

    #761165

    Sweet. Thanks for letting me know everything worked out. 🙂


    My blog: http://jahaines.blogspot.com

Viewing 4 posts - 1 through 3 (of 3 total)

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