SQL Server Performance Forum – Threads Archive

RESTORE DB& exclusive access

I have a nightly SQL server batch job on server that copies the production RCTS database to the RCTSRpt database instance by using the backup and restore process. It has three steps
1)backup database RCTS to backup file
2)restore it to RCTSRpt databae
3)delete the backup file ; Also it is scheduled to run morning 4.30
But recently some users are online that time, and my job fails withfollowing error : Executed as user: NT AUTHORITYSYSTEM. Exclusive access could not be obtained because the database is in use. [SQLSTATE 42000] (Error 3101) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
Is there any solution to this ????

1)BACKUP DATABASE [rcts] TO [rcts_reporting] WITH INIT , NOUNLOAD , RETAINDAYS = 1, NAME = N’rcts_reporting’, NOSKIP , STATS = 10, NOFORMAT 2)RESTORE DATABASE RCTSRpt
FROM rcts_reporting
with move ‘rcts_Data’ to ‘f:MSSQLdataRCTSRpt_data.mdf’,
move ‘rcts_Log’ to ‘f:MSSQLdataRCTSRpt_log.ldf’ 3)exec master..xp_cmdshell ‘del F:MSSQLBACKUP
cts_reporting.BAK’

use master
Alter Database YourDbRpt
SET SINGLE_USER With ROLLBACK IMMEDIATE This will put kick anyone out that is in there and rollback their transaction immediately.
I restore to a file on disk, so Backup Database YourDb To Disk = "\YourNetWorkShareMyDatabaseFolderYourDb.BAK"
With init, Name = "Some Awsome Backup" (You don’t need that other stuff) Restore Database YourDbRpt From Disk = "\YourNetWorkShareMyDatabaseFolderYourDb.BAK" with (move if necessary), Replace,
File=1, Recovery Alter Database YourDbRpt
SET MULTI_USER With ROLLBACK IMMEDIATE I hope this helps, Matt

Have you removed BUILTINAdministrators group from SQL? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>