When you need to perform maintenance tasks, you as a DB Admin may want to obtain exclusive access to the database. To do so, you can set the database to Single User Mode, which permits only one database connection at a time. By doing so, if other users try and access the database while you are working on that active connection, they will receive an error.
To bring a database to the single user mode, use the following query
ALTER DATABASE YOURDBNAME SET SINGLE_USER
Now if users are already connected to the db when you run this command, they will not be disconnected. Instead the 'SET SINGLE_USER' command will wait till the others have disconnected. If you want to override this scenario and forcefully disconnect other users, then use the following query
ALTER DATABASE YOURDBNAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Once you are through with the maintenance tasks, to return the database back to the multi-user mode, use this query
ALTER DATABASE YOURDBNAME SET MULTI_USER
I am Trying to Restore as follows with a job (this database is in my backup server, the backup by itself works perfect, but during the job, it always goes to the error)
ReplyDeleteSQL SERVER 2000
Job1(all steps gotonext on success and only last one quits on failure)
Step1:
ALTER DATABASE SAR
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
Step2:
RESTORE DATABASE [SAR]
FROM DISK = N'E:\MSSQL\BACKUP\BCKP_SAR_RELEVO.BAK'
WITH FILE = 1,
NOUNLOAD ,
STATS = 5,
RECOVERY ,
REPLACE ,
MOVE N'SAR_NEW_Data' TO N'E:\MSSQL\DATA\SAR.mdf',
MOVE N'SAR_NEW_AUDITORIA' TO N'E:\MSSQL\DATA\SAR_1.mdf',
MOVE N'SAR_NEW_LOG' TO N'E:\MSSQL\DATA\SAR_log.ldf'
STEP3:
ALTER DATABASE SAR
SET MULTI_USER
Step4:
USE SAR
go
SELECT COUNT(*) AS 'Total SAR_Rec' from SAR_Rec
THATS IT!
Error:
Job 'REST_SAR_RELEVO' : Step 2, 'REST_SAR_RELEVO' : Began Executing 2009-02-24 19:39:00
Msg 3101, Sev 16: Exclusive access could not be obtained because the database is in use. [SQLSTATE 42000]
Msg 3013, Sev 16: RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000]