March 10, 2010 at 10:34 am
CirquedeSQLeil (3/9/2010)
YSLGuru (3/9/2010)
If you have a brand new out of the box install of SQL Server 2005 and the only thing you do to it is set it to use Mixed Mode authentication and then create 2 SQL Logins and assign them to the DB which the application you use restores to your server and nothing else is setup, no jobs, no operators no alerts or anything else like it, why in that scenario would you need to backup the system DB's if your recovery process (when the serve goes down) involves using a backup server that is already to go? I mean what is within the system DBs you are backing that you would need to restore from in that scenario?
The answer doesn't change for me. How much time does it take to reinstall SQL Server v. restore the system databases? It is faster and saves more time for me to simply restore the database. One of the first things done with a SQL server should be to setup the backup jobs, test the backup and test the restore. If you put off setting up the backup jobs for a later date - it may get forgotten. I have seen that happen numerous times and have been asked how to restore a database that hasn't been backed up.
Jason - In our particular case, for just this one server it actually would take less time to switch over to the standby server and while it is in use, rebuild our SQL Server box from an image backup (this part falls into the realm of our IT guys so I may have the wording incorrect with regards to this part) and then aftr hours take the DB from the stand by box and move it back to our refreshed Live box.
Thanks for taking a few minutes to chie in, I know we're all busy and so even a short post is mch appreciated.
Kindest Regards,
Just say No to Facebook!March 10, 2010 at 10:44 am
ALZDBA (3/10/2010)
Well ... in your typical scenario .... there shouldn't be any user databases !From the moment you add a user database, you should have a DRP in place !
That DRP off course not only includes the user database, but also the system databases.
As Lynn stated, chances of your system remaining in your typical state are very small.
BTW: here's a little overview....
As you can see the DBA Install backup job DB.sql must be executed.
This installs at least a FULL backup job of all non-readonly databases.
In most cases, also two log backup jobs are installed.
All jobs handle all datases !
Last two steps (before the copy to safe zone) in these jobs are:
Backup master
backup msdb
ALZDBA - In our case or scenario the server actually doesn't change that much and this may be simply because of how little the 1 app that makes us eof the serve, does that is specific to SQL Server like security. The sofwtare company that produces the app has no actual DBA's or much less DB Architects on staff so everything si done by programmers who have read SQL in 24 Hours or Less and so anything handled outside of the application like the SQL Login needed to connect to the DB is as simple as it gets. There's only 1 DB User which is the DBO that is linked to the 1 login that is created and thats it. No Jobs no nothing.
I haven't been backing up the SYSTEM DB's on this 1 server because of the fact that it houses nothing but this 1 user DB and when the thing was done once about a year ago it was, in my opinion a lot easier to switch users to connecting to a stand by copy we had on a stand by server then to go thru an on the spot restore. I know this isn;t the typical scenario for a SQL Server setup and thats why I tried to emphasize that my questions were not with regards to the more common setup/configuration most of us deal with.
Thanks again for taking a few minutes to chip in and reply. The more replies to a post the better the over all answer.
BTW - Great signature line.
Kindest Regards,
Just say No to Facebook!March 10, 2010 at 10:51 am
homebrew01 (3/9/2010)
The system databases are so small, there's no good reason not to back them up.Trying to see how much you can get away without doing isn't really a good approach.
Homebrew01 is right on the mark with this. Here's my question; Why would you not want to back up the system databases?
I've been supporting SQL Server since 1998 and during that time I've never had to restore a system database in a production enviornment. That being said I've always taken the attitude that it is better safe then to be sorry. I would rather err on the side of having backed up databases, especially system databases, that I thought I didn't need than find myself in the rare instance of needing a backup that I didn't have.
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
March 10, 2010 at 10:54 am
I'm late, but I always have done daily master, msdb, model. Things rarely change, but they always seem to have changed right before something breaks. They're small, no reason not to do them daily.
March 10, 2010 at 10:58 am
homebrew01 (3/10/2010)
YSLGuru (3/9/2010)
First off thanks to everyone who has taken the time to contribute, it is appreciated as always.What has been said so far in each reply is in agreement with the standard take on backing up your system DBs and that is you need to do this.
What I haven't seen yet and maybe its my fault with how I worded my original post, is why one would back up their system DB's in a simple setup as I described.
If you have a brand new out of the box install of SQL Server 2005 and the only thing you do to it is set it to use Mixed Mode authentication and then create 2 SQL Logins and assign them to the DB which the application you use restores to your server and nothing else is setup, no jobs, no operators no alerts or anything else like it, why in that scenario would you need to backup the system DB's if your recovery process (when the serve goes down) involves using a backup server that is already to go? I mean what is within the system DBs you are backing that you would need to restore from in that scenario?
Keep in mind I am talking about this specific scenario and not all SQL Server setups in general. We backup stuff on our regular boxes. It is the 2 SQL Setups like this that I'm finding hard to justify the extra backup work for. We backup the user database of course but what would we gain from also backing up the system DBs?
Thanks again for taking the time to chime in
How lazy can you possibly be to not bother spending 60 seconds to create a backup job ? You could have created a lot of maintenance plans in the time it took to write your posts.
If you are backing up user databases, then those backups jobs are in msdb, unless you back them up manually every day. And if they are in jobs, wouldn't you want an email alert to an operator if they fail ?
I guess if everything on your server is "disposable" you can rebuild from scratch if you prefer.
“How lazy can you possibly be to not bother spending 60 seconds to create a backup job ? You could have created a lot of maintenance plans in the time it took to write your posts” ~Homebrew01
First off I’m not lazy. If I were I wouldn’t have even posted this question.
Second, it may take 60 seconds to create a backup job but it doesn’t take 60 seconds to do a DB Restore especially when the DB is over 100GB.
Third – I’ve not had to create any maintenance plan on SQL Server to do any of this. BTW – If you are using SQL Servers Maint Plans to handle your Backup & Recovery you probably shouldn’t be speaking critical of others. The Maint Plan is an entry level process and seldom works %100 for all but the simplest of setups.
Fourth – “If you are backing up user databases, then those backups jobs are in msdb, unless you back them up manually every day. And if they are in jobs, wouldn't you want an email alert to an operator if they fail ?”
WRONG! We don’t use SQL Server’s native backup/restore so we aren’t dependent on MSDB to do a restore.
Look I do appreciate it when people take the time to reply to a post especially since most of us are always way busy and find ourselves with too much to do and not enough time to do it. That said if you are going to be a jerk with your replies, calling someone lazy because they may not agree with you, and assuming that because you do it a certain way then everyone else does to (I’m talking about DB backups) then I for one would rather you keep your comments to yourself and not reply, even if you are the only one with the answer to my question. I’d much rather get a post from someone who disagrees (politely) then a sarcastic comment from someone who knows the answer.
Kindest Regards,
Just say No to Facebook!March 10, 2010 at 11:04 am
YSLGuru (3/10/2010)
Jason - In our particular case, for just this one server it actually would take less time to switch over to the standby server and while it is in use, rebuild our SQL Server box from an image backup (this part falls into the realm of our IT guys so I may have the wording incorrect with regards to this part) and then aftr hours take the DB from the stand by box and move it back to our refreshed Live box.Thanks for taking a few minutes to chie in, I know we're all busy and so even a short post is mch appreciated.
I would not rely on an image backup for the databases. Image backups are great for the file system and OS but less than reliable for the databases.
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
March 10, 2010 at 11:08 am
Steve Jones - Editor (3/10/2010)
I'm late, but I always have done daily master, msdb, model. Things rarely change, but they always seem to have changed right before something breaks. They're small, no reason not to do them daily.
Nooo. Its never tool late. There are numeorus posts where replies keep comin in for many months and sometimes those later replies prove to be the treasures so don't think a late reply isn't a good one.
For most of our SQL setups this is done, backing up the system DB's but for this one I hadn't been doing it because I couldn;t justify it. We don't use SQL Servers built in backup/restore we use a third party product) and so when we need to restore the user DB on this server, which we do a couple time a month to refresh the development copy of it on our dev server.
There's no scenario where we'd need the system DB's to do a restore if some failure occured and so backing up the system DB's on this thing just for the sake of doing it seemed like a waste. I mean I could do it but if we had a failure of some kind I wouldn't use those backups to put everything back anyway so why back them up.
You may be asking if thats my stance why did I then post this to begine with? I wanted to be certain there wasn't something I was missing with regards to the system DB's not being backed up and so far there's nothing mentioned about them that would require we start backing them up.
I realize now, after all the replies it might have hepled if I had mentioned that we don't use SQL Servers native backup & restore and that we use virtual servers and so restoring a server is not like you would do with a real physical server that has to be re-created. I didn't want to get bogged down in discussions of virtual servers vs non-virtual and or backups in SQL Server verses third party because these are2 things I don't have much control over changing here.
Thanks again for taking time to chime in Steve.
Kindest Regards,
Just say No to Facebook!March 10, 2010 at 11:11 am
I'd still do system backups. It isn't a catastrophic failure that's an issue. What if part of msdb becomes corrupt? Or someone removes a login/job/whatever? You don't want a VM restore for that. You want to just pull back the single db.
I think you have some good arguements for large disasters, but it's the small ones that a single db backup mostly helps.
March 10, 2010 at 11:11 am
CirquedeSQLeil (3/10/2010)
YSLGuru (3/10/2010)
Jason - In our particular case, for just this one server it actually would take less time to switch over to the standby server and while it is in use, rebuild our SQL Server box from an image backup (this part falls into the realm of our IT guys so I may have the wording incorrect with regards to this part) and then aftr hours take the DB from the stand by box and move it back to our refreshed Live box.Thanks for taking a few minutes to chie in, I know we're all busy and so even a short post is mch appreciated.
I would not rely on an image backup for the databases. Image backups are great for the file system and OS but less than reliable for the databases.
We don't do image backups for the DB; we use a third party tool that doesn't require anything from the SQL Server the DB came from in order to restore it to the stand by server. As for the OS, that moves into an area our IT guys manage and not something I'm involved in so I can't say which is easier. I do know that when we had this happen once about a year ago, the IT guys had the orginal SQL Server backup and ready to go like new before the end of the day and we were down around lunch time.
Thanks for replying
Kindest Regards,
Just say No to Facebook!March 10, 2010 at 11:13 am
The main concern of your argument seems to be, "Why backup system databases when I can just re-install cause things haven't changed much and I have the scripts to recreate what has?" And you seem to be looking for someone to tell you it's okay.
It is okay, IF AND ONLY IF YOU ARE WILLING TO ACCEPT THE CONSEQUENCES OF NOT HAVING BACKUPS. What happens if your servers go down and your scripts for the changes are also destroyed? (Major flood damage, perhaps?)
Disaster Recover Plans are designed so that you CAN recover from a disaster. Backups are required for that. As people discover on this forum, IF YOU DON'T HAVE BACKUPS, YOU CAN'T RESTORE THEM.
Disasters are unpredictable (that's why they are disasters) and their results are unpredicatable (Hey, we got part of the disk up and running, but the rest of the disk is trashed... was that the part with the ldf or the mdf file?) DRPs allow the disaster to just be an emergency. But if you don't have any backups, you can't restore them. Good DRPs are paranoid and cover everything from a minor blip to a total wipe.
-- Kit
March 10, 2010 at 11:16 am
YSLGuru (3/10/2010)
Third – I’ve not had to create any maintenance plan on SQL Server to do any of this. BTW – If you are using SQL Servers Maint Plans to handle your Backup & Recovery you probably shouldn’t be speaking critical of others. The Maint Plan is an entry level process and seldom works %100 for all but the simplest of setups.Second, it may take 60 seconds to create a backup job but it doesn’t take 60 seconds to do a DB Restore especially when the DB is over 100GB.
I don't use maint plans either, but you seemed concerned about the time it takes to set up a backup job, so I suggested a maint plan because they can be done quickly.
Your system databases are 100+ Gig !?
March 10, 2010 at 11:21 am
YSLGuru (3/10/2010)
Steve Jones - Editor (3/10/2010)
I'm late, but I always have done daily master, msdb, model. Things rarely change, but they always seem to have changed right before something breaks. They're small, no reason not to do them daily.Nooo. Its never tool late. There are numeorus posts where replies keep comin in for many months and sometimes those later replies prove to be the treasures so don't think a late reply isn't a good one.
For most of our SQL setups this is done, backing up the system DB's but for this one I hadn't been doing it because I couldn;t justify it. We don't use SQL Servers built in backup/restore we use a third party product) and so when we need to restore the user DB on this server, which we do a couple time a month to refresh the development copy of it on our dev server.
There's no scenario where we'd need the system DB's to do a restore if some failure occured and so backing up the system DB's on this thing just for the sake of doing it seemed like a waste. I mean I could do it but if we had a failure of some kind I wouldn't use those backups to put everything back anyway so why back them up.
You may be asking if thats my stance why did I then post this to begine with? I wanted to be certain there wasn't something I was missing with regards to the system DB's not being backed up and so far there's nothing mentioned about them that would require we start backing them up.
I realize now, after all the replies it might have hepled if I had mentioned that we don't use SQL Servers native backup & restore and that we use virtual servers and so restoring a server is not like you would do with a real physical server that has to be re-created. I didn't want to get bogged down in discussions of virtual servers vs non-virtual and or backups in SQL Server verses third party because these are2 things I don't have much control over changing here.
Thanks again for taking time to chime in Steve.
I told you the late replies were sometimes the treasures and yours is exactly what I'm referring to and why i orginally posted my question. While we don't have any jobs or alerts or opertaors or anyting like that we do have SQL Logins albeit just the 1 that the application uses. If somehow it became corrupt or got droppedthat would be a scenario where a restor eof the system DB woudl come in handy. That is also the kind of out of the box thinking that caused me to post this to begin with.
That said, if the login was messed up or somehow got dropped I would probably just re-create it and re-sync it to the then orphanned DB User as opposed to restoring a system DB. That is unless for some reason I could not get the login added back. However if that happened I would probably opt to move to the stand by server so as to ensure the least amount of down time for our execs and so that I could troubleshoot (without rushing) or opt to let the IT guys re-fresh the server and then add the login back and restore the DB (from the stand by server) after the busines day was over.
When I came on board this was alreadyt setup like this and I've put off dealing with it so far because it's operated so smoothly except for the one time a year ago when there was a problem and even then it was a hardware issue and something the network guys handled. They had setup the server like this, where if anything went worng they'd go to the stand by server and the re-fresh the live SQL Server and move the data back after hours, because it was the easiest way to handle recovery on tehir end. As IT/Network guys they aren't into anything DB Specific that they don't have to know.
Thanks though for this point because its exactly the kind of thing I was hoping to get a reply for.
Kindest Regards,
Just say No to Facebook!March 10, 2010 at 3:10 pm
YSLGuru (3/9/2010)
I actually know the common reasons or recommendations for why one would typically backup the system databases on a SQL Server 2005 box and which system DB's but I wanted to word the Subject so as to make this easy to find and certain to get great feedback on the more specific question of When is it not necessary to do this.For example in our environment we have numerous SQL Server's most of which run SQL 2005 with a few older ones on 2000 (because of application requirements). I'm curious as to what you guys &gals think about the take of when it’s ok to NOT back up the system databases such as when you have a simple setup where there is only 1 non-system database and the there are no Jobs and no customizations in the model DB and as far as security goes there is only a handful of SQL Logins setup for use by the application that uses the only database on that server. In a scenario like that would it really be necessary to backup any of the system databases?
SYSTEM DB's individually:
TEMPDB: Its my understanding that you just don't back this up period as it is temporary. if thats wrong or not completely right please speak up and comment.
MODEL: If you have no customizations or defaults you want each new DB you create to use then is there really a need to back up the MODEL DB since you are using as is or in other words Out-of-the-box?
MSDB: The container DB for ALERTS, OPERATORS and SQL Jobs and their history. If you either do not use any Jobs, Operators or Alerts on the server or you do but they are but a few (i.e. 1-2) and you have T-SQL code to re-create them, then would you really need to back this up? What about if you don’t need to retain the jobs history info, would it then be unnecessary to back up MSDB?
MASTER: This of course is the main system DB that if it doesn;t work then SQL Server doesn't and everything else becomes secondary. Even if the answer about backing up the other 3 is NO I can see where you'd still want to backup MASTER on a regular basis so you can restore SQL Server as a product should it fail. That being said, would it really be necessary to even backup the MASTER system DB if your recovery plan process involves using another SQL Server that is indetically configured and ready to go as a replacement on short notice?
My main goal here is to get some input from you experienced DBA's on system DB backups to determine if it really is necessary to always backup one or more of the system DB's no matter what the scenario. In our particular case we have a SQL Server with so simple a setup (its only purpose is to service the DB for 1 application that has a very simple setup as far as SQL Security and the like) that I have to ask if backing up the system DB's is more of a hindrance then a help.
We already are doing incremental backups of the actual database the application uses and if we were to go down because of the DB we would not need to do anything to the system DBs to recover. And if the problem were with SQL Server itself and would require restoring the MASTER DB we would switch over to a backup/stand by server anyway making the recovery of the original SQL Server something that is not time critical.
In a scenario like that is backup the system DB's simply unnecessary in your opinion?
Thanks in advance for your input
.
You should backup master and msdb regularly as they are they most importatnt database for Sql Server to run properly. msdb should also be backed up atleast one copy (since we hardy make any changes to this db) and is used as template for newly created DB's. As the remaining is temp DB, which you can;'t backup as it is created every time sql server restarts. Hope this clarifies.. Thanks.
March 10, 2010 at 3:25 pm
Kit G (3/10/2010)
The main concern of your argument seems to be, "Why backup system databases when I can just re-install cause things haven't changed much and I have the scripts to recreate what has?" And you seem to be looking for someone to tell you it's okay.It is okay, IF AND ONLY IF YOU ARE WILLING TO ACCEPT THE CONSEQUENCES OF NOT HAVING BACKUPS. What happens if your servers go down and your scripts for the changes are also destroyed? (Major flood damage, perhaps?)
Disaster Recover Plans are designed so that you CAN recover from a disaster. Backups are required for that. As people discover on this forum, IF YOU DON'T HAVE BACKUPS, YOU CAN'T RESTORE THEM.
Disasters are unpredictable (that's why they are disasters) and their results are unpredicatable (Hey, we got part of the disk up and running, but the rest of the disk is trashed... was that the part with the ldf or the mdf file?) DRPs allow the disaster to just be an emergency. But if you don't have any backups, you can't restore them. Good DRPs are paranoid and cover everything from a minor blip to a total wipe.
@Kit G - I do appreciate your taking time to reply but your wrong in your assumption. I'm not looking for someone to tell me its OK; I'm not that insecure about my decisions. I'm only asking to see if any of the more experienced DBA's out there point out something I hadn't considered, something you have not done in your reply.
If you had actually read my post, the complete post and not just the first few lines you'd know that there is nothing to backup aside from the user DB which we are backing up and restoring successfully when we do restores periodically to our dev server.
"It is okay, IF AND ONLY IF YOU ARE WILLING TO ACCEPT THE CONSEQUENCES OF NOT HAVING BACKUPS. What happens if your servers go down and your scripts for the changes are also destroyed? (Major flood damage, perhaps?)"
What changes are you referring too?? What scripts? We have no jobs or operators nor any SSIS packages, no nothing outside of the user DB and a SQL Login the application uses to connect. We do DB backups via a third party tool so what is it in the SYSTEM databases I'd miss by not backing them up???? If I had to set up a new server to replace this one here I would need to set it to Mixe Mode, create a login and make it the DBO of the DB after I restored it and thats it.
You know it’s funny and sad because it seems like half the people posting aren't even bothering to read the orginal question/post because I keep reading about how I'm going to be in trouble by not backing up my database and that’s not what was said or asked in the original post.
To be as clear as possible, we ARE , I repeat, we ARE backing up the actual user database that contains the data the application uses. The backup of the user DB is NOT at issue or a question. I've already had to do a temporary change to the stand by server in the past so I know for a fact that yes THIS will work. I'm posting this to solicit input, ideally polite and well thought out input on what may be in the system DB's that I missed or was unaware of and so far most of the replies have said I can't do a restore without those system DB's whcih is just untrue.
Again I appreciate you taking the time to reply but please read the full post/question next time before rpleying and try and not be so rude when you do. Its like professionalism has been beaten and thrown out the door.
Kindest Regards,
Just say No to Facebook!March 10, 2010 at 3:54 pm
YSLGuru (3/10/2010)
...and so far most of the replies have said I can't do a restore without those system DB's whcih is just untrue.
Nobody has said that you need to have the system databases backed up to restore a user database. It was said that a restore cannot be performed on a database that hasn't been backed up. Sure you can restore a database that has been backed up - no dispute.
As for the rest of the requirements, a central piece is hinging on a Spare server. What if that spare server takes a dump while you are trying to rebuild the primary server? No system db backups will cause a longer delay in getting back up and running.
The crux of the argument to backup your system databases resides in being able to recover as quickly as possible with as minimal loss to the business as possible. If you are 100% certain that nothing will ever happen to bring down your spare server and primary server, it would be your call to take the risk at not backing up the system databases.
If there are no jobs, packages, users, configs, job history, settings, that you are not afraid to lose (or that don't exist) - then you may be able to justify not backing up the system database.
What if a user, or overpermissioned developer, decides to start using the master database just like a user database? All procs, tables, views, and data (that person created in master) would be lost, if no backup is taken for that database. It really is a matter of preparing for the worst case scenario.
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
Viewing 15 posts - 16 through 30 (of 44 total)
You must be logged in to reply to this topic. Login to reply