January 14, 2013 at 5:43 pm
I have a situation where all system database (.mdf, .ldf) were wiped out after system recovery from a major outage. I try to follow this link (http://blogs.msdn.com/b/psssql/archive/2008/08/29/how-to-rebuild-system-databases-in-sql-server-2008.aspx)
to rebuild system databases but got error that difficult to trouble shoot.
OS: window 2008 SP1
SQL: 2008 SP2
My question1:
1. Is the "setup" file is still good to use if my sql server is patched to SP2 after installation?
2. What else I can try to recover system files if I have the backup files?
Thanks
Clare
January 15, 2013 at 12:03 am
If your backup is good, you should be able to restore them. Post the error you got while restore.
First try to restore master database.
March 18, 2013 at 11:26 am
Stop SQL Server and restart it in single user mode. Then from management studio right click the master database and and use this menu to restore form your backup device. This assumes your back up is not corrupted.
----------------------------------------------------
March 18, 2013 at 4:15 pm
1. Is the "setup" file is still good to use if my sql server is patched to SP2 after installation?
If this is one of the system database files that are in other locations on your server... yes. it will upgrade it self to SP2 when you start it. You can use this to get SQL Server up long enough to restore master from your last good backup.
2. What else I can try to recover system files if I have the backup files?
(last ditch effort short of reinstall from scratch) Please only do this if you're familiar with the process or at least all of the moving pieces.
If you have a server where master/msdb/model/tempdb are all setup in the same location as your server you're having an issue with (if you only install with defaults and all) copy your existing files into a folder marked old, place these new files where the old ones were... then start up SQL server, normally around here. 'C:\Program Files\Microsoft SQL Server\MSSQL_10_50\MSSQL\Binn'
in command line run ''C:\Program Files\Microsoft SQL Server\MSSQL_10_50\MSSQL\Binn\sqlservr -m' If that's where your sqlservr.exe is located (adjust accordingly)
for a named instance use
''C:\Program Files\Microsoft SQL Server\MSSQL_10_50\MSSQL\Binn\sqlservr -m -s<Instance Name>"
next open another command prompt window and run sqlcmd.
'RESTORE DATABASE master FROM <backup_device> WITH REPLACE'
when you restore master, you should be able to restore the rest of the system databases as well from backup.
I hope this leads you at least in the general area.
.
April 3, 2013 at 3:22 pm
Bill (fluffydeadangel) (3/18/2013)
1. Is the "setup" file is still good to use if my sql server is patched to SP2 after installation?If this is one of the system database files that are in other locations on your server... yes. it will upgrade it self to SP2 when you start it. You can use this to get SQL Server up long enough to restore master from your last good backup.
2. What else I can try to recover system files if I have the backup files?
(last ditch effort short of reinstall from scratch) Please only do this if you're familiar with the process or at least all of the moving pieces.
If you have a server where master/msdb/model/tempdb are all setup in the same location as your server you're having an issue with (if you only install with defaults and all) copy your existing files into a folder marked old, place these new files where the old ones were... then start up SQL server, normally around here. 'C:\Program Files\Microsoft SQL Server\MSSQL_10_50\MSSQL\Binn'
in command line run ''C:\Program Files\Microsoft SQL Server\MSSQL_10_50\MSSQL\Binn\sqlservr -m' If that's where your sqlservr.exe is located (adjust accordingly)
for a named instance use
''C:\Program Files\Microsoft SQL Server\MSSQL_10_50\MSSQL\Binn\sqlservr -m -s<Instance Name>"
next open another command prompt window and run sqlcmd.
'RESTORE DATABASE master FROM <backup_device> WITH REPLACE'
when you restore master, you should be able to restore the rest of the system databases as well from backup.
I hope this leads you at least in the general area.
Thanks Bill for the reply! More questions for you 🙂
1. What do you mean by "If this is one of the system database files that are in other locations on your server... "?
I found out that I have to copy SQL install folder locally to rebuild system databases. Use the setup file from C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release does not work
2. I am following msdn document to rebuild resource databases and it says sp need to be applied afterwards. I got error saying there is nothing to be patched when I tried to apply the patch. So my question is if my SQL server 2008R2 is patched to SP2 and I need to rebuild the resource database. How do I apply the patch to resource database? Can I just copy it from other machine that has the same SQL version?
"Rebuild the resource Database
--------------------------------------------------------------------------------
The following procedure rebuilds the resource system database. When you rebuild the resource database, all service packs and hot fixes are lost, and therefore must be reapplied.
To rebuild the resource system database:
Launch the SQL Server 2012 Setup program (setup.exe) from the distribution media.
In the left navigation area, click Maintenance, and then click Repair.
Setup support rule and file routines run to ensure that your system has prerequisites installed and that the computer passes Setup validation rules. Click OK or Install to continue.
On the Select Instance page, select the instance to repair, and then click Next.
The repair rules will run to validate the operation. To continue, click Next.
From the Ready to Repair page, click Repair. The Complete page indicates that the operation is finished.
"
Thanks
Clare
April 4, 2013 at 7:01 am
If all the system database files have been wiped out, you cannot start in single user mode as SQL Server expects at least the master database to be present and readable.
Your best options is:
model.mdf
modellog.ldf
msdbdata.mdf
msdblog.ldf
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 4, 2013 at 9:41 am
Clare:
Are we talking about SQL2008 or SQL2008 R2? The location of the setup file that rebuilds the system databases is different for R2, and the procedure is not well documented.
I went through this exercise last week - fortunately for me it was a non-prod system I deliberately broke so I could learn exactly how to fix it if the Master DB was actually lost.
For detailed steps see my second message in http://www.sqlservercentral.com/Forums/Topic1437039-1550-1.aspx
April 4, 2013 at 10:23 am
Perry Whittle (4/4/2013)
If all the system database files have been wiped out, you cannot start in single user mode as SQL Server expects at least the master database to be present and readable.Your best options is:
- Restore a copy of the master database to a similiar sql server as a user database, call it oldmaster.
- Detach the database and rename the mdf and ldf to master.mdf and mastlog.ldf
- Take copies of these files and create
model.mdf
modellog.ldf
msdbdata.mdf
msdblog.ldf
- Snap these files into the broken instance and start SQL Server in single user minimal config mode.
- Restore the master database using the command supplied by Bill.
- Restore the MDSB and Models too, restart the SQL Server services and you should be good to go.
Perry,
Thank you for the suggestion! I have to try it in my lab. "similiar sql server " means same version, right? Also, what else you need to verify after SQL server is online?
Thanks
Clare
April 4, 2013 at 11:04 am
dan-572483 (4/4/2013)
Are we talking about SQL2008 or SQL2008 R2? The location of the setup file that rebuilds the system databases is different for R2, and the procedure is not well documented.
I went through this exercise last week - fortunately for me it was a non-prod system I deliberately broke so I could learn exactly how to fix it if the Master DB was actually lost.
For detailed steps see my second message in http://www.sqlservercentral.com/Forums/Topic1437039-1550-1.aspx%5B/quote%5D
It is SQL2008 R2. I read your link and also the article from Gail, very helpful! Lot's of thanks!!!
The production problem I expereiced was that the server OS had to be recovered from file backup and the file back tool ignores .mdf or .ldf files. Therefore, databases system files, resource files and template files were missed together.
For resource and template database files I think I can just copy over from other SQL server that has the same version. For system databases files I am trying to find out all the alternative recovery methods. I do agree the best way is to save file backups of system databases.
April 5, 2013 at 11:01 am
Perry Whittle (4/4/2013)
If all the system database files have been wiped out, you cannot start in single user mode as SQL Server expects at least the master database to be present and readable.Your best options is:
- Restore a copy of the master database to a similiar sql server as a user database, call it oldmaster.
- Detach the database and rename the mdf and ldf to master.mdf and mastlog.ldf
- Take copies of these files and create
model.mdf
modellog.ldf
msdbdata.mdf
msdblog.ldf
- Snap these files into the broken instance and start SQL Server in single user minimal config mode.
- Restore the master database using the command supplied by Bill.
- Restore the MDSB and Models too, restart the SQL Server services and you should be good to go.
Perry, the SQL server will not start after I followed the process you described. The two SQL servers all have SQL2008R2 SP2 Standard Edition. The error message I got from event viewer is "initerrlog: Could not open error log file ''. Operating system error = 3(The system cannot find the path specified.).".
After I run process monitor, I found out the "ACCESS DENIED" message for process sqlservr.exe
Date & Time:4/5/2013 9:18:30 AM
Event Class:Registry
Operation:RegOpenKey
Result:ACCESS DENIED
Path:HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer\Parameters
TID:3228
Duration:0.0001145
Desired Access:Read
When I switched back the original master database files ( which works before I start the testing), the SQL server started normally. The SQL service account has the right permission but why it has Access Denied error when I used the new master database to start up SQL server?
Thanks
April 5, 2013 at 3:39 pm
clare.xia (4/5/2013)
but why it has Access Denied error when I used the new master database to start up SQL server?Thanks
You started the instance in single user minimal config mode?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 5, 2013 at 4:47 pm
Perry Whittle (4/5/2013)
clare.xia (4/5/2013)
but why it has Access Denied error when I used the new master database to start up SQL server?Thanks
You started the instance in single user minimal config mode?
Yes, -m-c-f-T3608. I run from command line though. I think it could be cause by the command line was not run as administrator. The location of the system databases was altered from D drive to C drive to match the server I restored to..Is this something can cause trouble too? I am reinstall the testing SQL now so it system database will sit on C drive. After that, I will retest the solution.
Thanks
Clare
April 5, 2013 at 5:45 pm
It works after the reinstallation. The system database files location have to be same as the server where I restored the master database to.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply