October 15, 2010 at 6:35 am
Hello all;
I'm happy to say we're making significant progress, thanks to the help I got from my original post on Recovery Models.
http://www.sqlservercentral.com/Forums/Topic983007-1291-1.aspx"> http://www.sqlservercentral.com/Forums/Topic983007-1291-1.aspx
As an interim solution (and because we have capacity), we are taking nightly full backups with hourly log backups on all the databases that are in Full Recovery. We will refine this, one database at a time, based on RTO and RPO metrics that we are in the process of gathering.
However, I'm not sure what to do with system databases, specifically, Master, MSDB and Model. (We won't backup TempDB, and I believe that Resource gets backed up with Master - anyway, BOL says to leave it alone so that's good enough).
BOL advises to backup Master and Model "as often as necessary" for your business needs, and MSDB "whenever it is updated"
The core of my question is, if we do full backups nightly and hourly log backups, should we be doing the same frequency and types of backups for our system databases? Or, does Simple Recovery (e.g. a full backup nightly) on these represent "as often as necessary"?
As always, your thoughts are deeply appreciated.
Steve
October 15, 2010 at 6:49 am
master cannot be taken out of simple recovery mode so cannot do log backups. How often you back it up depends on how much it changes in your environment. If there is a lot of churn of system level objects back it up nightly, else as a rule of thumb back it up weekly plus ad-hoc if you amend system level objects (add a user, a database, a linked server etc)
model database once a week and ad-hoc should you add objects to it you want propagated to all new databases
msdb - harder to say. If you are dependant on the information in msdb for database recovery information or have constantly changing SSIS packages held in msdb perhaps its worth setting that to full recovery and backing up logs. In most cases weekly is fine
mssqlsystemresource - this is NOT backed up when you backup master. There is no SQL command to back up this database, so just once for every SQL upgrade, take SQL down and make a flat file copy of it somewhere safe. This is a read only database and does not change except as part of the upgrade process, when it is overwritten.
---------------------------------------------------------------------
October 15, 2010 at 7:26 am
George,
This is very helpful and I will proceed based on your input.
I think, because we are making very pervasive changes to our backup routines, I'll err on the side of caution and recommend these backups at least a few times a week and not rely on people remembering to do them ad hoc (e.g. if we make the types of changes you use as examples).
I'm getting the sense that the exposure from data loss in one of these databases is inconvenient, but not fatal? (i.e. some new jobs may fail, new users could disappear etc., but we can still recover SQL Server if our system databases lose a couple days worth of changes?).
October 15, 2010 at 7:46 am
SwayneBell (10/15/2010)
George,I'm getting the sense that the exposure from data loss in one of these databases is inconvenient, but not fatal? (i.e. some new jobs may fail, new users could disappear etc., but we can still recover SQL Server if our system databases lose a couple days worth of changes?).
You can but I would not expose myself to that risk. If one of these databases becomes corrupt SQL will not start. Quickest and safest recovery is from a backup. Do not fall into the trap of thinking of these databases as unimportant, they are integral to the functioning of SQL.
you would be into a rebuild master situation without backups (this is always true with a master database corruption) and even as an experienced DBA I don't like relying on that scenario.
DEFINITELY get a flat file copy of the resource database.
TIP - get a flat file copy of all the system databases except tempdb. Then in a corruption situation you can slide these files into place, restart SQL and restore from latest backup. Just replace the copies with every SQL upgrade.
I recommend practicing recovery scenarios.
---------------------------------------------------------------------
October 15, 2010 at 7:52 am
george sibbald (10/15/2010)
SwayneBell (10/15/2010)
George,I'm getting the sense that the exposure from data loss in one of these databases is inconvenient, but not fatal? (i.e. some new jobs may fail, new users could disappear etc., but we can still recover SQL Server if our system databases lose a couple days worth of changes?).
You can but I would not expose myself to that risk. If one of these databases becomes corrupt SQL will not start. Quickest and safest recovery is from a backup. Do not fall into the trap of thinking of these databases as unimportant, they are integral to the functioning of SQL.
you would be into a rebuild master situation without backups (this is always true with a master database corruption) and even as an experienced DBA I don't like relying on that scenario.
DEFINITELY get a flat file copy of the resource database.
TIP - get a flat file copy of all the system databases except tempdb. Then in a corruption situation you can slide these files into place, restart SQL and restore from latest backup. Just replace the copies with every SQL upgrade.
I recommend practicing recovery scenarios.
Thanks again George.
I'll follow your suggestions and I'll likely recommend nightly backups (simple recovery) for these databases along with the flat file backups.
October 15, 2010 at 7:59 am
The best practices for system databases is that it is not necessary to backup the tempdb database as it is rebuilt each time SQL Server starts.
The model database should be backed up only if we modify it. The master and the msdb databases should be backed regularly(nightly daily) in case of a production server or weekly(depending on the frequency of the changes) in case of test server. If we add a database or made some other major structural change, then we should do an extra backup of master.
We generally backup our system databases on prod server nightly daily and on test server weekly.
Also, its good to check the backups(if the are valid) once in 2 weeks by restoring them as they may have been corrupted. Rebuilding index should also be included every week or bi weekly depending on usage.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
October 15, 2010 at 8:05 am
SKYBVI (10/15/2010)
The best practices for system databases is that it is not necessary to backup the tempdb database as it is rebuilt each time SQL Server starts.
TempDB can't be backed up. You'll get an error if you try.
Msg 3147, Level 16, State 3, Line 1
Backup and restore operations are not allowed on database tempdb.
The master and the msdb databases should be backed regularly(nightly daily) in case of a production server
Daily's often overkill for master. Unless you're adding and changing logins on a daily basis there just isn't that much changing in master that warrents daily backups. Honestly I wouldn't go more frequent than weekly, even on a production database.
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
October 15, 2010 at 8:18 am
@ gail
Thanks for letting me know.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
October 17, 2010 at 8:23 am
For the size of the backups in an average system I dont think there is much excuse for not backing up nightly. It doesnt hurt wont take up much space except in occasional businesses with a large model or msdb database. For SQL 2008 Ent there is even less reason not to with backup compression. I may be over cautious but space is cheap and I'd rather have the confidence that I have a fresh working system backup.
October 18, 2010 at 5:41 am
GilaMonster (10/15/2010)
TempDB can't be backed up. You'll get an error if you try.
Msg 3147, Level 16, State 3, Line 1
Backup and restore operations are not allowed on database tempdb.
The master and the msdb databases should be backed regularly(nightly daily) in case of a production server
Daily's often overkill for master. Unless you're adding and changing logins on a daily basis there just isn't that much changing in master that warrents daily backups. Honestly I wouldn't go more frequent than weekly, even on a production database.
Thanks...this seems to jive with my post a little earlier in this thread - i.e. that in normal circumstances, going a few days without backing up system databases might be inconvenient, but not fatal.
Basically, I was trying to get my head around how closely the system databases (at the instance level, with information about all databases on the server) need to be synchronized in time with the user databases. The feedback from all of you has been a great help.
Steve
October 18, 2010 at 7:43 am
Personally, I back up my system databases once a week - in my maintenance plan named 'System Databases - Weekly' - unless I have a reason to do otherwise (patches, upgrades, etc). It seems to work well enough - no issues so far.
October 26, 2010 at 2:12 pm
i do full backups 5-6 days a week. only because each tape lasts a year or so for system db's and i have 3 tapes dedicated to it
October 27, 2010 at 2:32 am
We should also take Tape drive/Disk space under consideration where we are keeping backup file.
Like, what frequency of the backup should be ? daily/weekly etc ?
and for how long we should keep those backup files ( retention period) ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 27, 2010 at 7:24 am
one time we needed to restore msdb from 6 months ago and we didn't have backups that old. now i keep them for a year or so. usually when the last backup is over 6 months old and i really need an extra tape at the time because it takes 2 weeks to order new ones i'll expire one with old system db backups on it
October 28, 2010 at 6:04 am
george sibbald (10/15/2010)
mssqlsystemresource - There is no SQL command to back up this database, so just once for every SQL upgrade, take SQL down and make a flat file copy of it somewhere safe. This is a read only database and does not change except as part of the upgrade process, when it is overwritten.
George's advice re: the resource database flatfile is important to remember also.
I can see from your responses that there is more than one school of thought around this.
The solution for me, I think, will be to err on the side of caution and take nightly backups, if only because we're already doing this and we have much bigger fish to fry in terms of our Data Recovery Plan.
It continually amazes me how helpful the people in this forum are. Thank you again.
Steve
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply