There are certain situation when user wants to start SQL Server Engine in “single user” mode from the start up.
To start SQL Server in single user mode is very simple procedure as displayed below.
Go to SQL Server Configuration Manager and click on SQL Server 2005 Services. Click on desired SQL Server instance and right click go to properties. On the Advance table enter param ‘-m;‘ before existing params in Startup Parameters box.
Make sure that you entered semi-comma after -m. Once that is completed, restart SQL Server services to take this in effect. Once this is done, now you will be only able to connect SQL Server using sqlcmd.
Make sure to remove newly added params after required work is completed to restart it in multi user mode.
Reference : Pinal Dave (https://blog.sqlauthority.com)
56 Comments. Leave new
Epic WIN! works just perfect on sql 2008 r2, thanks for the help!!
Worked a treat.
i have a scenario
my current OS is crashed and took sqlserver with it but luckly i saved the system database; Now i have installed the fresh copy of sql server with the same build and service pack;
i want to replace the existing master database with the previous files (master.mdf and ldf) .. Actuly i am missing backup file , what i balieve that if i had master.bak file then i can easy over write the existing database and can restore to a previouse state but how can i do that with the MDF and LDF files
hopefully i explain it!
Talib Hussain
I just wanted to thank you. I think i’ve been to this post about 5 times now and am now finally just copying in my notes so i don’t have to search for it everytime. Thank you again for this super helpful tip.
Dude ! your a star I must have spent an hour trawling microsoft on how to do this ! yes i found the -m bit but where do i put -m, well done for putting it in terms a noob like me can understand rather than that microsoft waffle.
hi sir i have the -d,so what i do sir,please explain clearly
thanks Pinal
I have changed the startup as single user by adding the “-m;” and now I am stuck in starting rhe SQL server. How do I start sqlserver from sqlcmd?
I need to change the password of sa. Thats the requirement.
Can anyone help?
A trick for some of you.
sqlservr -f”sqlcmd”
This is like single-user mode in that in only allows connections from a particular application name. I’ve used it when I need to be the only one connected to the server and single-user mode keeps getting hogged by other connections.
You the man. Beat my head for 2 hours and in 3 seconds on your site I found out how to do it correctly.
Thank u very much….
its working fine….
I am having a problem on SQL Server 2008R2 when following the instructions above and get this error ” Error 3(The system cannot find the path specified.) occurred while opening file ‘master.mdf’ to obtain configuration information at startup. An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary.”
I tried removing the -m; at the startup Parameter to get back to where I was and now my server won’t start with the same messages reported above, and I tried rebooting the server as well. Any suggestions would be appreciated.
Ignore my last post, I modified the startup parameter to be -m;-dC:\Program Files…..
Not sure if that extra dash is 2008R2 specific, but I compared the startup parameters to another 200R2 instance it it was that way. (minus the -m; of course for the single user mode flag)
Hello,
I tried sqlcmd but I am still getting the same error:
C:\Users\username>sqlcmd
Msg 18461, Level 14, State 1, Server VOTSPRSTR2, Line 1
Login failed for user ‘AD\usersname’. Reason: Server is in single user mode. Only one administrator can connect at this time.
C:\Users\username>
SQL Server Agent is not running only SQLServer is running from the services.msc view….
Thanks,
Dom
Hello,
I tried
> sqlservr -m
>sp_addsrvrolemember ”, ‘sysadmin’
GO
but it could not find the stored procedure… I think there should be something launch before!!!
Thanks,
Dom
Hello,
I restart all steps:
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\sqlservr -m
2012-11-23 12:34:00.48 Logon Error: 18461, Severity: 14, State: 1.
2012-11-23 12:34:00.48 Logon Login failed for user ‘AD\svcsprestore’.
Reason: Server is in single user mode.
Only one administrator can connect at this time. [CLIENT: 10.32.136.60]
I am trying to get in single user mode to add a sysadmin account but it seems the command is trying more and it is looping for the last 10 minutes on the same message above!!!
any idea?
Thanks,
DOm
Hello,
Finally trying again from scratch
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn>net stop ms
sqlserver
The SQL Server (MSSQLSERVER) service is not started.
More help is available by typing NET HELPMSG 3521.
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn>net start m
ssqlserver /m
The SQL Server (MSSQLSERVER) service is starting.
The SQL Server (MSSQLSERVER) service was started successfully.
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn>sqlcmd
1> EXEC master..sp_addsrvrolememeber @loginame = N’AD\dominiqued’, @rolename = N’ sysadmin’
2> GO
1> exit
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn>net stop ms
sqlserver
The SQL Server (MSSQLSERVER) service is stopping.
The SQL Server (MSSQLSERVER) service was stopped successfully.
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn>net start m
ssqlserver
The SQL Server (MSSQLSERVER) service is starting.
The SQL Server (MSSQLSERVER) service was started successfully.
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn>
Now I have access to the databases as sysadmin…
Not sure what the issues were but try 4 times work…
Thanks,
Dom
Okay, I had a similar problem except this was way worse. Imagine this:
Cannot open configuration manager.
cannot open the instance property
cannot log into the database via sqlcmd
what do you do then? forget it, install a new instance and move the database over to the new instance.
Hello everybody,
I need your help!!!! I am very frustrated doing lots of things to recover my sql server but it continues failing.
My scenario is this: VMWARE –> VM: Win2012 Server, SQL Server 2008, standard instance –> MSSQLSERVER
4 partitions in a basic disk. System, c(boot), e and f
in c it is located master, model and msdb (mdf and logs)
in e –> mdf for a DB called Portugal and Temddb.mdf
in f –> the logs for databases
What I did is to copy all the data from e and f, delete these volumen, extend c and create e and f again as separate disk.Then i paste all data of old e: in new e: and the same for f:
I cannot start SQL Server (MSSQLSERVER) service. I get this log error:
2013-01-24 09:33:12.20 Server Microsoft SQL Server 2008 (SP3) – 10.0.5512.0 (X64)
Aug 22 2012 19:25:47
Copyright (c) 1988-2008 Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.2 (Build 9200: ) (VM)
2013-01-24 09:33:12.20 Server (c) 2005 Microsoft Corporation.
2013-01-24 09:33:12.20 Server All rights reserved.
2013-01-24 09:33:12.20 Server Server process ID is 3304.
2013-01-24 09:33:12.20 Server System Manufacturer: ‘VMware, Inc.’, System Model: ‘VMware Virtual Platform’.
2013-01-24 09:33:12.20 Server Authentication mode is MIXED.
2013-01-24 09:33:12.20 Server Logging SQL Server messages in file ‘C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG’.
2013-01-24 09:33:12.20 Server This instance of SQL Server last reported using a process ID of 36 at 24/01/2013 9:27:24 (local) 24/01/2013 8:27:24 (UTC). This is an informational message only; no user action is required.
2013-01-24 09:33:12.20 Server Registry startup parameters:
-d C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf
-e C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG
-l C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
2013-01-24 09:33:12.21 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2013-01-24 09:33:12.21 Server Detected 4 CPUs. This is an informational message; no user action is required.
2013-01-24 09:33:12.26 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2013-01-24 09:33:12.28 Server Node configuration: node 0: CPU mask: 0x000000000000000f Active CPU mask: 0x000000000000000f. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2013-01-24 09:33:12.30 spid7s Starting up database ‘master’.
2013-01-24 09:33:12.51 spid7s FILESTREAM: effective level = 0, configured level = 0, file system access share name = ‘MSSQLSERVER’.
2013-01-24 09:33:12.55 spid7s SQL Trace ID 1 was started by login “sa”.
2013-01-24 09:33:12.55 spid7s Starting up database ‘mssqlsystemresource’.
2013-01-24 09:33:12.58 spid7s The resource database build version is 10.00.5500. This is an informational message only. No user action is required.
2013-01-24 09:33:12.69 spid7s Server name is ‘CE-NAV02’. This is an informational message only. No user action is required.
2013-01-24 09:33:12.69 spid11s Starting up database ‘model’.
2013-01-24 09:33:12.84 spid11s Clearing tempdb database.
2013-01-24 09:33:12.85 spid13s A new instance of the full-text filter daemon host process has been successfully started.
2013-01-24 09:33:12.85 Server A self-generated certificate was successfully loaded for encryption.
2013-01-24 09:33:12.86 spid11s Error: 5123, Severity: 16, State: 1.
2013-01-24 09:33:12.86 spid11s CREATE FILE encountered operating system error 5(failed to retrieve text for this error. Reason: 15100) while attempting to open or create the physical file ‘F:\LOGS\TempDB\templog.ldf’.
2013-01-24 09:33:12.86 spid11s Error: 5123, Severity: 16, State: 1.
2013-01-24 09:33:12.86 spid11s CREATE FILE encountered operating system error 5(failed to retrieve text for this error. Reason: 15100) while attempting to open or create the physical file ‘E:\Data\TempDB\tempdb.mdf’.
2013-01-24 09:33:12.86 Server Server is listening on [ ‘any’ 1433].
2013-01-24 09:33:12.86 Server Server is listening on [ ‘any’ 1433].
2013-01-24 09:33:12.86 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
2013-01-24 09:33:12.86 Server Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ].
2013-01-24 09:33:12.86 Server Server is listening on [ ::1 1434].
2013-01-24 09:33:12.86 Server Server is listening on [ 127.0.0.1 1434].
2013-01-24 09:33:12.86 Server Dedicated admin connection support was established for listening locally on port 1434.
2013-01-24 09:33:12.86 spid14s Starting up database ‘ReportServer’.
2013-01-24 09:33:12.86 spid15s Starting up database ‘ReportServerTempDB’.
2013-01-24 09:33:12.86 spid16s Starting up database ‘Portugal’.
2013-01-24 09:33:12.86 spid13s Starting up database ‘msdb’.
2013-01-24 09:33:12.87 spid14s Error: 17204, Severity: 16, State: 1.
2013-01-24 09:33:12.87 spid14s FCB::Open failed: Could not open file E:\Data\Data\ReportServer.mdf for file number 1. OS error: 5(failed to retrieve text for this error. Reason: 15105).
2013-01-24 09:33:12.87 spid15s Error: 17204, Severity: 16, State: 1.
2013-01-24 09:33:12.87 spid15s FCB::Open failed: Could not open file E:\Data\Data\ReportServerTempDB.mdf for file number 1. OS error: 5(failed to retrieve text for this error. Reason: 15105).
2013-01-24 09:33:12.87 spid14s Error: 5120, Severity: 16, State: 101.
2013-01-24 09:33:12.87 spid14s Unable to open the physical file “E:\Data\Data\ReportServer.mdf”. Operating system error 5: “5(failed to retrieve text for this error. Reason: 15105)”.
2013-01-24 09:33:12.87 spid15s Error: 5120, Severity: 16, State: 101.
2013-01-24 09:33:12.87 spid15s Unable to open the physical file “E:\Data\Data\ReportServerTempDB.mdf”. Operating system error 5: “5(failed to retrieve text for this error. Reason: 15105)”.
2013-01-24 09:33:12.87 spid16s Error: 17204, Severity: 16, State: 1.
2013-01-24 09:33:12.87 spid16s FCB::Open failed: Could not open file E:\Data\Data\Portugal.mdf for file number 1. OS error: 5(failed to retrieve text for this error. Reason: 15105).
2013-01-24 09:33:12.87 spid16s Error: 5120, Severity: 16, State: 101.
2013-01-24 09:33:12.87 spid16s Unable to open the physical file “E:\Data\Data\Portugal.mdf”. Operating system error 5: “5(failed to retrieve text for this error. Reason: 15105)”.
2013-01-24 09:33:12.87 spid14s Error: 17204, Severity: 16, State: 1.
2013-01-24 09:33:12.87 spid14s FCB::Open failed: Could not open file F:\LOGS\ReportServer_1.LDF for file number 2. OS error: 5(failed to retrieve text for this error. Reason: 15105).
2013-01-24 09:33:12.87 spid15s Error: 17204, Severity: 16, State: 1.
2013-01-24 09:33:12.87 spid15s FCB::Open failed: Could not open file F:\LOGS\ReportServerTempDB_1.LDF for file number 2. OS error: 5(failed to retrieve text for this error. Reason: 15105).
2013-01-24 09:33:12.87 spid14s Error: 5120, Severity: 16, State: 101.
2013-01-24 09:33:12.87 spid14s Unable to open the physical file “F:\LOGS\ReportServer_1.LDF”. Operating system error 5: “5(failed to retrieve text for this error. Reason: 15105)”.
2013-01-24 09:33:12.87 spid14s Error: 5105, Severity: 16, State: 1.
2013-01-24 09:33:12.87 spid14s A file activation error occurred. The physical file name ‘F:\LOGS\ReportServer_1.LDF’ may be incorrect. Diagnose and correct additional errors, and retry the operation.
2013-01-24 09:33:12.87 spid15s Error: 5120, Severity: 16, State: 101.
2013-01-24 09:33:12.87 spid15s Unable to open the physical file “F:\LOGS\ReportServerTempDB_1.LDF”. Operating system error 5: “5(failed to retrieve text for this error. Reason: 15105)”.
2013-01-24 09:33:12.87 spid15s Error: 5105, Severity: 16, State: 1.
2013-01-24 09:33:12.87 spid15s A file activation error occurred. The physical file name ‘F:\LOGS\ReportServerTempDB_1.LDF’ may be incorrect. Diagnose and correct additional errors, and retry the operation.
2013-01-24 09:33:12.87 spid16s Error: 17204, Severity: 16, State: 1.
2013-01-24 09:33:12.87 spid16s FCB::Open failed: Could not open file E:\Data\Data\Portugal_1.ndf for file number 3. OS error: 5(failed to retrieve text for this error. Reason: 15105).
2013-01-24 09:33:12.87 spid16s Error: 5120, Severity: 16, State: 101.
2013-01-24 09:33:12.87 spid16s Unable to open the physical file “E:\Data\Data\Portugal_1.ndf”. Operating system error 5: “5(failed to retrieve text for this error. Reason: 15105)”.
2013-01-24 09:33:12.87 spid16s Error: 5120, Severity: 16, State: 9.
2013-01-24 09:33:12.87 spid16s Unable to open the physical file “E:\Data\Data\Portugal_1.ndf”. Operating system error 5: “5(failed to retrieve text for this error. Reason: 15105)”.
2013-01-24 09:33:12.87 spid16s Error: 17204, Severity: 16, State: 1.
2013-01-24 09:33:12.87 spid16s FCB::Open failed: Could not open file F:\LOGS\Portugal_2.ldf for file number 2. OS error: 5(failed to retrieve text for this error. Reason: 15105).
2013-01-24 09:33:12.87 spid16s Error: 5120, Severity: 16, State: 101.
2013-01-24 09:33:12.87 spid16s Unable to open the physical file “F:\LOGS\Portugal_2.ldf”. Operating system error 5: “5(failed to retrieve text for this error. Reason: 15105)”.
2013-01-24 09:33:12.87 spid16s Error: 5105, Severity: 16, State: 1.
2013-01-24 09:33:12.87 spid16s A file activation error occurred. The physical file name ‘F:\LOGS\Portugal_2.ldf’ may be incorrect. Diagnose and correct additional errors, and retry the operation.
2013-01-24 09:33:12.91 spid11s Error: 17204, Severity: 16, State: 1.
2013-01-24 09:33:12.91 spid11s FCB::Open failed: Could not open file E:\Data\TempDB\tempdb.mdf for file number 1. OS error: 5(failed to retrieve text for this error. Reason: 15105).
2013-01-24 09:33:12.91 spid11s Error: 5120, Severity: 16, State: 101.
2013-01-24 09:33:12.91 spid11s Unable to open the physical file “E:\Data\TempDB\tempdb.mdf”. Operating system error 5: “5(failed to retrieve text for this error. Reason: 15105)”.
2013-01-24 09:33:12.91 spid11s Error: 1802, Severity: 16, State: 4.
2013-01-24 09:33:12.91 spid11s CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
2013-01-24 09:33:12.91 spid11s Error: 5123, Severity: 16, State: 1.
2013-01-24 09:33:12.91 spid11s CREATE FILE encountered operating system error 5(failed to retrieve text for this error. Reason: 15105) while attempting to open or create the physical file ‘E:\Data\TempDB\tempdb.mdf’.
2013-01-24 09:33:12.91 spid11s Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
2013-01-24 09:33:12.91 spid11s SQL Trace was stopped due to server shutdown. Trace ID = ‘1’. This is an informational message only; no user action is required.
2013-01-24 09:33:13.14 Logon Error: 17187, Severity: 16, State: 1.
2013-01-24 09:33:13.14 Logon SQL Server is not ready to accept new client connections. Wait a few minutes before trying again. If you have access to the error log, look for the informational message that indicates that SQL Server is ready before trying to connect again. [CLIENT: fe80::759f:e712:daa1:5f25%12]
2013-01-24 09:33:13.17 Logon Error: 17187, Severity: 16, State: 1.
2013-01-24 09:33:13.17 Logon SQL Server is not ready to accept new client connections. Wait a few minutes before trying again. If you have access to the error log, look for the informational message that indicates that SQL Server is ready before trying to connect again. [CLIENT: ]
As you can see there are lots of errors …
any one can help me? Thank you so much.
best regards!!
NACHO
it is solved! Thank you anyway. i have restored backups from databases that were not system’s backups and give rights into the e : and f: volume to allow access from sql to tempdb.
Best Regards,
NACHO