SQL Server 2005 backups for previous 5 days on disk?

Question

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?

Answer 1

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.

Answer 2

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

Answer 3

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.