Solved

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

Posted on 2009-07-07
7
439 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.
0
Comment
Question by:yvsupport
  • 4
  • 3
7 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24793272
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
 

Author Comment

by:yvsupport
ID: 24793338
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24793435
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:yvsupport
ID: 24793625
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24793728
0
 

Author Comment

by:yvsupport
ID: 24794038
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
 

Accepted Solution

by:
yvsupport earned 0 total points
ID: 24794295
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

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now