We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

SQL2000 SP3 master db restore failing even when in single user mode using sqlservr.exe -c -m

yvsupport
yvsupport asked
on
Medium Priority
493 Views
Last Modified: 2012-05-07
Hi all,

I'm struggling to do a master DB restore from backup. I've done before so not sure why this box is a problem. I've stopped SQL, SQL Agent & Report Server services. Started SQL in single user mode using sqlservr.exe -c -m, opened only query analyzer as sa (no object browser) and run the query:

RESTORE DATABASE master FROM DISK = 'c:\master_db_200907020100.BAK'
but I still recieve:
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.

I'm certain the database is in single user mode and I've stopped all services that I can possibly think of that may be accessing SQL in some way.

I've also run an sp_who and there are 16 spids, but they are all sa.

Anyone got any ideas? Thanks in advance.
Comment
Watch Question

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Error says that your instance is not in single user mode.
Kindly try it again with the steps mentioned below:

http://msdn.microsoft.com/en-us/library/ms190679.aspx

Hope this helps.

Author

Commented:
Hi rrjegan17,

I'm starting SQL using sqlservr.exe -c -m so i think it is in single user mode but still get the error. is there a way to check?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Just issue the statement sqlservr.exe -m and check it out.

Kindly try out using

-m"Microsoft SQL Server Management Studio - Query" to open a query window and handle it over there.

Author

Commented:
Tried just -m but still the same problem. This is SQL 2000 so no management studio, but yes I am running the restore in query analyser but have also tried doing it in enterprise manager.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:

Author

Commented:
Hi rrjegan17,

Yes that is exactly what I've tried already.

I think that there is something on the server using SQL the second I start it in single user mode. If I do an sp_who2, there are more spid's than i think there should be in single user mode. I've attached a screenshot along with another server I have placed in single user mode that does allow me to restore master.
sp-who2.doc
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.