[B] Transact-SQL Question related to SQL Server Backups [/b] 1

[Apollo21]

Good Morning or Afternoon Everyone!
I'm running SQL Server 2000(SP4) on an MS 2003 Operating system. My goal is to automate a backup methodology on some SQL servers we have at our site. I have most of it completed. however, I have a large part implemented through the maintenance plans. I have read that that is not the optimal method for implementing my strategy (posts here and other readings from the Net). I have written some simple code to execute a number of differential backups in a day, added this to a job and now am executing the job the required number of times a day with the correct interval. I am writing these backups using the Disk= Option. What I want to do next is to have a way of rolling from one day to the next with a new "disk=" file. The original one is hard coded and I have it appending to the previous differential backup. I execute a full backup every morning around 2:00 A.M.. How can I limit/expire the current file= process at the end of a 24 hour period and bring in a new one ???

Following is my Code:

BACKUP DATABASE XXXXX2000
TO Disk = 'D:\mssql\(local) Instance\DifferentialTarget\XXXXX2000Differential.bak'
WITH DIFFERENTIAL,
NOINIT

Any help would be great!
Thank you in advance!

Apollo21

 

[mrdenny]

You can do this by using a variable for the file name.

declare @filename varchar(2000)
set @filename = 'D:\MSSQL\MSSQL$Instance\DifferentialTarget\' + convert(varchar(10), getdate(), 112) + '2000Differential.bak'
backup database Yourdatabase to disk=@filename
with differential, init

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Apollo21]

Thank you Mr. Denny, it works very well.

 

[mrdenny]

No problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com