June 21, 2012 at 11:58 am
I am convinced that I need to backup my master and msdb databases, but what other databases should I backup as well?
After doing a little bit of research I have come to the assumption that I don't need to backup my model database. My reasoning is that I don't believe we ever modified it, and therefore it is vanilla. This then leads me to believe that if I were to lose/restore a database server that the newly installed model database would match the old one. In fact, MS says that you don't need to backup this database if you have never updated it.Is my reasoning sound? Furthermore, is there a way to check if it was modified in case I am incorrect? Am I missing anything?
Then there are tempdb and mssqlsystemresource databases. As for tempdb, I am pretty sure that is just space for operations and therefore a backup is not needed, but what about mssqlsystemresource? Does this contain anything worth holding onto? After all wouldn't MS want us to see it if it was worth backing up?
June 21, 2012 at 12:05 pm
something to keep in mind: system databases cannot be restored from previous versions or servicepacks;(regular databases are no problem)
so say you backed up master, msdb and model in RTM (release to market base version), then upgraded to SP2;
if any of those db's get corrupted, you cannot restore from those previous backups; after an upgrade you need to do a fresh set of backups that match the exact version.
so if model got corrupted, you'd have to get a model from another instance with the same service packs applied (and maybe CU's as well...i'd have to double check on that)
also on bootup, isn't tempdb born as a copy of model?
it might be a pain to track something like that down (especially if you are in an environment with out too many servers!)
Lowell
June 21, 2012 at 12:09 pm
Back model up (and also I often take copies of its data and log files). Otherwise recovering from a corrupt model is an absolute pain.
It doesn't need to be backed up regularly if you never change it, just have a valid backup as of the last time a service pack/hotfix/cumulative update was installed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 21, 2012 at 12:10 pm
In every instance I create a maintenance plan that backs up ALL databases. This includes system and user databases. This way if/when user databases are created they will automatically backed up everynight. This backs up master, model and msdb. They are generally very small so I dont really worry about whether or not to back up. mssqlsystemresource is located on installation drive, usually c:. These can be backed up with OS disk backup.
John
June 21, 2012 at 3:12 pm
backup master, msdb and model. Tempdb and resource database cannot be backed up. Tempdb is recreated on server restart so no point even if you could back it up.
Take a flat file copy of the resource database files on every upgrade, they are files on disk so can get corrupted. It certainly does contain data worth holding onto, it contains all the system metadata and SQL will not operate without it.
I also take flat file copies of the master, msdb and model database files on each upgrade, makes recovering from the loss of those databases so much easier.
---------------------------------------------------------------------
June 22, 2012 at 7:19 am
Thanks all, I will backup the model as well since it seems like it won't hurt. I will handle that backup manually since we are not modifying it. If I ever find the urge to modify it I will take another backup.
george sibbald (6/21/2012)
Take a flat file copy of the resource database files on every upgrade, they are files on disk so can get corrupted. It certainly does contain data worth holding onto, it contains all the system metadata and SQL will not operate without it.
I assume you are just copying the mssqlsystemresource files and that's it? Then if and when you need to restore you just copy it back in.
June 24, 2012 at 8:25 am
I assume you are just copying the mssqlsystemresource files and that's it? Then if and when you need to restore you just copy it back in.
correct
---------------------------------------------------------------------
June 25, 2012 at 11:37 am
The other point is that it is so small it is easier to do daily backups with master and msdb anyway. Then you know you have current backups.
The other point to note, that a previous poster stated is very valid. Any service packs or CU updates to SQL Server you will need a backup of anyway because you cannot restore an older version of it if you need to.
June 25, 2012 at 12:15 pm
The model database is usually so small that you should just back it up at the same time as the other system databases. My preference is to ceate a maintenance plan to do a full backup of the system databases every day, and keep them for a week
Better to have a backup you don't need than to need one you don't have.
June 26, 2012 at 1:12 am
Lowell (6/21/2012)
you'd have to get a model from another instance with the same service packs applied (and maybe CU's as well...i'd have to double check on that)
Same collation too 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 26, 2012 at 8:18 am
I generally structure backups to handle all SIMPLE recovery model databases one way, all FULL and BULK_LOGGED recovery model databases another way, Master (which gets Full backups whenever SIMPLE databases get Differentials) by itself, and then special handling for special databases (rare). Tempdb is always excluded.
Unless you're very, very space or time constrained, back up everything except tempdb and databases you know you deliberately don't want to (they're restored from another server's backups on a frequent schedule and any changes have no value, etc.)
On most of my servers, there's several tiny databases (including master, msdb, and model), a few small ones, and perhaps one large one, which most often is the most critical. Thus, any exceptional rules for the large one that require more frequent backups have only a marginal cost to apply them to all other like databases (backing everything up more frequently takes little more time or space than backing only the big one up more frequently), especially if it's in the FULL recovery model, since T-Log backups don't back up the same data over and over like Full or Differential backups do.
June 26, 2012 at 8:52 am
Unless there is absolutely another reason to do otherwise, which i have to this point i have yet to find in the 10+ years have been doing SQL...
- One maintenance plan to do Full Backups on ALL databases on a daily basis. This
will do system and user databases. When a new database is added it will be
automatically backed up and to the same place as the others. Have a plan as
to how many days to keep and use maintenance plan to delete anything older
than that.
- One maintenance plan to do recurring Translog backups (say every hour). Set
this to ALL databases. It will skip databases in SIMPLE mode. Keep as many days
as the Full Backups.
- Another maintenance plan to do other tasks (say weekly), like check database
integrity, clean up history (very important. have seen performance hits from
leaving years of history data), reorg indexes, statistics.
Be sure to monitor that these jobs are running and are successful by using databasemail and having a way (system monitor) to alert if the SQLAgent service is not running.
Keep it simple until absolutely have to do otherwise.
John
June 26, 2012 at 9:01 am
UconnDBA (6/26/2012)
Unless there is absolutely another reason to do otherwise, which i have to this point i have yet to find in the 10+ years have been doing SQL...- One maintenance plan to do Full Backups on ALL databases on a daily basis. This
will do system and user databases. When a new database is added it will be
automatically backed up and to the same place as the others. Have a plan as
to how many days to keep and use maintenance plan to delete anything older
than that.
- One maintenance plan to do recurring Translog backups (say every hour). Set
this to ALL databases. It will skip databases in SIMPLE mode. Keep as many days
as the Full Backups.
- Another maintenance plan to do other tasks (say weekly), like check database
integrity, clean up history (very important. have seen performance hits from
leaving years of history data), reorg indexes, statistics.
Be sure to monitor that these jobs are running and are successful by using databasemail and having a way (system monitor) to alert if the SQLAgent service is not running.
Keep it simple until absolutely have to do otherwise.
John
Maintenabce plans are good for small databases or when you find yourself an Accidental DBA having to get something into place. I used them when I first started working with SQL Server but after a bit I found them limiting. I started writing my own processes so that I could have more control over the processing, particularly the rebuilding or reorganization of indexes. I also wanted more control over scheduling the backups of databases and not having them all in one job (or maintenance plan), especially when I had databases with different recovery models and recovery requirements.
June 26, 2012 at 12:41 pm
Lynn Pettis (6/26/2012)
Maintenabce plans are good for small databases or when you find yourself an Accidental DBA having to get something into place. I used them when I first started working with SQL Server but after a bit I found them limiting. I started writing my own processes so that I could have more control over the processing, particularly the rebuilding or reorganization of indexes. I also wanted more control over scheduling the backups of databases and not having them all in one job (or maintenance plan), especially when I had databases with different recovery models and recovery requirements.
I couldn't agree more. The devs before me used maintenance plans, but I am actually planning on getting away from them. My biggest gripe so far is that if I modify a job associated with a maintenance plan (like adding a step to invoke a script to compress and copy backups) then I went back and modified the maintenance plan, I would find that the maintenance plan would restore the job to how it was before I made my changes to the job. Maybe there is a better way to do this, but right now I am just trying to stabilize everything. Plus, it just works.
June 26, 2012 at 3:43 pm
D. Clark (6/26/2012)
I couldn't agree more. The devs before me used maintenance plans, but I am actually planning on getting away from them. My biggest gripe so far is that if I modify a job associated with a maintenance plan (like adding a step to invoke a script to compress and copy backups) then I went back and modified the maintenance plan, I would find that the maintenance plan would restore the job to how it was before I made my changes to the job. Maybe there is a better way to do this, but right now I am just trying to stabilize everything. Plus, it just works.
Also agreed; personally, I like a slightly customized version of Ola Hallengren's backup scripts[/url].
As with all backups, the real trick is to successfully and regularly test restoring them (preferably to a different database instance).
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply