Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

My goal is simple. I want to have SQL Server keep a nightly backup of a DB for a rolling 5 days.

So each night at some time I want a new DB backup made and one deleted such that I have a rolling 5 days on disk.

I am trying to get a maintenance plan set up to handle this work and have the saving of the file done (I think). I have the backups being appended so a single bak file will contain the rolling 5 days (not sure this will work 0 cause how will SQL know what to delete, and deleting the single bak file will remove all the backups).

As you can tell why am having problems figuring out how to remove older backups fo the DB so that Y have only the most recent 5 on disk.

Any tips for me on this?

share|improve this question

migrated from stackoverflow.com Oct 19 '10 at 20:33

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

add comment

3 Answers

up vote 0 down vote accepted

Maintenance plans as suggested are a good way forward. One alternative is to use a scheduled job that calls a stored procedure like this one;

CREATE PROCEDURE backup_all_databases
@path VARCHAR(255)='c:\backups\'
AS
DECLARE @name VARCHAR(50) -- database name
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
DECLARE @dbIsReadOnly sql_variant -- is database read_only?
DECLARE @dbIsOffline sql_variant -- is database offline?

DECLARE db_cursor CURSOR FOR

SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('tempdb') AND version > 0 AND version IS NOT NULL
OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0 
BEGIN
SET @fileName = @path + @name + '.bak'  -- + '_' + @fileDate + '.BAK'
SET @dbIsReadOnly = (SELECT DATABASEPROPERTY(@name, 'IsReadOnly')) -- 1 = Read Only
SET @dbIsOffline = (SELECT DATABASEPROPERTY(@name, 'IsOffline')) -- 1 = Offline
IF (@dbIsReadOnly = 0 OR @dbIsReadOnly IS NULL) AND @dbIsOffline =0
BEGIN
BACKUP DATABASE @name TO DISK = @fileName  WITH INIT
END
FETCH NEXT FROM db_cursor INTO @name 
END
CLOSE db_cursor
DEALLOCATE db_cursor
GO

Adapt it to append a timestamp to the backup file name. And add any expiredate or retaindays parameters you require. Like BOBS suggested, you can also use a maintenance clean up task to clear out older backup files than 5 days. This gives you individual backups for each day. You can run it hourly or x number of times during the day or once a day, whatever you need.

share|improve this answer
add comment

If you are using Maintenance Plans, there is a "Maintenance Cleanup Task" that you can add to your plan that is used for cleaning up BAK files after so many days. Just put this task after your "Back Up Database Task" and configure it to delete backup files older then 5 days, and you should be good to go.

HTH, Dan

share|improve this answer
add comment

In the TSQL BACKUP command there are two backup-set options that can be set. There is EXPIREDATE to indicate the date the backup expires and RETAINDAYS to indicate number of days to keep the backup. You would use the RETAINDAYS options and set it to 5 days. The backup process will do the rest.

In the maintenance plan setup, you should see similar options when defining the database backup task. You can indicate how long to keep the backup.

There isn't an option in maintenance plans to indicate number of backups to save.

share|improve this answer
add comment

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.