SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Rebuild system database failure


Rebuild system database failure

Author
Message
clare.xia
clare.xia
SSChasing Mays
SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)

Group: General Forum Members
Points: 642 Visits: 250
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
SQL Show
SQL Show
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1455 Visits: 1078
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.
MMartin1
MMartin1
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7037 Visits: 2033
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.

----------------------------------------------------
How to post forum questions to get the best help
Bill (DBAOnTheGo)
Bill (DBAOnTheGo)
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1231 Visits: 599
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.

.
clare.xia
clare.xia
SSChasing Mays
SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)

Group: General Forum Members
Points: 642 Visits: 250
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
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52387 Visits: 17672
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.



-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
dan-572483
dan-572483
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3473 Visits: 2000
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
clare.xia
clare.xia
SSChasing Mays
SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)

Group: General Forum Members
Points: 642 Visits: 250
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
clare.xia
clare.xia
SSChasing Mays
SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)

Group: General Forum Members
Points: 642 Visits: 250
dan-572483 (4/4/2013)
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


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.
clare.xia
clare.xia
SSChasing Mays
SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)

Group: General Forum Members
Points: 642 Visits: 250
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search