Solved

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

Posted on 2009-07-07
7
453 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

 

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

Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

719 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