November 14, 2003 at 10:29 am
Please help me!! I have spent days on this and am still puzzled. My main problem seems to be getting SQL Server to start in single mode. Some times it does, most of the time it doesn't.
Please could someone tell me the exact steps or where I am going wrong
These are my steps:
Shut down EM and QA
Open a command prompt Start-> Programs->Accessories -> C:\ Command Prompt
Run net stop mssqlserver
cd \Program Files\Microsoft SQL Server\MSSQL\BINN
(Is this really necessary, next line runs without changing directory)
sqlservr.exe –c –m
I think I should see the line:
SQL Server started in single user mode.
Instead I see:
SQL Server is starting at priority class 'normal
If I then open Query Analyzer and run:
USE MASTER
GO
RESTORE DATABASE MASTER
FROM DISK = 'C:\TempBackup\master\master_db_200311140100.BAK'
GO
I get the message:
RESTORE DATABASE must be used in single user mode when trying to restore the master database.
As mentioned, I thought I had this cracked because I have got it to work once or twice and then gone on to restore msdb, a full back up and multiple transaction logs.
One of the times I got master to work was by running sqlservr.exe –c –m from start-> run.
Is it necessary to open a window?
November 14, 2003 at 10:52 am
I have done that before :
Stop SQL Server, open command line and start SQL Server in Single user mode.
Change directory to the folder where “sqlservr.exe” file is and run command:
Sqlservr.exe –c –f –m –s Andar
Open SQL Query analyzer and run command to restore master database:
RESTORE DATABASE [master] FROM DISK = 'J:\SQL backups\Full backup\master.bak'
November 14, 2003 at 11:11 am
sorry, 'Andar' is my SQL Server instance name, if you have default instance then just run Sqlservr.exe –c –f –m –s
November 14, 2003 at 11:22 am
your command "sqlservr.exe –c –m" also should work, I think you have more than one SQL Server instance. In the command window you must switch to the directory where SQL instance you want to stat resides, before running command sqlservr.exe –c –m
November 16, 2003 at 5:21 am
I am fairly certain that I have only one instance on this server. How can I tell?
In Enterprise Manager I have created a linked Server to my Production Server. Is this likely to be causing a problem?
I have been starting the server in directory C:\Program Files\Microsoft SQL Server\MSSQL\BINN
How can I tell whether I am in the right directory? sqlservr.exe is in this directory.
I won't be able to try the command line Sqlservr.exe –c –f –m –s until I am back in the office next Wed. Do you think this might work when sqlservr.exe –c –m doesn't. I could not find -s in BOL, what does this do?
Thanks for your help
Stefan
November 17, 2003 at 7:25 am
I have restored master several times. Not sure what the -c is for, but I use the -m. I find I must leave the command window open while I run the restore command in the query analyzer. Once I close the window, I am no longer in the single user mode. Hope this helps.
November 17, 2003 at 8:31 am
-c indicates that SQL Server should run as a program, and not as a Windows 2000 service. Using this parameter makes SQL Server start more quickly in a command window.
-f indicates that SQL Server should start in a "minimal" configuration. You would specify this option when you manually set a configuration setting that prevents SQL Server from starting normally. It's an emergency mode meant to allow you to fix any mistakes you make.
-m indicates that SQL Server will start in single-user mode, and only one user is allowed to connect to SQL Server at any time with a single connection. You can set this method during recovery situations after losing critical data files (such as recovering your master database from a backup).
-s specifies the instance name that you are starting. Leave off this parameter when you're manually starting a default instance of SQL Server. Otherwise, you must specify this option, even when you are calling SQLServr.exe from the \binn directory of a named instance. ( I used it to start my instance Andar)
You say In Enterprise Manager you have created a linked Server to your Production Server.
that means SQL Server was running on your Production server ? If yes than you had 2 users connections, that is not single user mode. That is what seems to me.
You should close not only all connection ( as EM, QA ) , you should STOP SQL Server on your Production server and run sqlservr.exe, not remotely but directly on the Production Server.
November 17, 2003 at 9:24 am
quote:
You say In Enterprise Manager you have created a linked Server to your Production Server.that means SQL Server was running on your Production server ? If yes than you had 2 users connections, that is not single user mode. That is what seems to me.
You should close not only all connection ( as EM, QA ) , you should STOP SQL Server on your Production server and run sqlservr.exe, not remotely but directly on the Production Server.
Sorry if I did not explain myself correctly
My development machine is the one I am trying to restore to. It has a local development Server and also a link to the Production server. It is the development server I am trying to restore to, not the Production Server.
I don't think you have suggested I do anything which I have not already tried. Although maybe I will try unlinking from the Production server.
December 2, 2003 at 3:21 am
Problem solved - I have been able to repeatedly test disaster recovery - Just found it amazing how many hurdles I had to overcome and document in the learning process.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply