| Author |
Topic |
|
ras2a
Yak Posting Veteran
66 Posts |
 Posted - 2006-06-29 : 09:15:48
|
| Is it important to backup 'all' the system db's? (master, model, msdb, northwind, pubs, tempdb). If so, how often should these be backed up?At present, we backup our user db's and the master db. In other words, can these other db's be recovered easily or is better to just include them all in a backup regime?Regardsras |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-06-29 : 09:24:58
|
| I don't think of Pubs and Northwind as system databases.The rest should be backed up whenever you are going to do something significant to the database, such as installing a service pack.Peter LarssonHelsingborg, Sweden |
 |
|
|
ras2a
Yak Posting Veteran
66 Posts |
Posted - 2006-06-29 : 14:36:27
|
| Ok, so other than that, you don't need to backup them up regularly - as per daily user db/log backups?Cheers Peso |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-06-29 : 15:00:08
|
| We perform full backups of the system databases daily. System databases are master, model, msdb, and tempdb. Tempdb doesn't get backed up though. I don't recommmend backing up these databases only when something significant changes. Master and msdb should be backed up regularly. We delete Pubs and Northwind from our production servers. We leave them on development just in case we need a scratch area to play around with.Tara Kizeraka tduggan |
|
|
|
DHeath66
Starting Member
5 Posts |
Posted - 2006-06-29 : 15:39:35
|
| Depends on what you consider as regularly. But if these databases are used often and have valuable data then you should make sure you have very good backups of not the data but the system databases as well. Because rebuilding the system databases is a task (if no backups are available)that is out of this world :) especially if you want the new system databases to interact with old databases, jobs, sp's, alerts, and the likes. It can be done just a lot of workDHEATH |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-06-29 : 17:06:55
|
| I make daily backups of master, model, and msdb on all servers and retain them on disk for 8 days.They are small, only take a few seconds to backup, and are easy to schedule with the maintenance plan wizard, so there is no reason not to have at least daily backups of the system databases.If you don't have them when you need them, you will really feel the pain (and deserve it).CODO ERGO SUM |
|
|
|
ras2a
Yak Posting Veteran
66 Posts |
Posted - 2006-06-29 : 18:16:36
|
| Thanks for all the answers guy's! Will set this up tomorrow.So basically, daily backsup of: master, model and msdb?thanks againras |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-06-29 : 18:19:43
|
| Master and msdb for sure. If you never change model, then you don't need to back it up.Tara Kizeraka tduggan |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-06-29 : 20:15:59
|
| I include model because it is usually so small, and because no one would remember to back it up manually if it was changed.CODO ERGO SUM |
|
|
|
ras2a
Yak Posting Veteran
66 Posts |
Posted - 2006-06-29 : 20:45:37
|
| that's great - cheers again, dudes :) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-06-30 : 01:26:51
|
| "The [system databases] should be backed up whenever you are going to do something significant to the database, such as installing a service pack."Peso I think you should review that policy!msdb can be very helpful in working out what was backed up, and restored, and when - and if your server is hosed that may be critical in discovering why a backup chain appears to be brokenFull BackupTLog backup...TLog backupRestore from Full and SOME of the TLog backupsTLog backup...TLog backupCrash!and Master will have information about databases that are created etc., plus may very well be being used for global SProcsAnd as MVJ said detecting when someone changes MODEL is a black art best avoiding by just backing it up.Back them up daily ... or using DIFFs on weekdays if you must (we do DIFFs for MSDB 'coz it runs around 1GB for a full backup (and before anyone asks! loads of databases, TLog backups every 15 minutes, 3 months history retained, you can do the maths!)Kristen |
|
|
|
ras2a
Yak Posting Veteran
66 Posts |
Posted - 2006-06-30 : 04:43:34
|
| At present we use 2 full backups a day (One @ 1pm & one @ 5pm) and tx logs inbetween (every 30 mins). I assume that, in order to carry out differential backups, you have to use a script? I'm pretty sure there was a link posted in one of my threads - will check that out. Our user db is only small, but I imagine that restoring multiple tx logs can take quite a while? By the way, I have been creating test dbs for restore purposes just to ensure everything runs smooth, which it does.cheersras |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-06-30 : 13:52:22
|
| We don't perform differential backups. We perform full backups daily and transaction log backups every 15 minutes. If ever we need to restore to a point in time, then we'd quickly write a script to generate the RESTORE LOG commands using xp_cmdshell and the dir command in the backup directory. Differentials help out when your database is larger and either you don't have the space for multiple full backups or your full backups take too long.Tara Kizeraka tduggan |
|
|
|
ras2a
Yak Posting Veteran
66 Posts |
Posted - 2006-06-30 : 18:45:30
|
| cheers tkizer |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-06-30 : 20:15:12
|
| One suggestion: if you're using a 3rd party backup tool like SQL LiteSpeed, DO NOT use it to back up your system databases, use regular SQL Server backup commands instead. That way you don't have to install anything extra in order to restore master and msdb. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-06-30 : 20:19:36
|
quote:
Originally posted by robvolk
One suggestion: if you're using a 3rd party backup tool like SQL LiteSpeed, DO NOT use it to back up your system databases, use regular SQL Server backup commands instead. That way you don't have to install anything extra in order to restore master and msdb.
Doesn't LiteSpeed include a command line tool that can be used to uncompress the backup files that LiteSpeed creates?CODO ERGO SUM |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-06-30 : 21:24:22
|
Yeah, they do, but if I'm in a situation where I have to restore master and msdb, that's a step I'd rather not deal with. I'd be a little pressed for time.  And unless either one is over 250 MB in size I can live without the compression. |
|
|
|
ras2a
Yak Posting Veteran
66 Posts |
Posted - 2006-06-30 : 21:56:34
|
| Well, I 'was' originally using the SQL Agent for Veritas Backup Exec (v10), but I have, on peoples recommendation here, stopped using this. Am now using SQL jobs exclusively. I have created test dbs and test restored etc for my own peace of mind - everything seem's fine.cheersras |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-07-01 : 06:01:13
|
| "And unless either one is over 250 MB in size"IME MSDB will be > 250MB if you have more than 20 or 30 databases on the server (or forget to prune the historical records!)But MASTER backups hitting 25MB, let alone 250MB, would be pretty scary, wouldn't it? So no reason to add additonal "risk" or "downtime" on that one.Kristen |
|
|
|
|
Backing up all the system db's?