Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 13-02-2009
Member
 
Join Date: Feb 2009
Posts: 8
Starting SQL server in single user mode?

Hello!

I want to know how to start SQL in single user mode?

Regards!
Reply With Quote
  #2  
Old 13-02-2009
Member
 
Join Date: May 2008
Posts: 35
Re: Starting SQL server in single user mode?

Starting SQL Server in Single-User Mode

Under certain circumstances, you may have to start an instance of SQL Server in single-user mode by using the startup option -m. For example, you may want to change server configuration options or recover a damaged master database or other system database. Both actions require starting an instance of SQL Server in single-user mode.

Starting SQL Server in single-user mode enables any member of the computer's local Administrators group to connect to the instance of SQL Server as a member of the sysadmin fixed server role.

When you start an instance of SQL Server in single-user mode, note the following:

* Only one user can connect to the server.
* The CHECKPOINT process is not executed. By default, it is executed automatically at startup.

When you start an instance of SQL Server in single-user mode, SQL Server Management Studio can connect to SQL Server. Object Explorer in Management Studio might fail because it requires more than one connection for some operations. To manage SQL Server in single-user mode, execute Transact-SQL statements by connecting only through the Query Editor in Management Studio, or use the sqlcmd utility.
Reply With Quote
  #3  
Old 13-02-2009
Member
 
Join Date: May 2008
Posts: 2,383
Re: Starting SQL server in single user mode?

Sql Server can be started from command line, using the Sqlserver.exe. This exe has some very important parameters with which a Dba should be familiar with.
-m is used for starting Sql Server in single user mode and -f is used to start the Sql Server in minimal configuration mode
__________________
The FIFA Manager 2009 PC Game
Reply With Quote
  #4  
Old 13-02-2009
Member
 
Join Date: Apr 2008
Posts: 1,948
Re: Starting SQL server in single user mode?

Single User mode allows only one single user to connect to the database.
Even if SQL server is in Single User Mode, you can connect the SQL Query Analyzer with the SA user.

Single user mode specifies that only one user at a time can access the database. Basically it is used for maintenance purposes.

If any other users are connected to the database and you switch the database to single user mode then their connections to the database will be closed without any warning message.

Following are the steps to start SQL server in Single User mode -

On your server : Start >> Run >> type in CMD and hit Ok >> it will open up Command Prompt >> here execute the following command

net stop MSSqlServer

>> it will stop the SQL service >> now to start SQL in Single User mode execute following command

sqlservr -m

####

How to Set a single database into Single User Mode :-

You can set a single database into single user mode using an alternative procedure but this process cannot be used to set the MASTER database into single user mode.

# Connect to SQL Server using SQL Query Analyzer with SA user

# Execute the following command -

alter database database-name set SINGLE_USER

where ?database-name? is the actual name of the database.

This cannot be used to place the databases MASTER, MSDB or TEMPDB in single user mode.

# To set the database back into normal multi-user mode use, execute the following command -

alter database db-name set MULTI_USER
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Starting SQL server in single user mode?"
Thread Thread Starter Forum Replies Last Post
How to change the product edition from Multi User to Single User in Tally.ERP 9 fumble Guides & Tutorials 1 20-04-2011 04:08 PM
Mac OSX:Single user password reset Mishraji Operating Systems 5 12-11-2010 02:50 AM
How to reset User Password via single user mode in mac os x Jay Scott Operating Systems 3 10-11-2010 07:00 PM
Booting to single-user mode Aarav Operating Systems 6 02-07-2010 11:20 AM
0x85010004 for single user on sbs2003 jensskov Operating Systems 2 01-06-2009 09:23 AM


All times are GMT +5.5. The time now is 04:42 PM.