| Author |
Topic |
|
dbbyleo
Starting Member
11 Posts |
 Posted - 2007-10-19 : 22:35:45
|
| (Be forewarned... this is long, but I think you'll find the information interesting)When it comes to SQL Server backup/restore/recovery, my approach is based on a two-part philosophy. I'd like to share this with you and get you opinion and feedback...First, I approach recovery from the instance level, not the individual databases. While a SQL Server instance is made up of system- and user-databases, I make no distinctions between them in terms of recovery - the instance is the whole "database". I'll later explain why.Second, I approach it so that it will ultimately allow me to do a "displace" recovery... not just "in-place" restore. In other words, be able to restore the database on the current host ("in-place"), such in a case of a media or other failures where the host is intact - which is usually the common, easy case. But above that, be able to recover the database onto another host, such in a case of a catastrophic failure of the original host.At any rate, the two part approach equates to what I consider provisions for doing "displace SQL Server instance recovery" - the ability to restore the whole SQL Server Instance, including the original system-databases, onto another host.I come from an Oracle background... in the world where there's only one "database instance"... no such thing as "individual" databases. And really, the term "database" in Microsoft equates to what Oracle calls a "tablespace". Oracle's "system tablespace" equates to Microsoft's "system databases"... and so forth. It's a common case of one vendor calling it, "TOE-MAY-TOE", and the other calling it, "TOE-MAH-TOE". And in Oracle... I'm not the habit of making distinction between these tablespaces - system or non-system - when it comes to backing up... not many are (I would think). Our backup appoach views backing up the WHOLE database instance... therefore if needed... we can restore the WHOLE database instance. I can't find any other logical way to approach database backup.But as I've found... in the world of SQL Server, there's seems to be a disconnect between how user-databases should be backed up (and restored) from the the way system-databases should be backed up (and restored). Most people make distinction between these database when it comes to backup and recovery.Let me it explain it this way...In my shop, my backup strategy provides the capability to restore both system- and user-databases. Some people might be surprised, but all this can actually be done using only the built-in SQL Server backup and restore functions. What I've also found is SQL DBAs find it almost "un-natural" to restore system-databases. I've heard and read some say is this... "just restore the user-databases on the new server and "re-create" the objects (that were in the previous system database) into the new system databases on the new host". Some also suggests using scripts to make "re-creating" these objects easier.I think that people approach it this way because restoring system database onto another server (a different host name, a different file directory structure, etc) is not as simple as restoring user-databases.But it is doable... and doable using only the built-in SQL Server functions.My previous post on this matter can be found here (http://sqlforums.windowsitpro....22923&highlight_key=y)I long time ago, I found a way to do this. And since then, my backup strategy has remained fairly the same... backup the whole sql server instance... and have the ability to restore the whole instance (if/when needed). And in the end, we not only have a great backup strategy, but a strategy for disaster recovery.I agree that the approach to [simply] restore user-database, and then "re-creating" system-database objects is viable, but it's not "absolute". And here's why...You'll always have to make provisions around when system-database objects are created/changed... for the life of the instance... such as logins, for example.I've seen some application create extended procedures (in the master database) and create jobs and plans (in msdb).You'll have to consistently ensure you update your "re-create scripts" to ensure you capture these objects and changes... through the life of the instance.You'll have to be that much more intimate with the applicatons in order to understand all the components it may be adding outside it's user-database.There a huge potential risk that you'll miss something.Bottom line... restoring the whole SQL Server instance avoids any of the above caveats, guaranteeing a "physical restore" [of the original instance] versus one where parts of it had to be "logically" recreated and/or potentially be missing.I admit, I also use scripts to complete a full SQL Server instance recovery. For me, it is a set of batch and sql scripts, but I only use it to expedite the process. And more importantly... the scripts are created once for an instance and doesn't have to be updated...regardless of what occurs within the instance...regardless if new logins or extended procedures are created/updated (in the master)...regardless if new/updatess to jobs/maintenance plans/etc occur (in msdb)...and I don't even really know what goes on in model... but again... it doesn't matter...all of it gets restored.The process... scripts or no scripts... is the same for all SQL Server instance I support... regardless of the idiosyncrasies of the application running on them. I have to make NO special provisions for any of them.It's a solution where at the end of the recovery... I can tell the customer that whatever they originally lost has been restored (from the last backup)... without any doubt. Again it's a "physical restore", not one where parts of it had to be "logically" recreated.Lastly... If you understand my philosophy... then you could probably understand why I don't consider Log Shipping and Replication to be a thorough disaster recovery solution. The reason being is that these solutions only provision for the recovery of user-databases, right? You cannot use log shipping or replication for system databases, right? And as with the other... you'll have to provision for re-creating system-database objects. Again, I admit log shipping and replication are viable solution, but again, with the idea of re-creating system database objects... it's not absolute... not for system recovery.Tell what you think. |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-19 : 23:32:25
|
| You are right if need to recovery whole server, but not necessary to restore whole server to get couple of deleted rows back. Do you have to backup system dbs? Definitely. Restoring them every time when restore user db? Don't think so. |
 |
|
|
dbbyleo
Starting Member
11 Posts |
Posted - 2007-10-20 : 01:08:48
|
| rmiao... absolutely...Whole instance restore is only applicable in a disaster situation... when the whole instance has been affected... not just one user-database.So you see my point of view. Still, I wonder what others think. Let me wait until I say anymore about the matter. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-20 : 02:46:08
|
We restore individual databases 100 times more often than system ones.For a disaster recovery to a new machine we will restore all databases - including making sure that a) new server is at same SProc level and b) all drives / paths match the original. There will be data loss back to the last backup available that is stored externally to the failed server. Last point of log shipping, MOVE of BAK files to alternative location or "Last Night's Tape Backup" depending on mission critical-ness of application and client's tight-fisted-nessRestore database is common during "trial run" for something. Perhaps a customer will ask us to import some data which is incomplete and needs "massaging". Import the data, client looks at it, often easier to restore than to continue. So that process is done with the intention of being able to restore back.Restore database [to a separate, temporary, database] for recovery of a few rows (as rmiao said) e.g. something deleted by accident; or to do a comparison to figure out why something happened; or what has changed since some earlier date - fraudulent behaviour for example; or trying to work out what a new employee has updated without being aware of their company's required processes.Restore database because a drive failed - usually RAID5 drive failure corrupting a database  . Both database and TLog normally operational at this point, but database showing DBCC CHECKDB errors. So disallow all connections to database; take a final TLog backup; restore the whole lot over the top of the original. TLogs on separate spindle, so this will provide a zero data-loss solution (assuming the error is caught before Full Backup retention policy throws away the last-good-backup!)Restore to move. This would typically be because Server A is getting overloaded and Database X now needs to be moved to Server B. All sorts of mucking about with MASTER and MSDB required to facilitate this ...Kristen |
|
|
|
dbbyleo
Starting Member
11 Posts |
Posted - 2007-10-20 : 11:13:14
|
| Kristen... thanks for a thorough feedback. You pointed out various reasons why user-databases would have to be restored. And there's no arguing the recovery situation you've described to be valid ones.And again... as I replied to rmiao... don't misconstrue my original post to imply I restore the whole instances every time I need to restore a user-database... in-place or displace. It's speaking solely in the context of disaster recovery. But in addition, my philosophy also considers the idea that provisioning for disaster recovery would be ultimate backup/recovery strategy... meaning... the backup approach could then be used for "lesser" recovery situation, including those you've described.At any rate, the main point of my original thread is simply to understand how others view on the necessity to "physically" backup and, more importantly, restore system databases - in terms of disaster recovery. Again, this is different from restoring the system databases via [what I consider] "logically" re-creating objects in the system databases. It does surprise that the method of restoring system database on a completely different server it not a widely none method. I know you said you do it... but you do it after provisioning another identical server. In your method... the method of restoring system databases would be fairly straight forward. You'd simply start the new instance in single-user mode, then restore master... after that everything falls into place... since even the director paths are identical. Restore system database on another, completely different server, however, is a bit more involved.... but nonetheless is very possible and relative easy. I think many DBAs speculate that it's a "nightmare" situation to do this. I'm here to tell you that it's not... having been migrating sql servers from server to another for last 5 years of using this method.That said... I'll focus on the first part of your post..."For a disaster recovery to a new machine we will restore all databases..."Looks like after provisioning for another, identical server, you would be able to do a "physical" restore of the system databases. So I assume you share my point of view that [in a disaster recovery situation], it's important to restore system databases in this manner. Again, this would be in contrast with what others have suggested... where [again], they would simply restore the user-databases on another server and then re-creating system database objects.Last comment to you... with the method I eluded to, you could recover your whole instance without the specific requirements you would have in your current approach - to need to deploy another identical server.In fact... later I'll share how we can provide "rapid [disaster] recoverability" by taking advantage of this method - providing recovery time of seconds in the event of a disaster, which in my opinion [almost] rivals clustering solutions without the added complexity of clustering. But before I go in technical detail of the method, I'd really like to hear from feedback for my general approach. Thanks guys... but keep'em comin'. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
|
|
dbbyleo
Starting Member
11 Posts |
Posted - 2007-10-22 : 14:13:30
|
| Troll Warning? What's that?So what if I posted in sqlmag forum... that's a completely different forum?I'm trying to get feedback from a good demographic, so I thought I'd try to get feedbacks from members from different forums.If you don't have any worth-while feedback... just keep it to yourself. I recall you petty comment of "one of two isn't bad" from dbforum. You're just an @$$ with nothing better to do or say. |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-10-22 : 14:31:10
|
| Look dbbyleo, don't come in here all high and mighty with your 4 total posts. I don't understand how anything you are saying is a surprise to anyone (the gurus) here. I think we've all restored master, msdb, ect to a different server when building new servers or during a disaster. In my opinion, you've pretty much stated the obvious...keep and use backups of all databases (user and system) when needed. You're just about 15 to 20 years (maybe more) late with coming up with that concept.Oh yea, and Blindman is far from an @$$ in my opinion. He has some good knowledge and info...you wanted opinions...so you got them. |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-10-22 : 14:48:49
|
| Another thing I'd like to point out is that you say log-shipping doesn't fit your strategy. What do you d then? Say you run database backups at 4am daily and have an outage at 3pm....you just lose a days worth of data and deal with it? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-10-22 : 14:52:52
|
| I guess I don’t really understand what it is that you are advocating that is at all different than the standard practice. You haven’t really gone into any specifics, exact procedures, scripts, or anything other than your "two-part philosophy".I assume that you are making backups of all the databases, so that is nothing new. If you are recovering to a new server, you have to restore all the databases, including the system databases, so that is certainly nothing different than the standard practice. The fact that restoring the system databases is a little more trouble than restoring the user databases is well known, but the procedures for doing that are well documented. It is possible to just setup a new server, create the logins, etc., and restore the user databases, but that is a judgment call about which is easier.CODO ERGO SUM |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-10-22 : 21:23:30
|
quote:
Originally posted by dbbyleo
Troll Warning? What's that?
You are not looking for advice or comment. You are looking for congratulations on your brilliant and innovative backup strategy, or you are looking to drag people into petty arguments about obscure topics. You snagged Kalman Toth on the other forum, but he's always a pretty hungry fish.If you want to participate on the forums then start by answering some of the practical questions people post. That will get you more respect. If you have more to say, then write a blog and link to it in your signature. That is a more appropriate forum for your "philosophy".e4 d5 xd5 Nf6 |
|
|
|
dbbyleo
Starting Member
11 Posts |
Posted - 2007-10-22 : 22:29:21
|
| Van... as hostile as your feedback sounded... it's still 100 times better than the 2-bit comments from Blindman. I'm sure he/she is a brilliant SQL DBA, but his feedbacks (or lack there of) doesn't show it very well.Anyway... to you and MVJ, thanks for the feedback. Let me address the things you've pointed out.You guys imply that restoring system database - "...when building new servers or during a disaster..." - is something commonly done.And forgive me if I've posted a dumb question or an "obvious" statement.But like I said in my original post... that in other forums threads and two other SQL DBAs I personally know... "everyone" has a position that restoring system databases just is NOT done... they're approach is to "try" to logically re-create system database objects. They say it would be a "nightmare" to restore system databases. So to me... it did NOT seem so common.Anyway... I apologize if I wasn;t clear in the original post and thus sounded like I was "stating the obvious".And I'll go ahead and say that while restoring system database is a little more involved... it's far short of a "nightmare". In fact, once you've understand what exactly needs to happen to restore system database on another server (and apparently you guys have 20 years ago), it's really a fairly easy task.But as MVJ said... it might be that it is a "judgement call as to which is easier". So I'm still wondering why some people would find it easier to re-create potential application objects in the system databases?? To do this, means you have to know EXACTLY what objects are in the system-databases [that an application created and uses]. And you have TRACK IT - if it changes or new ones are added, etc. And you have to this for ALL the applications that your sql servers support. I admit... this volatile situation may not be common, but you have to admit... you can't be 100% sure UNLESS YOU KNOW THE APPLICATION THROUGH AND THROUGH. Any less and there's a potential that you might miss to re-create something.Ultimately, to restore system databases, you can have one set of procedure that can apply to all sql server instances. To do it the other way... you'd potentially have different provisioning for each application... and you can't start provisioning before you know exactly what objects to provision for.If I'm being clear... then tell me if you see it the same... somewhat... or not at all.Blindman... I apologize for call you an @$$. Please do enlighten me with your knowledge. I'm not trying to be "high and mighty". I'm actually being the opposite... and asking for you to validate my thinking.I'm not looking for you to agree with me... I'm looking for good arguments and reasons for why you would or wouldn't.Thanks in advance. |
|
|
|
dbbyleo
Starting Member
11 Posts |
Posted - 2007-10-22 : 22:41:58
|
| I just saw your post after posting my last one. I think it's unfortunate you think I'm here looking for "glorification". You right though, I'm looking for a discussion. So kill me.I think it's you who thinks you're high and mighty... labelling others as "hungry fish"... what that means anyway.What BUGS me is that it WOULDN'T kill you to simply leave it alone... and let others who care to reply, reply! Instead, you come in with your own agenda [in which kinds of post are valid and which post are not]... ultimately obscuring the original post for everyone else.But I'll leave at that... going any further would to inappropriately indulge in something you obviously couldn't resist. Talk about petty arguments... |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-22 : 23:15:29
|
| >> you have to know EXACTLY what objects are in the system-databases [that an application created and uses].Apps shouldn't create any objects in system dbs in my opinion. |
|
|
|
dbbyleo
Starting Member
11 Posts |
Posted - 2007-10-23 : 00:14:42
|
| rmiao...Agreed. But what I/you want isn't always the case.I have and currently support sql server whose application have created and use objects in the system databases.In my specific circumstance... some application require to be able to have write privileges to system databases. In most instances I've seen... the application asks for sa credentials during its install and initial communication to the sql server. This is where I assume it's creating the objects in the system databases... but obvious exactly what. Some application require that the service account it uses on sql server has privileges just short of "System Administrator". At any rate... I have found sql jobs created by the application. These are easy enough to identify. But in another situation, I've seen an application create extended procedures (in master). These ones aren't so obvious. So how am I suppose to ever know what/where/when to provision for these objects.Anyway... the other members here say that it's pretty common to restore system databases... it's a non-issue!... it's not even a question! (is the impression I get). But for polling sake, is it common practice for you that when an instance has to be moved or recovered on another server that you take the method of restoring the system database instead of merely re-creating the objects in them (whether they're for administration or for the supported application)? I can tell you that in other forums and 2 other DBAs I know personally (like I said in my original post) it is not so common.Again, I appreciate the discussion... |
|
|
|
dbbyleo
Starting Member
11 Posts |
Posted - 2007-10-23 : 00:24:25
|
| I missed a point Van made earlier..."Another thing I'd like to point out is that you say log-shipping doesn't fit your strategy. What do you d then? Say you run database backups at 4am daily and have an outage at 3pm....you just lose a days worth of data and deal with it?"Well... I take full backups regularly... and log backup even more regularly. In our specific case we take log backups as often as every hour. If the business required a lesser recovery point objective (RPO), then we'd simply increase the frequency of the backup... say to every 15 minutes (if the business could only lose up to 15 minutes worth of data).Does that address your point?If you point is more towards being able to provide point-of-failure capability... log shipping doesn't provide this either (but correct me if I'm wrong... haven't employed log shipping in our shop). |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-23 : 02:09:17
|
| Log shipping is the same as taking transaction log backups and moving them to another server, so I'm not sure what you are referring to.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-23 : 02:41:00
|
| "In our specific case we take log backups as often as every hour"As often as?</shudder> |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-10-23 : 11:11:59
|
quote:
Originally posted by dbbyleo
I missed a point Van made earlier..."Another thing I'd like to point out is that you say log-shipping doesn't fit your strategy. What do you d then? Say you run database backups at 4am daily and have an outage at 3pm....you just lose a days worth of data and deal with it?"Well... I take full backups regularly... and log backup even more regularly. In our specific case we take log backups as often as every hour. If the business required a lesser recovery point objective (RPO), then we'd simply increase the frequency of the backup... say to every 15 minutes (if the business could only lose up to 15 minutes worth of data).Does that address your point?If you point is more towards being able to provide point-of-failure capability... log shipping doesn't provide this either (but correct me if I'm wrong... haven't employed log shipping in our shop).
That is freaking log shipping. Instead, you are waiting until a disaster happens to do log shipping instead of doing it at intervals throughout the day. But it's still log shipping. So that's one point you can take out of your argument. If you take log backups and are ever going to reapply them anywhere in a disaster situation...like to another server since your Prod server is hosed, then you are log shipping. |
|
|
|
dbbyleo
Starting Member
11 Posts |
Posted - 2007-10-24 : 10:20:13
|
| I think the thread has gone a little astray... let me pull it back to my original point, which probably wasn;t clear (and I apologize).Point #1:You can't say log shipping and/or replication ALONE is a thorough DR solution because (again) it doesn't do anything for system database. I pose the point because (again) from others I've talked so far... they promote that it is a thorough solution for DR without saying anything about what you have to do to recover the system databases. So any case... log shipping or not... a complete DR solution includes provisions to recover the system database. BUT again.. if this is already how you all see it... then I'd appreciate a simply confirmation (without the sarcasm or intensity that I've receieved so far for one or two members).Point #2:Provisions for recovering the system database should include that you restore the system database from backup (something apparently that's been done for decades... sorry I'm late). The point is NOT "hey, do you know you can restore system database from backup?!". My point is why would you try to "recreate" the system databases objects (if it is so easy to simply restore the system databases)?? And again... may this is how you all see it (and I've only stated the obvious), but again a simple confirmation would do. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-24 : 12:08:33
|
| I support only in-house applications and therefore know exactly where everything is inside SQL Server. Applications are not allowed to put anything in system databases with the exception of logins, jobs, and packages. These are very easily recoverable and scriptable from the primary server. Our policy is to create/modify these objects on the primary and DR server, so the DR server always has the latest stuff. We use log shipping for the user databases in SQL Server 2000 and database mirroring for SQL Server 2005. We also use clustering for site and maintenance issues at both sites. We have a 5 9s SLA with our customers with means only minutes of downtime each year. Please explain in detail how you restore the system databases so that we can discuss it rather than you just explaining in words your policy.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Next Page
|
Backup/Recovery Philosophy... pull up a chair