Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Rebuild system database failure Expand / Collapse
Author
Message
Posted Monday, January 14, 2013 5:43 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, October 22, 2013 11:43 AM
Points: 56, Visits: 249
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
Post #1406996
Posted Tuesday, January 15, 2013 12:03 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, March 21, 2014 9:46 AM
Points: 387, Visits: 1,078
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.
Post #1407049
Posted Monday, March 18, 2013 11:26 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 3:05 PM
Points: 355, Visits: 870
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.
Post #1432280
Posted Monday, March 18, 2013 4:15 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 2:54 PM
Points: 611, Visits: 507
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.


.
Post #1432381
Posted Wednesday, April 3, 2013 3:22 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, October 22, 2013 11:43 AM
Points: 56, Visits: 249
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
Post #1438596
Posted Thursday, April 4, 2013 7:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:51 AM
Points: 6,350, Visits: 13,674
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"
Post #1438770
Posted Thursday, April 4, 2013 9:41 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 11:28 AM
Points: 553, Visits: 1,618
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
Post #1438868
Posted Thursday, April 4, 2013 10:23 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, October 22, 2013 11:43 AM
Points: 56, Visits: 249
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
Post #1438896
Posted Thursday, April 4, 2013 11:04 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, October 22, 2013 11:43 AM
Points: 56, Visits: 249
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.
Post #1438914
Posted Friday, April 5, 2013 11:01 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, October 22, 2013 11:43 AM
Points: 56, Visits: 249
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
Post #1439371
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse