How to get an exclusive connection in AutoRestore job ( sp_RestoreGene)? 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 ?
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.