Post reply

Howto retain few backups and delete old backup files

  • April 27, 2020 at 5:47 am

    Go to Answer

    #3746505

    Hi,

    I have created a dynamic backup script to take backup for 2 databases.

    something like as follows:

    use master

    go

    declare @dbname varchar(100)

    set @dbname = 'db1'

    declare @cmd varchar(1000)

    set @cmd = 'BACKUP DATABASE '+@dbname+' TO DISK = ''C:\backup\'+@dbname+'_FULL'+'_'+convert(char(8),getdate(),112)+'_'+REPLACE(CONVERT(VARCHAR(8),GETDATE(),108),':','')+'.BAK'' WITH INIT, STATS =5, BUFFERCOUNT = 100;'

    print @cmd

    EXEC (@cmd)

    go

    declare @dbname varchar(100)

    set @dbname = 'db2'

    declare @cmd varchar(1000)

    set @cmd = 'BACKUP DATABASE '+@dbname+' TO DISK = ''C:\backup\'+@dbname+'_FULL'+'_'+convert(char(8),getdate(),112)+'_'+REPLACE(CONVERT(VARCHAR(8),GETDATE(),108),':','')+'.BAK'' WITH INIT, STATS =5, BUFFERCOUNT = 100;'

    print @cmd

    EXEC (@cmd)

    go

    I have a created a SQL Agent Job to take these backups weekly once over the weekend.

    Now, my requirement is , I want to retain latest 2 backups and delete the remaining old backups. How can we implement it programmatically.

    Regards,

    Sam

  • April 27, 2020 at 9:32 am

    Answer

    #3746561

    If you want to do it the way Microsoft do it, take a look at xp_delete_file, but that only deletes .bak and .trn files so you need to ensure you do the file extensions correctly.

    You could also write a PoSh script to do it for you as the xp command is an undocumented procedure so MS reserve the right to change its behaviour which may introduce breaking changes to your environment.  Again if you do a search engine for "xp_delete_file" you will get a number of alternative methods.

     

    But as a side note, any reason you've gone to writing this yourself instead of using maintenance plans or Ola or Minion Ware etc?

  • April 27, 2020 at 1:47 pm

    #3746642

    Thanks Anthony

  • April 27, 2020 at 3:50 pm

    #3746711

    vsamantha35 wrote:

    Now, my requirement is , I want to retain latest 2 backups and delete the remaining old backups. How can we implement it programmatically.

    Just a reminder about something that a lot of people forget about... NEVER delete files until the latest backup has succeeded.

    --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)

  • April 27, 2020 at 4:58 pm

    #3746741

    And keep 2 full backups.

    My recommendation is that you don't do this yourself and use Ola's script: https://www.sqlservercentral.com/scripts/sql-server-backup-integrity-check-index-and-statistics-maintenance

  • April 29, 2020 at 6:13 am

    #3747224

    Thank you Jeff and Steve.

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

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