0

I am using Paul Brewer's script for AutoRestore sp_RestoreGene on SQL Server 2008R2 box.

I created the stored procedure dbo.sp_RestoreGene on the Server 1 where the backup file is located.

I called the store procedure:

USE [DBA_]
DECLARE @RC int
EXECUTE @RC = [dbo].[sp_RestoreGene]
@Database = 'Production'
,@TargetDatabase = 'Test'
,@WithRecovery = 1
,@WithCHECKDB = 1
,@WithReplace = 1
,@FromFileFullUNC = '\\Server1\Restore_backup\Backup\FullBackupForTest\'
,@Log_Reference = 'Incident xyz' 

GO

It generated the following Restore script. I created a job which runs the script below in scheduled time on Server 2

;DECLARE @msg_Production VARCHAR(1000)
;RESTORE DATABASE [Test] FROM DISK = N'\\Server1\Restore_backup\Backup\FullBackupForTest\Production.bak'  WITH REPLACE, FILE = 2, NORECOVERY, STATS=10, MOVE N'Production' TO 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Test.mdf', MOVE N'Production_log' TO 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Test_log.ldf'
;SET @msg_Production = 'Incident xyz';  RAISERROR (@msg_Production,0,0) WITH LOG;RESTORE DATABASE [Test] WITH RECOVERY
;SET @msg_Production = 'Incident xyz';  RAISERROR (@msg_Production,0,0) WITH LOG;DBCC CHECKDB('Test') WITH NO_INFOMSGS, ALL_ERRORMSGS

I got an error:

Msg 3101, Level 16, State 1, Line 2 Exclusive access could not be obtained because the database is in use. Msg 3013, Level 16, State 1, Line 2 RESTORE DATABASE is terminating abnormally.

To get the exclusive connection when I restore manually I use the script below . It works perfect.

use master
Go
ALTER DATABASE Test SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
waitfor delay '00:00:05' -- 5 sec delay .. no other spid takes our space !
GO

Restore script

Go
ALTER DATABASE Test SET MULTI_USER 
go 

Can I include in the restore script the alter single user and alter set multi_user script? Or is there a better way to get exclusive connection for autorestore?

How to get exclusive connection for Auto restore ?

0
2

To get exclusive database access (provided it is not used in replicaiton or logshipping or any other HA/DR), OFFLINE it.

ALTER DATABASE DB_name 
SET OFFLINE WITH ROLLBACK IMMEDIATE

Make this as a first step and then run the restore script that you generated.

BTW, its Paul Brewer's restore gene script that you are using.

2
  • thank you I corrected paul full name. It is not used in replication or logshipping . It works perfect Nov 16 '15 at 16:25
  • @SQL_DBA_Bini Glad that it helped !
    – Kin Shah
    Nov 16 '15 at 16:29

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.