Differential Backup with RETAINDAYS

I am looking into saving differential backup files for X number of days:

I used the wizzard with RETAINDAYS here is the syntax:

BACKUP DATABASE [Northwind] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\N' WITH NOUNLOAD , RETAINDAYS = 2, DIFFERENTIAL , NAME = N'n', SKIP , STATS = 10, FORMAT

When I run it overrides the first file.

My question is should I rename the differential backup file and save it in a different folder. If so how do I do that?

Or do you know of a script that will retain a differential backup for x number of days.

Or do you know of a better solution.

Thanks in advance

Anu AhujaHi Anu,

FYI: The Database Maintenance Wizard does the same for you for Full and Transaction Backups. But for Differential Backup , you have to write a script.

The script below does the backup of the currentdatabase by adding dateand time along with databasename. So this will backup the database with new name everytime.

Example: NorthwindAug231040.bak

/* Script to Backup the database with Date and Time */
/* Owner : Varad01 */
/* Try this script first with Pubs database */

Declare
@.CurrentDateTime varchar(20),
@.dbname varchar(20),
@.dbbackupname varchar(40)
Begin

Select @.dbname=db_name()

Select @.CurrentDateTime =
substring(DATENAME(month, getdate()),1,3) +
cast(DATEPART(day, GETDATE()) as varchar(2))+
cast(DATEPART(hh, GETDATE()) as varchar(2)) +
cast(DATEPART(mi, GETDATE()) as varchar(2))

Select @.dbbackupname = @.dbname+@.CurrentDateTime

select @.dbbackupname ='C:\' + @.dbbackupname + '.bak'

backup database @.dbname to disk=@.dbbackupname WITH NOUNLOAD , RETAINDAYS = 2, DIFFERENTIAL, SKIP , STATS = 10, FORMAT

End

Hope this Helps.

Have Fun :)

Varad01
MCDBA,MCSE|||Thank you very much!!!

Anu:)|||Hello again,

The above script is working great, I am able to create a unique name for the differential backup. BUT ......I would like the backup to be retained for 3 days and that is not working.

Do I add a delete?

Thanks in advance.

Anu:confused:|||Hi!

Just a suggestion... why don't you create three separate folders for your three-day retention backup files. With this, you can safely specify different destination paths for each execution. Use the script below:

/* Script using different destination folders per day*/
/* Created by Boysie Jocson, Manila, Phils. */

declare @.day_week int,
@.directory char(80)
set @.day_week = datepart(dw,getdate())
if @.day_week = 1
begin
set @.directory = '\\servername\day1\db_1stdiff.bak'
end
else if @.day_week = 2
begin
set @.directory = '\\servername\day2\db_2nddiff.bak'
end
else if @.day_week = 3
begin
set @.directory = '\\servername\day3\db_3rddiff.bak'
end

BACKUP DATABASE [dbname] TO DISK = @.directory WITH INIT , NOUNLOAD , DIFFERENTIAL , NAME = N'db_diff', SKIP , STATS = 10, DESCRIPTION = N'db differential backup', NOFORMAT

... hope this helps.|||Thank you!! This is working.

Anu|||Thank you!! This is working.

Anu:)|||Originally posted by anu
Thank you!! This is working.

Anu:)

I'm happy to hear that. You're welcome!