I have a job scheduled to run a few times everyday. The job backups my databases, like:

BACKUP DATABASE [databasename] 
TO DISK = N'd:\sqlserver\backup\databasename.bak' 

I can track the backups in the file by running:

RESTORE HEADERONLY FROM DISK = N'd:\sqlserver\backup\databasename.bak' 

And then I can restore a database file by using something like:

RESTORE DATABASE databasename 
FROM DISK = N'd:\sqlserver\backup\databasename.bak' 
WITH FILE = 1455, 
MOVE 'databasename_data' TO 'c:\...', 
MOVE 'databasename_log' TO 'c:\...'

But I have thousands of backups, and I'm reaching my storage limit.

Because I don't need so many backups, I'm wondering if there is a easy way to limit the backup file to keep only the last 90 backups.

Any tips?


I have just one .bak file with n backups inside. I can read a specific backup using the withfile option in the restore statement. The bellow answers are supposing I have multiple .bak files. What I need to delete is a outdated backup inside the .bak file.

  • 1
    You can't remove backups from the database file. You need to delete the file and create a new file for each backup. Then manage the number of backups that you keep on disk using the xp_delete_file system stored procedure. There's a couple of good examples in the answers on using this.
    – mrdenny
    Nov 28, 2012 at 22:55
  • 1
    You've probably got more productive work to be doing than writing scripts to manage backup files. Ola Hallengren, we thank you. Nov 29, 2012 at 0:00

4 Answers 4


The easiest solution would be using a maintenance plan task to clean up old backup files. However, you could consider scripting your way out of this, using something like the following script:

CREATE PROCEDURE [dbo].[usp_DeleteOldBackupFiles]
@path nvarchar(256),
@extention nvarchar(10),
@age_hrs int

DECLARE @DeleteDate nvarchar(50)
DECLARE @DeleteDateTime datetime

SET @DeleteDateTime = DateAdd(hh, -@age_hrs, GetDate())

SET @DeleteDate = (Select Replace(Convert(nvarchar, @DeleteDateTime, 111), '/', '-') + 'T' + Convert(nvarchar, @DeleteDateTime, 108))

EXECUTE master.dbo.xp_delete_file 0, @path, @extention, @DeleteDate, 1
-- xp_delete_file information
-- xp_delete_file actually checks the file header to see what type of file it is and will only delete certain types such
-- as database and log backups. I suppose they expanded this to certain types of log files as well but as you say this is
-- not documented by MS. Just be aware that it will not delete just any file type

-- First argument is:
-- 0 - specifies a backup file
-- 1 - specifies a report file
-- (I'm not sure what the difference between a "backup file" and a "report file" is, since you specify the extension of files
-- you're deleting with the third argument.)
-- Fifth argument is whether to delete recursively.
-- 0 - don't delete recursively (default)
-- 1 - delete files in sub directories

Credit: http://www.sqlservercentral.com/Forums/FindPost1034057.aspx

There's also xp_delete_file:

  • I will give Yugz solution a try because it is more simple, but the script works just fine. Thank you. I let you guys know about my test.
    – lolol
    Nov 28, 2012 at 16:30
  • I have just one bak file where I add a new backup everyday. The method will not work.
    – lolol
    Dec 6, 2012 at 17:06

I use a couple of different methods.

Method 1-

/* Deletes files written to over 14 days ago.  Ex: If today is the 18th, any files written on the 10th and before will be deleted */
/* 2nd part is for transaction logs */

--Remove full backups
EXECUTE master.dbo.xp_delete_file 0, N'X:\SQLBackupsFolder',N'BAK', @ThreeDaysAgo,1

--Remove TLogs
EXECUTE master.dbo.xp_delete_file 0, N'X:\SQLBackupsFolder\SQLTlogBackups',N'LOG', @ThreeDaysAgo,1

This is a unsupported command but it has been well documented by bloggers. In hindsight, I should probably change the @ThreeDaysAgo variable from NVARCHAR(50) to DATETIME2 but this isn't an app, just a simple script so I left it as is.

The other method I like to use in some environments is to have a script execute it. You can set it to be called from a job step so if it fails or hangs you're notified from SQL Server, or you could use windows scheduler.

Method 2-

forfiles /P "X:\Folder" /M *.* /D -16 /C "cmd /c del /q @PATH"

This particular one will go through the X:\Folder and delete anything that is over 16 days old. There are a lot of parameters so pick which one works best for you. You will need read/write access to the backup folder for the SQL Server Agent user if you plan on making this part of a scheduled job.

  • "This is a unsupported command but it has been well documented by bloggers" -- yes, and it's also been well documented to not work particularly well.
    – Jon Seigel
    Nov 29, 2012 at 14:53
  • xp_delete_file doesn't work if the file is altered in any way (such as being zipped), doesn't delete maintenance history plans (we have another sproc for that) and it didn't perform awesome in 2005. In 2008+ I've never had a problem with it. If someone does though, they should use FORFILES. I like both so far. XP_DELETE_FILE since I can call it without dropping to powershell or cmd_prompt, and for_files since I can put it in a job as well. Test test and always test again! :) Nov 29, 2012 at 16:47
  • @AliRazeghi I have just one .bak file with multiple bakcups inside, so both methods will not work.
    – lolol
    Dec 6, 2012 at 17:05
  • Hi lolol, if you are appending backups into 1 physical file then you're right, you will lose the granularity. Why append though? Dec 6, 2012 at 18:06

Personally, I'm a big fan of using Powershell for this kind of task. The basic command is simple enough (remove all .bak files older than 90 days):

dir X:\BackupFiles\*.bak | where {$_.LastWriteTime -lt (Get-Date).AddDays(-90) } | remove-item

You can get a lot fancier, like if everything is stored in subdirectories:

dir X:\BackupFiles\ -recurse | where {$_.extension -eq "bak" -and $_.LastWriteTime -lt (Get-Date).AddDays(-90) } | remove-item

You can fine tune your command and then set it up as an Agent job to periodically clean things out. Lots of more involved examples on the web.

  • I have just one backup file with multiple files inside. The method will not work.
    – lolol
    Dec 6, 2012 at 17:06
  • 1
    Understandable. Not sure how much control you have on backups and how they're performed, but I prefer writing each backup to a different file name. If that is a valid approach, the powershell script for file management would be a great way to go.
    – Mike Fal
    Dec 6, 2012 at 17:28
  • I'll move to this approach (thank you) but now I have to delete at least 1000 backups from my .bak to fix that file problem. I'm running out of space and I will not have space to keep 3 months single files and the big file.
    – lolol
    Dec 6, 2012 at 17:30

If you use the SQL Server Maintenance plan wizard to create your jobs to back up the databases you can set an Expiration date on your backup set.

click on the link below it explains hows its done.

Set Expiration Date on Backup set

  • If I can have a different expiration date for each file inside the .bak, this will do the job. I will try. Thank you.
    – lolol
    Nov 28, 2012 at 16:28
  • This won't actually purge the backups from the file. It will simply make it so that you can't restore the older backups.
    – mrdenny
    Nov 28, 2012 at 22:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.