September 13, 2011 at 3:46 am
Running multiple sql server 2005 instances on windows 2008.
Our Server guys changed the way that they back up the server (at the server level) and excluded all .ldf and .mdf files.
Needless to say when we recently went through a disaster recovery drill I had to uninstall and reinstall sql server (all intstances) before I could begin restoring databases.
I am wondering if I had made copies of the master, resource, msdb and model .mdf and .ldf files for each instance and then copied them into their correct directories for the DR drill if i could have avoided having to reinstall SQL Server? I don't have a server to test this on which is why I am asking....
How do others backup their servers hosting sql server?
September 13, 2011 at 4:23 am
.mdf & .ldf file backups aren't of much use if SQL is active. Are you taking proper SQL database backups regularly ? "BACKUP DATABASE MyDatabase ..."
September 13, 2011 at 4:23 am
File-level backups of SQL data files are not backups. They have a irritating habit of being useless when trying to attach them.
You should have backups of the system databases as well as backups of the user databases (that's database backups, not copies of the files).
Some people keep copies of the system databases (taken while SQL is stopped) to avoid having to rebuild master if there's a problem with the master database, but that's in addition to backups, not a replacement of backups.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 13, 2011 at 6:54 am
Piling on a bit, because it's a point worth making.
Most backup products are not aware of the fact that SQL Server has this pesky little function called a transaction. So, even if the backup software can backup the MDF/LDF files while they're being accessed, it doesn't take into account that there are transactions in an incomplete state within these files when the backup occurs. Later, when you try to use the files to attach a database, as Gail says, it all breaks down.
The safest option, use a SQL Server backup to file which can be done against a live database, no service interruption. Then backup those files. When you're in a DR scenario, you restore the databases. Yeah, same thing with the system databases. Second best option, good for extremely large systems, use a transactionally aware disk copy function instead of a backup. This is common in very high-end SAN systems and some of the virtual servers. But, test the restore process to be sure that the vendor is not blowing smoke when it comes to their being transactionally aware. Some are, some, not so much. Third best option, and one I don't like, get a plug-in for your backup software that is transactionally aware. I've found these to be very problematic. Last option, do cold backups. Shut down your service and then backup the MDF/LDF files. As long as you don't mind the server being totally offline, you can do this.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 13, 2011 at 7:43 am
Jpotucek (9/13/2011)
How do others backup their servers hosting sql server?
Backing up a server is different than backup up SQL databases.
Also, if these are production databases, you should be taking transaction log backups frequently 24/7, or at least during business activity.
September 13, 2011 at 8:08 am
Thank you for all the replies.
I AM doing regular Databases backups Full nightly Database backups and Transaction Log backups where appropriate and depending on the SLA with the Business owner for each Database.. Some the Databases get a TLOG backup every 5 mins during business hours.. some ever 2 hours..
I am simply trying to figure out how to tell my Server Guys how to backup the Server so that when they restore it at DR The SQL Instances will start.
September 13, 2011 at 8:13 am
Jpotucek (9/13/2011)
I am simply trying to figure out how to tell my Server Guys how to backup the Server so that when they restore it at DR The SQL Instances will start.
I hope backup taken by server guys is tape backup. You can ask them to backup your Full backup and tran log backup folders.
But i guess during DR drill, they will only restore the backup from tape to the DR box. Database restoration will have to be done by the DBA [Unless you are using SRDF where disks from primary and DR servers are mirrored]
September 13, 2011 at 8:21 am
Jpotucek (9/13/2011)
Thank you for all the replies.I AM doing regular Databases backups Full nightly Database backups and Transaction Log backups where appropriate and depending on the SLA with the Business owner for each Database.. Some the Databases get a TLOG backup every 5 mins during business hours.. some ever 2 hours..
I am simply trying to figure out how to tell my Server Guys how to backup the Server so that when they restore it at DR The SQL Instances will start.
You have to build out the restores as part of the DR script. That way they can follow along and do the right commands in the right order.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 13, 2011 at 8:55 am
Basically at DR, they recover the Server and hand it over to me. In past DRs, when they 'handed' the server over to me to recover the databases, the SQL Server Instances were up. NOw they have changed that way they back up the server and excluded all MDF and LDF files, when they 'hand' the server over to me, the instances will not start so I did and uninstall\reinstall just to get to a point where I COULD restore Databases.. I'm thinking if I had offline copies of my system Database files - even if they were a little old - I could at least get my Instances started --- is my thinking wrong here?
September 13, 2011 at 9:00 am
Let me know if i am missing something.. I assume you have a 'standby' server for the DR drill (you are not doing drill on prod right?). If you have standby server, SQL Server must already be installed?
September 13, 2011 at 9:02 am
I'm guessing that for some strange reason the server guys are re-imaging the DR server from tape. That's an exceedingly odd approach....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 13, 2011 at 9:07 am
They are indeed.
September 13, 2011 at 9:52 am
That's extraordinarily odd, and slow. A DR SQL Server should have SQL installed and ready to start (if not running as a log shipping secondary) and just need the restore of the latest backups to bring online. Reimaging the entire machine when needed does not strike me as efficient...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 13, 2011 at 10:13 am
I agree with you.. what they do (twice a year) is go offsite and recover the tape library and then start recovering servers (from tape) everything from Domain Controllers to file server to any Server hosting Databases. There has to be a way for them to backup the Sql server Binaries and system DB files so that when they recover the server the instances start. They did is in the past - but now that they are excluding all .mdf and .ldf files from their server backups I'm kinda screwed here.. Thinking if I tell them to excuded .ldf and .mdf files EXCEPT the ones for the system databases I would be OK.. !?
September 13, 2011 at 10:25 am
That's called vmware & veem backups (or whatever your favorite vendor)!
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply