SQL Server Performance Forum – Threads Archive

restore master db, how?

An error popped up when I attempted to restore the
master database. But
EXEC sp_dboption ‘master’, ‘single user’, ‘true’
results in error also:
Server: Msg 5058, Level 16, State 5, Line 1
Option ‘SINGLE_USER’ cannot be set in database ‘MASTER’.
sp_dboption command failed.

---

Single user is not allowed in master database.
Is restricd access to db administrator.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

---

How about: RESTORE DATABASE { database_name | @database_name_var }
[ FROM < backup_device > [ ,…n ] ]
[ WITH
[ RESTRICTED_USER ]
[ [ , ] FILE = { file_number | @file_number } ]
[ [ , ] PASSWORD = { password | @password_variable } ]
[ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
[ [ , ] MOVE ‘logical_file_name’ TO ‘operating_system_file_name’ ]
[ ,…n ]
[ [ , ] KEEP_REPLICATION ]
[ [ , ] { NORECOVERY | RECOVERY | STANDBY = undo_file_name } ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] REPLACE ]
[ [ , ] RESTART ]
[ [ , ] STATS [ = percentage ] ]
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

---

BTW, detatch and attach doesn’t work either.
I got an error:
Server: Msg 7940, Level 16, State 1, Line 1
System databases master, model, msdb, and tempdb cannot be detached.
when I executed the cmd:
EXEC sp_detach_db ‘master’, ‘true’

---

This command has the same restriction: single user mode. Here is the error:
Server: Msg 3108, Level 16, State 1, Line 1
RESTORE DATABASE must be used in single user mode when trying to restore the master database.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

---

BOL: USE master
GO
RESTORE DATABASE master
FROM TAPE = ‘\.Tape0’
GO under single user mode is what the BOL specified. However
when I used file instead of tape (single user mode from
prompt), it didn’t work: USE master
GO
RESTORE DATABASE master
FROM file = ‘d:dirfile’
GO
Incorrect syntax near the keyword ‘file’

---

About single user, it happens to me.
Is neccesary to be the only user and with one connection open.
Close EM, be along with SQL Analyzer with only one connection too.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

---

First,use command "sqlservr.exe -m " to start SQL Server with simple user mode.
execute RESTORE DATABASE to restore the master DB. Jelly.

---

I tried both
sqlservr.exe -m
and
sqlservr.exe -c -m
to start in single user mode (shutdown service prior to this)
I got the same error:
Incorrect syntax near the keyword ‘file’
if I execute:
use master
restore database master from file = ‘g:12032003’ or
restore database master from file ‘g:12032003’ I have no problem to start sql server in a single user mode, the
problem is, I couldn’t get the restore syntax correct. Sounds
simple, I wish BOL would put a real example somewhere for restore from
a file, not a tape.

---

I found an example of how to rebuild/restore master at http://www.informit.com/content/index.asp?product_id={A31FA53B-0BD1-4D8B-8F8B-7B61493B1AB8} To summarize the steps are
1) add the backup device
2) stop sql
3) bring it up in single user mode
4) login as sa/administrator
5) restore from the backup device
rest of steps in article It looks like to restore from file, they recommend that you add the backup device. Try it. Its probably faster/easier than banging your head against a brick wall.

---

It should be: Restore database master from DISK = ‘g:1232003’ i.e. Change the ‘file’ to ‘disk’

---

Thanks Chris. So the MS BOL is not quite clear how to backup master from file.
Do it from tape works based on their example. For disk, the
critical step I missed is, to fool MS SQL by building a
logic device based on the backed up physical file, then
restore from the logic, not the physical, device.
Interesting.

---

Thanks.
There is no need to build a logic device then.

---

BTW, what is the reason behind restoring master database?
Try considering using REBUILDM utility to rebuild the master database.
_________
Satya SKJ
Moderator
SQL-Server-Performance.Com

---

]]>