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