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

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.
yvsupportAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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.
0
yvsupportAuthor 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?
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

yvsupportAuthor 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.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
0
yvsupportAuthor 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
0
yvsupportAuthor Commented:
I've just answered my own question. Something was grabbing SQL, no idea what but MS page shows how to start in single user mode only allowing one application access. In this case query analyser so i can do my restore. The command is sqlservr.exe -m"query analyzer"

The website is:
http://msdn.microsoft.com/en-us/library/ms188236.aspx

I can't believe I've spent days on this one!! Thanks for your help anyway.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.