March 27, 2003 at 5:45 am
do I need to backup system databases?
if yes, When ?
March 27, 2003 at 6:28 am
Yes, model isn't as important, but MASTER and MSDB need to be backed up. I do them once a month.
MASTER changes when there are users added, changes to schemas, etc. Whenever I make major changes to my databases, I not only backup the database(s) I changed, but also the Master DB.
MSDB contains all of your scheduled jobs. Anytime I create a new job I backup the MSDB DB.
So, unless I make changes to those databases, I back them up once a month.
-SQLBill
March 27, 2003 at 8:24 am
Yes!!, backup all the system database on schedulle and after making changes to them. If you also have replication, you should backup the distribution db frecuently too.
April 3, 2003 at 2:17 pm
Another point about MSDB. Not only does this contain all of your job schedules, it also contains your job History. If you use scheduled jobs to perform your backups but do not back up MSDB frequently, you will not be able to restore them if you have a failure.
I typically set the MSDB backup to occur nightly as the last full DB backup. That way, if a failure occurs later, the backup copy of MSBD will contain a complete set of the latest backup information. It typically is not a large database so disk space should not be a concern. If it is, you can set the scheduled jobs to delete old backups after 'n' days or weeks. Hope this helps.
My hovercraft is full of eels.
April 3, 2003 at 3:22 pm
I backup all the databases except model, Northwind and tempdb everyday. I daily bcpout the data of few system tables daily, from my SQL 6.5, 7.0 and 2000 Servers.
.
April 4, 2003 at 5:41 am
sswords,
you have mentioned
"delete old backups after 'n' days or weeks"
how can I delete old backups through a job. If the sheduled jobs append backup sets in to same backup device?
April 4, 2003 at 6:27 am
I backup up MSDB daily, I dont want to have to remember (or risk forgetting) to run a new backup after I change a job. Doesnt cost much, why not do it? I do backup distribution db, mainly because it's easier than not doing it with a maintenance plan. If you're using replication, restoring distribution db is COMPLICATED - I use the simpler alternative of just re-snapshotting everything if my distribution db goes down. I also delete Pubs, Northwind from production boxes.
Andy
April 4, 2003 at 8:57 am
Restoring the distribution DB is not so complicated if you keep the backups in syncro with the publisher db. I use the sync with backup option.
I have to do it once, (hope not again) and I have my replication up to date.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply