April 25, 2014 at 9:15 am
Hello all,
I need to better understand under what circumstances you would rebuild versus restore the system databases? In a meeting about our backup policy, I brought up a "what if" scenario were the sql server would not start and we had to rebuild a new server. I was told, all I had to do in that case was restore the master db - no problem. However, if the sql won't start, there is no restoring the master database, it must be rebuilt, is that correct? That is what I've found so far in Microsoft's documentation anyway...
Thank you,
Karen
April 25, 2014 at 9:31 am
I don't think I could put it any better than this. So I won't try. http://www.sqlskills.com/blogs/paul/disaster-recovery-101-restore-master-rebuild-master/
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
April 25, 2014 at 9:39 am
Thank you very much, looking at the link you supplied now.
I do have another question though 🙂 our networking team do the backups using a third party software but what I get from them is always the mdf & ldf. ( I do my own backups, but lets pretend I don't )
If I am able to restore the master database because the sql server will start, I currently see I can type this into sqlcmd...
RESTORE DATABASE [master]
FROM DISK = 'D:\backup\master_backup.bak'
WITH CHECKSUM, STATS;
Does the restore command above only use .bak or can I specify an mdf & ldf?
Thank you,
Karen
April 25, 2014 at 9:54 am
Karen
If you have a backup file (.bak), you can restore the database. If you have the database files (.mdf and .ldf), you can attach the database. The procedure for doing both those operations is different for master from other databases.
I would advise against using the database files as a recovery strategy. If they were backed up using an open file agent while SQL Server was running, you've no guarantee of consistency and you could find yourself with corruption in your database. You're the DBA: insist on doing native backups yourself and ask the infrastructure people to back up the resulting backup files to disk.
John
April 25, 2014 at 11:12 am
Thanks for the reply John, but question....
I understand what you are saying about the consistancy issue being possible... but say I had a corrupt master occur on a production system, could I not just shut down the sql service, copy over a previous mdf and ldf for the master, start the service and be on my way? What would be an intelligent arguement to that? What are some of the obvious reasons not to do that?
I have attached/detached db's before, not system db's though so unsure how that process differs and haven't found info on it yet.
Thank you,
Karen
April 25, 2014 at 11:57 am
karend211 34657 (4/25/2014)
Thanks for the reply John, but question....I understand what you are saying about the consistancy issue being possible... but say I had a corrupt master occur on a production system, could I not just shut down the sql service, copy over a previous mdf and ldf for the master, start the service and be on my way? What would be an intelligent arguement to that? What are some of the obvious reasons not to do that?
I have attached/detached db's before, not system db's though so unsure how that process differs and haven't found info on it yet.
Thank you,
Karen
Problem is that you can't be sure that previous mdf/ldf files will be in consistent state when you attempt to restart SQL Server with those files. That is the problem with relying on open file backups of the mdf/ldf database files. You really need to have SQL Server backups, either native or taken with a third party backup software such as Redgate Backup.
April 25, 2014 at 1:28 pm
http://www.simple-talk.com/sql/backup-and-recovery/the-sql-server-instance-that-will-not-start/
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
April 26, 2014 at 12:19 pm
karend211 34657 (4/25/2014)
I need to better understand under what circumstances you would rebuild versus restore the system databases?
If you wanted to change the system collation.
karend211 34657 (4/25/2014)
I brought up a "what if" scenario were the sql server would not start and we had to rebuild a new server. I was told, all I had to do in that case was restore the master db - no problem.
Why would you need a new server just because sql server won't start?? What you'd do is check the event logs to see why sql server won't start and work from there.
If your master database was indeed corrupted you would restore it from backups, most backup policies take a copy of the raw files and use them as a backup base. You could even restore the backup of master to a different instance as a user database then detach it. Rename the disk files to master.mdf and mastlog.ldf and plug them into the broken instance then start the service.
karend211 34657 (4/25/2014)
However, if the sql won't start, there is no restoring the master database, it must be rebuilt, is that correct? That is what I've found so far in Microsoft's documentation anyway...Thank you,
Karen
There are many reasons why the SQL Server service may fail to start, most common is a missing folder or permissions.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 28, 2014 at 1:33 pm
Why would you need a new server just because sql server won't start??
I am thinking worst case scenario & want to be prepared & have a plan for all avenues. Yes, I'd check the windows event log & sql server error log but honestly, unless there is something in there that gives me something to go on, I would be tempted to not waste too much time. - have I mentioned I'm an "accidental" dba -- .net developer 85% of the time.
Thank you everyone for the replies & links posted, I've found them very informative. I think what i've got from this is to practice practice practice the different methods that may be needed for recovery.
April 28, 2014 at 2:26 pm
Bear in mind, the server you restore to should be exactly the same (version, sp possibly even cu, but I'd not swear to that one) as your old one otherwise it is unlikely to work.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
April 28, 2014 at 6:12 pm
Bear in mind, the server you restore to should be exactly the same (version, sp possibly even cu, but I'd not swear to that one) as your old one otherwise it is unlikely to work.
Yes, I was wondering about the CU and if it needed to be the same.
April 28, 2014 at 11:10 pm
karend211 34657 (4/28/2014)
Why would you need a new server just because sql server won't start??
I am thinking worst case scenario & want to be prepared & have a plan for all avenues. Yes, I'd check the windows event log & sql server error log but honestly, unless there is something in there that gives me something to go on, I would be tempted to not waste too much time. - have I mentioned I'm an "accidental" dba -- .net developer 85% of the time.
Thank you everyone for the replies & links posted, I've found them very informative. I think what i've got from this is to practice practice practice the different methods that may be needed for recovery.
In my experience sql server doesnt just stop starting correctly for no reason. Most likely\common reasons are missing folder (somone deleted tempdb folder) or missing permission to a folder.
In just about all cases the sql server log and windows app log will highlight the problem area, you just need to go fix it!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 28, 2014 at 11:12 pm
andrew gothard (4/28/2014)
Bear in mind, the server you restore to should be exactly the same (version, sp possibly even cu, but I'd not swear to that one) as your old one otherwise it is unlikely to work.
i have seen problems before on systems where this hasnt been adhered to for SPs, CU should also be matched too although these are SP based and one may argue not as critical, i dont subscribe to that theory though. I would always match SP and CU.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 29, 2014 at 2:38 am
karend211 34657 (4/28/2014)
I am thinking worst case scenario & want to be prepared & have a plan for all avenues.
To be honest, short of corrupted/missing OS files, I can't think of much which would require a server rebuild to fix.
Edit: Or boot drive failures (RIP my test server)
The 'Not wasting time' is why I wrote that article on a non-starting SQL Server. It covers the common problems, how to diagnose and options for fixing.
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
April 29, 2014 at 7:43 am
The 'Not wasting time' is why I wrote that article on a non-starting SQL Server. It covers the common problems, how to diagnose and options for fixing.
Yes, I read it, book marked it, will keep it & use it in the event I should need it. It's a great resource, thank you for posting it.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply