SQL SERVER 2000 DIFFERENTIAL BACKUP QUESTION 1

[Apollo21]

I am currently backing up a series of databases utilizing full backups, log backups and differential backups. I have used the maintenance plans to create the full and log backups. With the differentials I created a job with the following code.

BACKUP DATABASE XXX
TO DISK=@filename
WITH DIFFERENTIAL,
DESCRIPTION = @description,
RETAINDAYS = @retaindays,
NOINIT

Retain days is equal to 2 in case you are wondering.
The full and log backups put out a separate file every time they run (not using a backup device). But what I am doing with the differential backups creates a backup device for each day and puts the backups onto the same device for that day.
The issue is that I do not know how to limit the number of these devices or is that not possible?

There must be some way to have a job that deletes these devices if they are so many days old.. just like in the maintenance plans. Would someone offer some instruction or code to help me out?

The other way of looking at this is to make the differential backup put out a regular file instead of going to a backup device.

Thank you in advance for your efforts,
Apollo21

 

[mrdenny]

What are you setting to the @filename variable to. If you change the filename each time you do a differential backup you will only get one backup per file.

Is that what you are asking? If not I'm not sure that I understand the question.

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]

Hi Denny, thank for giving this a try. Let me try to explain.

If I am only changing the file name once a day (automatically), then I have them going into the same device for that day. That is ok, but I want to know if there is a way for me to limit the number of those daily file devices. If five days go by I have five backup devices. I don't need the last two. Can I eliminate them automatically?

Is that better?

 

[Apollo21]

By the way my file name starts off with todays date like '20080831 Test Differential Bkups.bak'

 

[mrdenny]

Ah, how to delete the files.

With SQL 2000 there isn't much you can do from within the T/SQL. SQL 2005 gives you some nice stored procedures to handle all this.

The easiest way to do it is kind of a hack, but it does work. Go grab a copy of robocopy (google for it, it's part of the resource kit but tons of places have it available for download). Use it to move all files older than 2 days to a temp folder. Then delete all the files in the temp folder.

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