Post reply

Script to delete old bak files in sql server 2008

  • October 20, 2010 at 1:16 am

    #388046

    Hi,

    I have the database backups in a backup drive as below. The full backup runs at 11 PM

    full: M:\Backups\full

    diff:M:\Backups\diff

    log: M:\Backups\log

    and I want to keep 2 days backups on M:\Backups\full and on 3rd day, when backup job runs , it should delete the 2nd day bak files and then perform backups. similarly for differential & log backups.

    thanks

  • October 20, 2010 at 1:48 am

    #1239506

    Use maintenance plans. I think the task you need is called Maintenance Cleanup.

    John

  • October 20, 2010 at 2:00 am

    #1239510

    or build the date into the filename of the backup file. Much easier than trying to get the "modify date" from xp_cmdshell

  • October 20, 2010 at 6:00 am

    #1239609

    Like John said, make use of the Cleanup Task in Maintenance Plan. It is very easy to accomplish

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • October 20, 2010 at 12:48 pm

    #1239865

    Like John said, make use of the Cleanup Task in Maintenance Plan. It is very easy to accomplish

    But in my case, I need to create 3 maintenance plan task to delete the backup files right?

    1st maintenance plan task to delete bak files from M:\Backups\Full

    2nd maintenance plan task to delete bak files from M:\Backups\Diff

    3rd maintenance plan task to delete trn files from M:\Backups\Log

    Is there way to use any t-sql script instead of maintenance plan task?

    Thanks

  • October 20, 2010 at 1:27 pm

    #1239884

    You COULD use one maintenance plan with 3 tasks inside, but yes, you will need three separate tasks. T-SQL is not appropriate for manipulation of files; you almost would need xp_cmdshell for that. Maintenance Plans are built on SSIS, and that is optimized for SQL to use to manipulate files. That way you won't have to enable any command prompt utilities to accomplish the same thing.

  • October 20, 2010 at 2:34 pm

    #1239941

    What's wrong with the extended stored procedure that MS supply??

    --for first parameter (0) = FileBackup and (1) = FileReport

    -- 1 at the end means include first level subfolders in the delete

    declare @deldate datetime

    set @deldate = convert(nvarchar(20), dateadd(day, -3 , getdate()),120)

    --delete backup files from specified folder including sub folders

    --change BAK to either TRN or DIF to remove other file types

    EXECUTE master.dbo.xp_delete_file 0,N'E:\SQL_Backups\INST1\',N'BAK',@DelDate, 1

    --delete txt files from folder specified including sub folders

    EXECUTE master.dbo.xp_delete_file 1,N'X:\MSSQL10.CAREDEV\MSSQL\Backup',N'txt',@deldate,1

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • October 20, 2010 at 2:37 pm

    #1239943

    I thought that we were supposed to stop using the xps as they were going to be deprecated....?

  • October 20, 2010 at 3:57 pm

    #1239984

    jeff.mason (10/20/2010)

    I thought that we were supposed to stop using the xps as they were going to be deprecated....?

    Ha...it will depreciate as soon as Microsoft writes another stored procedure to do the work 😀

    Shawn Melton
    Twitter: @wsmelton
    Blog: wsmelton.github.com
    Github: wsmelton

  • October 20, 2010 at 4:19 pm

    #1239992

    jeff.mason (10/20/2010)

    I thought that we were supposed to stop using the xps as they were going to be deprecated....?

    Well it's still there in SQL Server 2008 and 2008 R2, can't see it disappearing anytime soon!

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • October 21, 2010 at 1:34 am

    #1240088

    As far as I know, xp_delete_file and others like it are undocumented, not deprecated. That means you should take care when using them since they could be deprecated or have their syntax or functionality changed at any new release or even service pack.

    John

  • April 13, 2011 at 11:54 am

    #1311221

    They don't work!!!!

  • April 13, 2011 at 12:09 pm

    #1311229

    Does anyone know when this will be resolved? I heard we have to wait till Denali full release, of which I don't think we will be upgrading to for some time if at all! I use the following: (it works sometimes and others it will not delete anything, then the next days run it works)...

    DECLARE @currentdate datetime

    DECLARE @olddate datetime

    set @currentdate = CURRENT_TIMESTAMP

    set @olddate = @currentdate - 1 -- Change number here depending how many days of backup file you want to retain on server.

    -- Add below line for each database.

    EXECUTE master.dbo.xp_delete_file 0,N'E:\Backup\SQL_INDEX_REBUILD',N'bak',@olddate,1

    :unsure: So every night I have to check to make sure this ran correctly (no errors are produced), this is just FUBAR.

  • April 13, 2011 at 11:41 pm

    #1311426

    HI,

    You can add a step to your daily backup job such that

    1)first step will delete old backup file based on ur need (days).

    you can use Xp_cmdshell command or xp_delete command as mentioned above,

    Then upon successfull completion of step 1 move to backup step.

  • April 14, 2011 at 8:36 am

    #1311648

    Thanks,

    But we always backup first to make sure we have a good and verified backup before we delete anything... I have been thinking about the xp_cmdshell also, thanks for the input... Does anyone know when this will be resolved or if it ever will?

Viewing 15 posts - 1 through 15 (of 23 total)

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