March 10, 2010 at 4:01 pm
Thanks for the repeat of the info, as I actually missed a touch there when I read through things.
Try not to get too defensive as many DBAs hear this question all the time from people that haven't thought it through. If someone is giving advice that you think doesn't meet your needs, I'd just refer them back to your original requirements, or ignore the post.
It sounds like there isn't much you need from msdb, and probably not model either. However adding the backup in, if you're doing master, likely doesn't hurt. The other thing I thought of was that sometimes you might tweak some settings in master, or someone might, and having a few backups might let you go back and make sure that you can recover those settings if they were recently changed. However that's unlikely, and minor.
I'd still suggest that you do back up the system dbs, more out of habit than any real likelihood of disaster. It's a good habit, and won't use much space. Plus it covers you if you do start to add in things like jobs that do help development.
March 10, 2010 at 4:17 pm
YSL,
this sounds like a DR strategy put together by a non-DBA, who likely would not even consider backups of the system databases because they did not realise their importance or function within SQL. And that was the reason they were never backed up rather than some conscious decision.
Now this works OK because you have VMS and they give the sysadmins/network guys the option of quick rebuilds, but lots of network guys think VMs are the answer to all DR problems because they don't understand the nuances of SQL server.
What this DR scenario gives you is quick recovery from server level problems, as you just failover. But thats not the end of it, the END to END process is a lot longer as they have to rebuild the primary server and failback. so for every disaster they actually have a reasonable amount of work to do, some of it out of hours.
What if you went to them and said for some disaster scenarios I can save you this work? they might bite your hand off. So for system db corruptions you can restore the db affected, or a lost password you can reload the login because you have it scripted out, or how about this alternative: next time SQL is down, copy the system dbs off to another directory (including resource database but not tempdb), then if the live db is ever damaged you can just slide the copy back into place and instant recovery. I'll bet that is faster than any failover and saves the work later.
Out of interest as this a simple app is it SQL express edition and how is the failover VM kept in synch?
---------------------------------------------------------------------
March 10, 2010 at 4:25 pm
shivrampal (3/10/2010)
I think you meant model is the database used as a template for creation of all others.
also SQL will not start without model database so a method of recovery for it is required.
---------------------------------------------------------------------
March 10, 2010 at 11:26 pm
YSLGuru (3/10/2010)
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.
I took the time to reread your original post, and I think you may have over-reacted a little here. Not saying you are wrong, just over-reacted. In your original post you make mention of backing up user databases, but you make no mention of how you are doing this. The assumption on most of our part is that you are using the native backup capabilities of SQL Server to accomplish this task. It isn't until later in the discussion that you actually mention that you are using a 3rd party application for this process.
You should also realize that we have recently had a rash of OP's posting asking for help to recover databases that had no backups. With this history, some people may react in slightly inappropriate ways when faced with a simalar possibility occuring again based on your question.
What I hope you get from this, however, is that it is important to backup the system databases regardless of how simple or complex a server may be. If something occurs, you have recourse without having to rebuild from scratch, and rerun any update rountines to recover from a server or hardware failure.
March 10, 2010 at 11:27 pm
YSLGuru (3/10/2010)
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 may not have anything of importance in the system databases now, but perhaps 6 months from now you will. Then you have to remember to start backing up the system databases.
Perhaps you don't need to back them up now. But it's not really a "waste" to do it. It only costs you a minute to set up the job. Not as though you have to spend time every day, or use up lots of disk space (a couple of meg). I use the term "cheap insurance" in situations like this where there is practically no cost to protecting yourself..
March 10, 2010 at 11:30 pm
At the risk of sounding like an echo (again again again again), I agree with homebrew and Lynn.
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 11, 2010 at 7:07 am
YSLGuru (3/10/2010)
@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.
I did read your posts. Because you mentioned several times that your system was a simple one with no to few changes on the system databases and that re-installing was not a problem, it was an impression I got, that you were looking for a reason to not backup the system databases. My apologies for the misunderstanding, that's one of the problems with forums, the tone is left out and misunderstandings can occur because of it.
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.
Then I probably misunderstood one of your posts or confused it with another. I thought had been a mention of 1-2 jobs that ran and that the job history wasn't important.
... try and not be so rude when you do. ...
I wasn't trying to be rude, just trying to emphasis my point. No backups are okay... if you're willing to accept the consequences if/when disaster strikes. Disaster recovery plans are done on the assumption that disaster will strike and everything will be FUBAR. Backups might be your only option to getting things running again. I agree that there's a very slight chance of that happening, but DRPs should take that into account. Planning and drilling take disasters and change them into emergencies. But if you don't plan for it, it stays a disaster.
As an example, I have a database at home that I play with every now and then. I haven't gotten around to backing up the database specifically, it's just included in a general backup of files I run on my computer each night. If the computer goes south and my general backups don't work (which I haven't tried tested), then that database is bye-bye, never to be recovered again. To me, that's okay as it's a play database, I am willing to spend the time to recreate it if I have to and it isn't all that important in my day-to-day life.
At work, I periodically restore the main database I work with to my sandbox just to make sure it can be restored, even though it isn't my job to check the backups that way. If that database isn't around, I have no work, it's a more important database in my life and I need it working.
It's a matter of evaluating the importance of the backup and what's the consequence if it isn't there and needed. That's all I was trying to communicate with my post, to you and anyone else reading the thread. My apologies if feathers were ruffled. It wasn't my intent.
-- Kit
March 11, 2010 at 8:23 am
IMO it shouldn't matter if you have only one db with a single user, or 100 db with n users.
The cost of having a systems db backup should not be considered overhead !
With your single user db instance, I can immagine you - for this moment - know how it is being used and you perform user-db-software upgrade from time to time and take it they don't use stuff outside of their own database.
How can you be 100% sure of that ?
I would still advise do just incorporate the systems db backup with the regular userdb backup. Just in case ...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 11, 2010 at 9:06 am
CirquedeSQLeil (3/10/2010)
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,
Thank you. This is exactly the kind of reply I ws hoping to get; solid reasons, realistic ones on why to back up the system DB's in the scenario I describe and not just a reply of 'You back them up because you do it for every server no matter what the scenario period'. Doing something simply for the sake of doing so without any real reason why is non-sensical and I appreciate your providing a solid reply that addresses the question/issue posted.
"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. ~Jason"'
This was implied in one of the replies (not by you but someone else) which is why I made the comment I did.
Thanks
Kindest Regards,
Just say No to Facebook!March 11, 2010 at 9:13 am
Steve Jones - Editor (3/10/2010)
Thanks for the repeat of the info, as I actually missed a touch there when I read through things.Try not to get too defensive as many DBAs hear this question all the time from people that haven't thought it through. If someone is giving advice that you think doesn't meet your needs, I'd just refer them back to your original requirements, or ignore the post.
It sounds like there isn't much you need from msdb, and probably not model either. However adding the backup in, if you're doing master, likely doesn't hurt. The other thing I thought of was that sometimes you might tweak some settings in master, or someone might, and having a few backups might let you go back and make sure that you can recover those settings if they were recently changed. However that's unlikely, and minor.
I'd still suggest that you do back up the system dbs, more out of habit than any real likelihood of disaster. It's a good habit, and won't use much space. Plus it covers you if you do start to add in things like jobs that do help development.
I typically have a lot of patience except when someone resorts to name calling. It’s one thing to have a dissenting opinion on something and another to be out right rude. We have over a dozen SQL boxes here ranging from 2000 to 2005 and since I have to handle the DBA role of those in addition to development work (mainly T-SQL stuff and a little traditional procedural/OOP like the .Net stuff) I’m trying to do only what really needs to be done and so when it comes to backing up the system DB’s in the scenario I described I want to have a good reason for the extra work and not just do it because that’s what you always do. We are going to have between 2 to 4 more SQL Servers like this, with a single DB and SQL Login with nothing else and so the way I deal with the current one will be multiplied 2 to 4 fold and so I don’t want to do something just for the sake of doing it.
Thanks again for taking the time to reply Steve.
Kindest Regards,
Just say No to Facebook!March 11, 2010 at 9:21 am
YSL,
you seem convinced you have found a scenario where system db backups are not required, but you won't ever get a production DBA worth his salt to agree with you I am afraid.
There is a compromise (apart from the one I posted earlier). I think the 'official' line on system db backups is to back them up whenever they change, so thats when you should back your system dbs up, which for you won't be very often, but they will change from time to time, at the very least when you patch your SQL server.
MSDB will in fact change often as it records backup history (most likely even if a third party tool is used), but if you are not dependant on this information to restore your databases you can live without that.
As these backups would be infrequent you should verify them on backup and do test restores with the backups from time to time.
---------------------------------------------------------------------
March 11, 2010 at 9:27 am
george sibbald (3/10/2010)
YSL,this sounds like a DR strategy put together by a non-DBA, who likely would not even consider backups of the system databases because they did not realise their importance or function within SQL. And that was the reason they were never backed up rather than some conscious decision.
Now this works OK because you have VMS and they give the sysadmins/network guys the option of quick rebuilds, but lots of network guys think VMs are the answer to all DR problems because they don't understand the nuances of SQL server.
What this DR scenario gives you is quick recovery from server level problems, as you just failover. But thats not the end of it, the END to END process is a lot longer as they have to rebuild the primary server and failback. so for every disaster they actually have a reasonable amount of work to do, some of it out of hours.
What if you went to them and said for some disaster scenarios I can save you this work? they might bite your hand off. So for system db corruptions you can restore the db affected, or a lost password you can reload the login because you have it scripted out, or how about this alternative: next time SQL is down, copy the system dbs off to another directory (including resource database but not tempdb), then if the live db is ever damaged you can just slide the copy back into place and instant recovery. I'll bet that is faster than any failover and saves the work later.
Out of interest as this a simple app is it SQL express edition and how is the failover VM kept in synch?
George - Yes this was setup by a non-DBA; the IT guy who had no DBA like help before I came on board. There were no system backups in place, I don't blame the guy because that’s not his area of knowledge. Giving him hard time about this would be like him giving me a hard time for not knowing how to properly implement/setup Cisco routers.
Thanks for the excellent suggestions on how to talk the IT guys into allowing a change; well worded. They are also very pro VM; something I am not against but not as sold on. That beings aid I have no ability/power to change this so I go with it and do what I can. Eventually, after the more critical Servers are fully addresses I can come back and look at doing a more complete recovery for this server but for the short term, the stand by server is to easy and quick to say no to; at least when I have lots of other problems to address that are more critical and have no easy alternative answer.
The application does run on SQL Express (I think) but we don’t use it to host the apps database. The DB is over 100GB in size and has a large number of users connecting so SQL Express wouldn’t be an option even if the application does support its use. We have little flexibility with what we can do with the server this application DB is on because of the strict terms of our support plan with the vendor of the application in question. For example we can’t upgrade to 2008 or even a new SP in 2005 until the vendor officially supports it else we risk losing our support. Luckily the DR is totally up to us (not that the vendor would have a valid reason for trying to tell a client what their DR options are) but the simplicity of the setup on the box (or Virtual Instance) that hosts the Server makes it unnecessary to do system DB backups in order to recover should something fail.
Thanks for taking a few minutes to chip in on this issue.
Kindest Regards,
Just say No to Facebook!March 11, 2010 at 9:46 am
Lynn Pettis (3/10/2010)
YSLGuru (3/10/2010)
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 ........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.
I took the time to reread your original post, and I think you may have over-reacted a little here. Not saying you are wrong, just over-reacted. In your original post you make mention of backing up user databases, but you make no mention of how you are doing this. The assumption on most of our part is that you are using the native backup capabilities of SQL Server to accomplish this task. It isn't until later in the discussion that you actually mention that you are using a 3rd party application for this process.
You should also realize that we have recently had a rash of OP's posting asking for help to recover databases that had no backups. With this history, some people may react in slightly inappropriate ways when faced with a simalar possibility occuring again based on your question.
What I hope you get from this, however, is that it is important to backup the system databases regardless of how simple or complex a server may be. If something occurs, you have recourse without having to rebuild from scratch, and rerun any update rountines to recover from a server or hardware failure.
Lynn - I respect your opinion and as always (especially in past posts) value your opinion but this is one point where we'll just have to agree to disagree. I do understand the relevance of backing the system DB's when your DR plan involves native SQL Server backups and so I will concede that if I had included in my post that we don't use native SQL Backups it would have provided a more complete understanding of the scenario I was describing. That said, this missing detail does not justify someone replying in an unprofessional manner and calling someone lazy. The only time I have replied back with anything other than a ‘thank you’ or a ‘here is more info..’Is when someone resorted to name calling or with a reply bordering on flaming.
I am also familiar with the problem of inexperienced folks (be they DBAs or non-DBAs trying to fill the role of a DBA) posting incomplete/bad/inappropriate questions/issues but I don’t believe that justifies replying harshly to someone who has not yet posted a rude reply or comment.
As always thanks for taking a few minutes to reply
Kindest Regards,
Just say No to Facebook!March 11, 2010 at 9:56 am
Its clear to me now after all the replies so far that no matter how many times I try I am not able to properly convey my situation/scenario and I take responsibility for that.
I’d like to thank all who have taken the time to reply as I know we are all very busy with more to do then time to do it and so taking even a few minutes to reply to a post says a lot about ones character.
Because I can’t properly convey the issue and since several posters have already wasted their valuable time on this I ask for no more replies to the question/issue the original post was about and ask that instead you use that time and effort to address other posters questions who are more in need of your knowledge and skills.
I would like to thank those who responded in a professional manner even when they had an opposing viewpoint then I with regards to system DB backups in the very specific scenario I was trying to describe. Let me state as others already have that it is important you do backups of your system databases on each and every single SQL Server system you manage and that if you feel you may have a scenario like mine but your not certain if you should do a backup, always err on the side of caution and do it anyway. My situation or scenario is unique and this is why I even raised the question of doing system DB backups on it to begin with.
Thanks again and continue to help Steve make SQLServerCentral.com the best SQL site on the internet.
Kindest Regards,
Just say No to Facebook!March 11, 2010 at 10:02 am
YSLGuru,
I think you're on the right track, and despite the posts, it seems that you understand the issue. Don't worry about getting some good feelings from others. Go with your instinct here.
I'll close this thread since it has deviated a little from what you were looking for.
(and thanks for the kind words)
Viewing 15 posts - 31 through 44 (of 44 total)
You must be logged in to reply to this topic. Login to reply