March 3, 2004 at 8:46 am
I am running SQL Server 2000 on a Windows Server 2000. I have started up the server in single user mode from the command prompt using sqlservr.exe -c -m so that I can restore the master database. I was able to restore the master database but now I'm unable to get the server out of single user mode! How do I do this?
I tried running sqlservr.exe without any options from the command prompt but got errors. I am also unable to open the server in Enterprise manager. It is giving me the error: "A connection could not be established to server. access denied"
March 3, 2004 at 10:18 am
I assume you have tried attaching with sa as well and are unable. You may need to rebuild master. (Rebuildm.exe) SEE BOL also before attempting this:
If a current backup of master is not available, or the backup cannot be restored because Microsoft SQL Server cannot start due to severe damage to master, master can be rebuilt using the Rebuild Master utility. When master has been rebuilt, a current backup of master can be restored, or the user databases, backup devices, SQL Server logins, and so on, can be re-created using SQL Server Enterprise Manager, or the original scripts used to create those entries.
Important The Rebuild Master utility rebuilds master completely. Because the msdb and model system databases are rebuilt as well, it may be necessary to restore backups of those databases. If any databases are restored or attached after master is rebuilt, it is necessary to specify the same sort order, code page, and Unicode collation when rebuilding master as used by those databases. If the same sort order, code page, and Unicode collation are not used, then it will not be possible to restore or attach those databases.
The general steps required to rebuild master completely if no backup is available are:
Important The compact disc or shared network directory containing the SQL Server installation software is required to rebuild the master database.
When master has been re-created and any changes have been reapplied, back up master immediately.
Francis
March 3, 2004 at 10:35 am
I have a good backup of the master DB. It's just that after setting the server to single user mode and restoring the master database from the backup I am unable to access the server. How do you reset the server to multi user mode?
March 3, 2004 at 10:50 am
See BOL on startup options. I believe you need the -d, -e and the -l options showing where is the master, error and log files. If you leave them out SQL Server goes to your registry which may now not have valid entries. If this doesn't work try using the -f option. After using this you will need to go in through EM and fix the -d -e and -l options via the Properties screen.
Francis
March 4, 2004 at 2:28 am
use this in the query analyzer
ALTER DATABASE database SET MULTI_USER
This command returns access to the database to its normal operating state.
Alamir Mohamed
Alamir_mohamed@yahoo.com
March 4, 2004 at 3:16 am
Can you post the error messages when you try and run sqlsrvr from command line without any parameteres ?
Also did you restart the service using Control Panel -> Admin Tools -> Services ?
March 4, 2004 at 2:44 pm
Since SQL Server is in single user mode I cannot connect to it thru Enterprise Manager or Query Analyzer
March 4, 2004 at 2:48 pm
I take it you tried executing sqlservr with the -d, -e and the -l options and it didn't work. Did the -f option help?
Francis
March 4, 2004 at 2:51 pm
You have to stop the sql server service before starting up the server in single user mode from the command prompt. After getting the errors at the command prompt I attempted to start the service but I got an access denied error.
The error messages were that it could not locate the mdf database files on the drive that I had sql server installed on. I had intentionally put them on another drive (my disk array). What I had to do was copy the mdf's from the disk array back the the local drive (D) and then start the service up from the command prompt with sqlservr.exe -f
Then I was able to shut down the service by doing a Ctrl-C and start up Enterprise Manager. When I did this however my user DB's came up suspect but I was able to restore them from a backup tape and they were fine.
March 4, 2004 at 2:53 pm
Yes the -f option let me start the service and then do a Ctrl-C and stop the service. Then I was able to open Enterprise Manager.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply