March 19, 2010 at 10:46 pm
Its shows message
"Msg 5058, Level 16, State 5, Line 1
Option 'SINGLE_USER' cannot be set in database 'master'.".
how can i set masterdatabase to single usermode.Is there any way to set master databse into single user mode.
second question is how to rebuild master database.I know that using Rebuildm.exe wizard we can rebuild master database.but the tool is not in my 90\binn folder.
second option i tried is to restore the mastedata base where i have backup.It will not allow to restore master database.
Malleswarareddy
I.T.Analyst
MCITP(70-451)
March 20, 2010 at 4:00 am
Master cannot be set into single user mode. Why are you trying?
rebuildm is a SQL 2000 command for rebuilding the master database. In SQL 2005 the setup command is used with certain switches.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 20, 2010 at 8:18 am
it sounds like you want to restore your master database for which you have to start the instance in single user mode, not set the database to single user. see this:
http://technet.microsoft.com/en-us/library/ms188236(SQL.90).aspx
basically start SQL with the -m option
net start mssqlserver -m
you can then restore your master database using the standard restore command with the replace option.
as the instance is in single user mode only one connection is allowed so usually the sqlcmd utility is used to run the restore. However you can use SSMS if before you start SQL in user mode in SSMS you go to tools/options and select 'open new query window' in the at startup box, then exit SSMS
---------------------------------------------------------------------
March 20, 2010 at 9:12 am
Hi,
from SSMS i put new query window.but how can i restore using query or by right click on database/restore option.Itried both that not worked.
by using command propmt it worked.
using sqlcmd utility it worked fine.but using ssms it not worked.
Malleswarareddy
I.T.Analyst
MCITP(70-451)
March 20, 2010 at 9:26 am
malleswarareddy_m (3/20/2010)
using sqlcmd utility it worked fine.but using ssms it not worked.
Possibly because Object Explorer took the only allowed connection. It's one of the reasons I prefer to use SQLCMD when the server's in single user mode. No unexpected connections.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 20, 2010 at 10:21 am
if you had not set the 'open new query window' as your startup mode option up front before restarting SQL in single user mode then yes object explorer would gave taken the connection on starting SSMS, but if you set that option a single query window is opened and you can use it to execute your query.
---------------------------------------------------------------------
March 20, 2010 at 10:33 am
I prefer to have SSMS set to open an empty environment. No object explorer connections, no query window connections.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 20, 2010 at 10:39 am
Then I would have thought you would prefer to still use SSMS when the server is in single user mode? Its got to be easier than sqlcmd especially if typing more verbose commands such as moving the resource database.
IMHO anyway.
---------------------------------------------------------------------
March 20, 2010 at 11:06 am
Just too easy to have something else in SSMS grab the only connection. Specially if there's 3rd party add-ins in SSMS (which I have). SQLCMD I know there's only one connection.
Probably more habit than anything.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply