February 23, 2006 at 7:07 pm
Greetings, This is somewhat of a newbie question and I hope I can explain it clearly. We are testing backup/restore of a new database prior to putting it into production. As I understand it I must be in single user mode in order to restore the master database. I have tried two methods for putting the database in single user mode,
1) stopping services and from the command line issuing the command sqlsrvr -m
2) Under sqlserver properties in the Enterprise Manager under startup options adding -m
Then restarting the services. I then try to connect using the query analyzer as the sa user or using the Enterprise Manager and I am told I can not connect as the database is in single user mode and only one administrator can be connected at any one time. I don't understand the problem.
Additional information, I reinstalled sql server prior to restoring the database and windows had also been reinstalled. I remember from my training that sql server will not recognize users or groups that have been recreated in Windows 2000 but, in that this is a reinstallation of sql server I don't believe that should have any bearing. Any light anyone can shed on this issue will be greatly appreciated.
Thanks.
February 23, 2006 at 7:33 pm
Have you tried closing EM and then open up a sql connection using QA?
February 23, 2006 at 8:05 pm
Yes I have and it returns the same error. I don't understand it. Are there some permissions I need to set on the sa user in order to do this?
February 23, 2006 at 10:26 pm
Please following these instructions as is:
February 24, 2006 at 10:00 am
Greetings, I have tried that and I receive the message indicating the database is in single user mode and only one login is permitted. I don't understand why it thinks something else is logged in. Must I stop any of the services?
February 24, 2006 at 11:24 am
Stop the Agent service. As long as that is running, jobs can and will run. Also, what login did you use to put the database in single user mode? Try using that login. Remember...Enterprise Manager is one connection and if you open Query Analyzer, that's a second connection. In single_user mode, you can only have one connection at a time.
-SQLBill
February 24, 2006 at 12:07 pm
Thanks for the responses. I resolved the problem. I stopped the SQLSERVERAGENT service nad then, in the Enterprise Manager chose the SQL Server, right clicked and asked it to connect ( a step I didn't try previously) and was successful. Thank you.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply