Solved

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

Posted on 2009-07-07
7
448 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

685 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