SQL2005 Maintenance Plan Backup Of System Databases

[zoeythecat]

Hi All,

When I create a Maintenance plan to backup "All System Databases" the job fails each time. I really don't get any error other than the job failed. When I create a Maintenance Plan and choose "These Databases" and select the system databases I don't have any issues. The problem is I have multiple backup files that get created. I would prefer to have 1 backup file I can overwrite each day.

How is everyone backing up their SQL databases? Would I be just as well off backing up the system databases this way (by choosing the "These Databases" Method)?

Tia,
Zoey

 

[Catadmin]

The maintenance plan doesn't overwrite the last backup. Using DBMP will always create brand new files with a day/time "stamp" in the file name. If you want an overwritable file, you'll have to create a backup device and write a T-SQL job to do your backups. See MRDenny's FAQ (faq962-5722) on SQL Backups for more details if you don't know how to do this.

BTW, MRDenny's FAQ is written for keeping multiple copies, but you can easily edit it to use only one backup device. Books Online also has plenty of details on Backup Devices (use those keywords).

As far as failing goes, I've found in SQL 2000 that I've had to create an individual plan for Master separate from MSDB and Model in order to get the DBMP to work. I haven't had any problem like this with 2k5, though.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[zoeythecat]

Cat,

Thanks for all the info. I will just stick with the plan that is working and just monitor the folder and purge the backup files on a weekly basis. Thanks for the MRDenny FAQ link as well.

 

[Catadmin]

NP. Glad I could help.

BTW, DBMP should have an option for deleting those old backup files. Go back into it and tell it how long to keep the backups, then it should auto-purge itself.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[zoeythecat]

Thanks. Where abouts in the DBMP menu would I modify this option?

 

[Catadmin]

It should be on the Backup tab (in SQL 2000). And if you use Transaction Log backups, you'll want to change it there too. Remember, the Backup Tab choice will only affect your Main backups while the Transaction Log tab choice will only affect the Transaction Log backups.

In SQL 2005, you need to add a Maintenance Cleanup Task in order to delete old backup files.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[zoeythecat]

ok thanks

 

[Catadmin]

You're welcome. @=)


Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[zoeythecat]

Cat,

Update on this. I needed to create a backup device first. After I created the backup device (I am backing up to a folder on disk) I now have only 1 backup file that backs up the Master, Model and MSDB from a backup job.

Zoey

 

[Catadmin]

Glad you were able to get it working. @=)



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"