1

I am in the process of writing a restore database script. I want to be able to connect as sa and run the restore script.

When I run SET SINGLE_USER does this mean that my login is the only user access to the database? Or how is this determined? I want to use this as I get an error saying other connections are active.

Below is my restore script to date. Are there any improvements I can make to this?

ALTER DATABASE AdventureWorks SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

ALTER DATABASE AdventureWorks SET READ_ONLY;
GO

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK' 
WITH
MOVE N'AdventureWorks_dat' to N'H:\Data\AdventureWorks.mdf',
MOVE N'AdventureWorks_log' to N'E:\Logs\AdventureWorks.ldf',
STATS = 5;

GO

ALTER DATABASE AdventureWorks SET MULTI_USER;
GO

ALTER DATABASE AdventureWorks SET READ_WRITE WITH NO_WAIT;
GO
2
  • 1
    There is no benefit to set READ_ONLY while you're restoring. The database will be in RESTORING mode and not accessible any way.
    – DenisT
    Mar 26 '15 at 16:57
  • Are you using this database in this time? I mean on the left top side of your screen ( dont know how to say this in english ) ? on "avaliable databases" ? Try: use MASTER in the beggining of your query.
    – Racer SQL
    Mar 26 '15 at 17:05
2

SET SINGLE_USER means only one user can connect to the database at a time. If someone or some service (SSIS, SSRS, SQL Agent, etc) connects before you than you will have to wait for them to disconnect

4
  • OK. So how can I ensure that I can restore the database immediately without waiting for other connections to finish? If I have SET SINGLE_USER and someone else is connected then I imagine I will not be able to kill their connection?
    – K09
    Mar 27 '15 at 10:09
  • Well they may never finish. The key is to get in there before they do. There is also the possibility that you are taking the session from a different query window. Are you running this script from the master DB? Mar 27 '15 at 10:20
  • Was not running it from master. Should I?
    – K09
    Mar 27 '15 at 11:24
  • 1
    I would close SSMS (to make sure all of your sessions are closed) and then open SSMS and do not select the database you want to alter. Select Master or add USE master; to the start of the script. This will avoid you accidentally taking the only session. Mar 27 '15 at 12:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.