Sql-server – SQL Server 2005 backups for previous 5 days on disk

sqlsql serversql-server-2005

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?

Best Answer

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.

Related Topic