Backing up Master, MSDB and Model Databases
-
Greetings, I currently have a maintenance plan set up to backup the master, msdb and model databases and I have specified that the transaction logs be backed up as part of the maintenance plan. The master and msdb are in simple mode so transaction logs are not backed up but the model is. My questions,
1) Is it necessary to backup the model database?
2) What are the risks, if any of not backing up transaction logs for the model and msdb databases and if their are risks what is the recommendation for backup methodology for these two databases?
Thanks.
-
The most common recommendation is to do a full backup whenever a change is made to the system databases.
-
It is not necessary to have Backup of Model DB if you have not created/added any object to model DB that you wish to be part of every user DB you create in future.
In general you should create a job that run say once every day or week
and take full DB Backup of Master and MSDB DB.
This way your job is automated and you do not need to remember taking Backup when you make changes and feel safe in case you loose any jobs or in worst case master.
Hope this helps,
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com -
Thank you both for your replies. One more question. The master and msdb databases are in simple mode, I gather that is the default, so transaction logs will not be backed up. Is that ok or should I change it so I can back up transaction logs. I suspect not as the activity against these two databases is limited but I just want to confirm. Thanks.
-
No need for transaction log for MSDB DB.
When you make some major changes in Job etc.
To make sure that you do not loose that change
due to any accidental event before next scheduled full
DB, Just run the Master,MSDB Backup Job once.
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com -
Its always advisable to backup the system databases. It should be a part of disaster recovery plan. If something happens to your SQL Server in future, for which, you have to reinstall the server, then you can bring the server in the same state as it was previously, by restoring the system databases. However, I don't think that you need to change the recovery model of the system databases. Their full backup in a week or fortnight should be sufficient.
Regards
Pankaj
Pankaj Khanna
Database Administrator - SQL Server 2000Keep hope to keep you intact...
-
To add my two cents.
As everyone else stated, the system databases don't change much. Back them up less frequently. Maybe once a week, but no less than once a month. Any time you make a change to one of the system dbs, you should immediately back it up. Just do full backups, they don't change enough to do log backups AND you cannot do a log backup of the MASTER (per the BOL).
Master - holds a lot of system stuff, you want to do a full backup of this often just to make sure you do have the latest. I suggest once a week.
MSDB - this holds all your job information. How often do you create or change jobs? Backup once a month.
MODEL - this is just a 'template' database. Anytime you create a new db, the db is initially created from this database. Backup once and forget about it. If you make changes to this database, then do a full backup. (If you don't have change this from the default and you lose your databases, you can just reload this from the install disk).
TEMPDB - never back this up. Any time you shut down SQL Server and restart it (including stopping and starting the SQL Server services), this database is rebuilt from scratch (based on the Model db). There should NEVER be any databases/data/etc in this db that need to be permanent.
-SQLBill
-
Thanks to everyone who replied. I feel much more comfortable about my backup procedures for the system databases. Thanks.
-
When I arrived at a customer site the first time I discovered that their maintenance plan "job step 1" had been failing for the past month. All of the files appeared to be present; but there was a message in the log that the transaction logs for master and msdb could not be backup up - so the request was "ignored". There were no other failure messages anywhere, and as I said, all the files appeared to be present; but, none of the transaction logs had been purged per the maintenance plan. There were over 30,000 old logs that had to be manually purged.
The only thing I did to currect the problem was remove the master and msdb from the single maintenance plan... and create a new plan to back them up. The purging started working, and the failure of the job step disappeared.
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982 -
How do you determine how many logs are waiting to be purged? Thanks.
-
Good question!
If the plan was written to purge logs older than 3 days, I look for three logs after the last three backups, and logs earlier than that are no longer required - in fact, without the related backups, they are not usable for anything... hence, purged.
Actually, once I got the maintenance plan working, it would have purged them on its' own; but I got rid of them prior to fixing the plan.
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982 -
that's "the logs" not "three logs"... sorry. proof read then hit send
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply