backup file with datetime stamp

  • How to create backup files with datatime stamps this is to retain 2 days backups on the disk

    BACKUP DATABASE [DBNAME] TO

    DISK = N'D:\BACKUPs\DBNAME\DBNAME_1.bak',

    DISK = N'D:\BACKUPs\DBNAME\DBNAME_2.bak',

    DISK = N'D:\BACKUPs\DBNAME\DBNAME_3.bak',

    DISK = N'D:\BACKUPs\DBNAME\DBNAME_4.bak',

    DISK = N'D:\BACKUPs\DBNAME\DBNAME_5.bak',

    DISK = N'D:\BACKUPs\DBNAME\DBNAME_6.bak',

    DISK = N'D:\BACKUPs\DBNAME\DBNAME_7.bak',

    DISK = N'D:\BACKUPs\DBNAME\DBNAME_8.bak'

    WITH RETAINDAYS = 2,NOFORMAT, NOINIT,

    NAME = N'DBNAME-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10

    GO

    Thanks!

  • DECLARE @sqlcmd nvarchar(4000)

    SELECT @sqlcmd = '

    BACKUP DATABASE [DBNAME] TO

    DISK = N''D:\BACKUPs\DBNAME\DBNAME_1_' + CAST(CAST(getdate() AS DATE) AS NVARCHAR(100)) + '.bak'',

    DISK = N''D:\BACKUPs\DBNAME\DBNAME_2_' + CAST(CAST(getdate() AS DATE) AS NVARCHAR(100)) + '.bak'',

    DISK = N''D:\BACKUPs\DBNAME\DBNAME_3_' + CAST(CAST(getdate() AS DATE) AS NVARCHAR(100)) + '.bak'',

    DISK = N''D:\BACKUPs\DBNAME\DBNAME_4_' + CAST(CAST(getdate() AS DATE) AS NVARCHAR(100)) + '.bak'',

    DISK = N''D:\BACKUPs\DBNAME\DBNAME_5_' + CAST(CAST(getdate() AS DATE) AS NVARCHAR(100)) + '.bak'',

    DISK = N''D:\BACKUPs\DBNAME\DBNAME_6_' + CAST(CAST(getdate() AS DATE) AS NVARCHAR(100)) + '.bak'',

    DISK = N''D:\BACKUPs\DBNAME\DBNAME_7_' + CAST(CAST(getdate() AS DATE) AS NVARCHAR(100)) + '.bak'',

    DISK = N''D:\BACKUPs\DBNAME\DBNAME_8_' + CAST(CAST(getdate() AS DATE) AS NVARCHAR(100)) + '.bak''

    WITH RETAINDAYS = 2,NOFORMAT, NOINIT,

    NAME = N''DBNAME-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10

    GO'

    --PRINT @sqlcmd

    EXEC (@sqlcmd)

    MCITP SQL 2005, MCSA SQL 2012

  • Thanks a lot 🙂

  • In addition, attached script also can be handy for backup and restore. However little modification is required as per the requirement. This is for log backup you can also use it for full backup.

    http://www.sqlservercentral.com/scripts/Backup+%2f+Restore/71918/

    http://www.sqlservercentral.com/scripts/Backup+%2f+Restore/72321/

    HTH

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • Sqlsavy (10/28/2013)


    How to create backup files with datatime stamps this is to retain 2 days backups on the disk

    BACKUP DATABASE [DBNAME] TO

    DISK = N'D:\BACKUPs\DBNAME\DBNAME_1.bak',

    DISK = N'D:\BACKUPs\DBNAME\DBNAME_2.bak',

    DISK = N'D:\BACKUPs\DBNAME\DBNAME_3.bak',

    DISK = N'D:\BACKUPs\DBNAME\DBNAME_4.bak',

    DISK = N'D:\BACKUPs\DBNAME\DBNAME_5.bak',

    DISK = N'D:\BACKUPs\DBNAME\DBNAME_6.bak',

    DISK = N'D:\BACKUPs\DBNAME\DBNAME_7.bak',

    DISK = N'D:\BACKUPs\DBNAME\DBNAME_8.bak'

    WITH RETAINDAYS = 2,NOFORMAT, NOINIT,

    NAME = N'DBNAME-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10

    GO

    Thanks!

    I can't tell you when it will happen nor what the circumstances will be but there will come a day when you find out that it's a real mistake to automatically delete backup files automatically or even by date. I strongly recommend keeping at least 1 backup file for each of two days even if those files are a week old. For example, we went through a move from one building to another. The servers were down from 5PM on a Friday night 'til 5AM the following Monday morning. If we had a maintance plan or automatic deletes that simply deleted anything older than two days and had a backup failure on a corrupt database, we would have had to spend the day recalling a backup tape or two from offsite remote storage.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Is it possible to delete old backups using SQL backup script with RETAINDAYS ?

  • Sqlsavy (11/4/2013)


    Is it possible to delete old backups using SQL backup script with RETAINDAYS ?

    No and apologies for the confusion on that.

    Normally when I see folks with a RETAINDAYS limit set during the backup creation, they also have something that automatically deletes data sometimes even before a new and viable backup has been made. That's what I was warning against and failed to be clear on that subject.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

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