February 17, 2010 at 3:11 pm
Our system administrator and database admin recently quit, and, as the saying goes, guess who was standing nearest the servers when that happened?
Now I've (unofficially) taken on the role of DBA, along with my regular developer duties. So I was just poking around in SSMS to see what the former dba had done and I noticed that he's got a nice maintenance plan in place to backup the user databases. But it doesn't look to me as though he's got anything in place to backup the system databases. I'll be honest, I don't know if that's right or not, but it seems to me as though you'd want to backup at least the master, model and msdb databases. But if it doesn't matter, than OK.
So, what's the correct thing to do? Should I make a maintenance plan to backup the 4 system databases?
Kindest Regards, Rod Connect with me on LinkedIn.
February 17, 2010 at 3:15 pm
definitely backup the system databases, in fact do it now.
In the maintenance plan there is an option to select system databases.
This does not include tempdb, as its name suggests thats a temporary database recreated on SQL server restarts, it cannot be backed up.
Set up integrity checks as well.
---------------------------------------------------------------------
February 17, 2010 at 3:20 pm
Yes backup the system databases. Only master, model, and msdb are necessary. They don't need to be backed up as frequently as the user databases - but must be backed up regularly.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 17, 2010 at 3:38 pm
Just to sound like an echo, backup the systems databases (Master, Model, MSDB) on a regular schedule.
Gethyn Elliswww.gethynellis.com
February 17, 2010 at 3:59 pm
Yes\No, because it is very rare you restore system databases, but no harm in taking system databases as they take little space. Include them in maintenance job.
EnjoY!
February 17, 2010 at 4:03 pm
GT-897544 (2/17/2010)
Yes\No, because it is very rare you restore system databases, but no harm in taking system databases as they take little space. Include them in maintenance job.EnjoY!
Its rare to restore any database in a production environment but if you have no good backup you won't be restoring anything.
Gethyn Elliswww.gethynellis.com
February 17, 2010 at 4:13 pm
GT-897544 (2/17/2010)
Yes\No, because it is very rare you restore system databases, but no harm in taking system databases as they take little space. Include them in maintenance job.EnjoY!
No harm in taking the backup of system dbs = true.
A lot of harm in NOT taking a backup of system dbs = true.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 17, 2010 at 4:25 pm
GRE-452109 (2/17/2010)
Just to sound like an echo, backup the systems databases (Master, Model, MSDB) on a regular schedule.
Not true - in a disaster recovery scenario you will probably be restoring those databases to rebuild the system. At least, you would be restoring the databases as user databases to get at the logins and agent jobs so that information could be extracted and rebuilt.
Or, you are pro-active and have a process that generates the scripts to recreate the logins and a process that generates the scripts to recreate your agent jobs, and a process that generates the scripts to recreate your maintenance plans, etc... And, of course you have to make sure those scripts are backed up also...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 17, 2010 at 4:35 pm
GT-897544 (2/17/2010)
Yes\No, because it is very rare you restore system databases, but no harm in taking system databases as they take little space. Include them in maintenance job.EnjoY!
Backing up the system databases is necessary in case of a system failure that requires rebuilding your SQL Server systems.
February 17, 2010 at 4:43 pm
Jeffrey Williams-493691 (2/17/2010)
GRE-452109 (2/17/2010)
Just to sound like an echo, backup the systems databases (Master, Model, MSDB) on a regular schedule.Not true - in a disaster recovery scenario you will probably be restoring those databases to rebuild the system. At least, you would be restoring the databases as user databases to get at the logins and agent jobs so that information could be extracted and rebuilt.
Or, you are pro-active and have a process that generates the scripts to recreate the logins and a process that generates the scripts to recreate your agent jobs, and a process that generates the scripts to recreate your maintenance plans, etc... And, of course you have to make sure those scripts are backed up also...
I think I was agreeing not disagreeing with backing up the system databases...or have i missed something?
Gethyn Elliswww.gethynellis.com
February 17, 2010 at 4:48 pm
Sorry - quoted wrong post. Meant to post the one above yours.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 17, 2010 at 5:04 pm
Jeffrey Williams-493691 (2/17/2010)
Sorry - quoted wrong post. Meant to post the one above yours.
Are you referring to mine or GT-897544?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 17, 2010 at 5:08 pm
GT's
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 17, 2010 at 5:14 pm
Jeffrey Williams-493691 (2/17/2010)
GT's
K - I thought the same but figured mine could use more explaining too.
Thanks Jeff.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 17, 2010 at 7:27 pm
GT-897544 (2/17/2010)
Yes\No, because it is very rare you restore system databases, but no harm in taking system databases as they take little space. Include them in maintenance job.EnjoY!
There is never a "No" here. If you don't backup the system databases, you lose logins, roles, replication jobs, scheduled jobs, etc, etc, etc. There is great potential harm in NOT backing up databases. Its not whether or not restoring them is a rare event and you just shouldn't tell people that. Not having an up to date set of backups for the system databases is just asking for trouble.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply