Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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, 
RECOVERY, 
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?

UPDATE:

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.

share|improve this question

migrated from stackoverflow.com Nov 28 '12 at 21:57

This question came from our site for professional and enthusiast programmers.

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 '12 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. –  Mark Storey-Smith Nov 29 '12 at 0:00

4 Answers 4

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

share|improve this answer
    
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 '12 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 '12 at 22:54

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
AS
BEGIN
SET NOCOUNT ON;

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
END
--============================
-- 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:

master.dbo.xp_delete_file
share|improve this answer
    
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 '12 at 16:30
    
I have just one bak file where I add a new backup everyday. The method will not work. –  lolol Dec 6 '12 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 */
DECLARE @ThreeDaysAgo VARCHAR(50)
SELECT @ThreeDaysAgo = CAST(DATEADD(d, -3, GETDATE()) AS VARCHAR)

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

share|improve this answer
    
"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 '12 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! :) –  Ali Razeghi Nov 29 '12 at 16:47
    
@AliRazeghi I have just one .bak file with multiple bakcups inside, so both methods will not work. –  lolol Dec 6 '12 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? –  Ali Razeghi Dec 6 '12 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.

share|improve this answer
    
I have just one backup file with multiple files inside. The method will not work. –  lolol Dec 6 '12 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 '12 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 '12 at 17:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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