SQL Server – Restore Database with SET SINGLE_USER

This Is a Striped down version of the script I use to restore a great many databases at the same time. Hopefully I took out all of the parts that make it work for my scenario and left them with appropriate hints as to what to place in your version.

    -- Remove old DBs:
use [master]
go

DROP Database [YourDBName]
DROP Database [YourNextDBName]
DROP Database [AndSoOn]
DROP Database [AndSoForth]



GO

-- Create Restore List

Insert into [dbo].[BackupList] (DBName) Values ('YourDBName')
Insert into [dbo].[BackupList] (DBName) Values ('YourNextDBName')
Insert into [dbo].[BackupList] (DBName) Values ('AndSoOn')
Insert into [dbo].[BackupList] (DBName) Values ('AndSoForth')


GO

declare @counter [int]
declare @maxcounter [int]
declare @DBName [varchar](50)
declare @SQL1 [varchar](4000)
declare @SQL2 [varchar](4000)
declare @SQL3 [varchar](4000)
declare @SQL4 [varchar](4000)
set @counter = 0
select @maxcounter = max(id) from [dbo].[BackupList]


while @counter <= @maxcounter
BEGIN
                select @DBname = DBname from  .[dbo].[BackupList] where ID = @Counter

                IF @DBname LIKE '%WhateverYoursIsLike' 
                BEGIN
                SET @SQL1 = 'RESTORE DATABASE '+ QUOTENAME(@DBName) + ' FROM  DISK = ''YourBack-UpLocation' + @DBName + '.bak'' WITH  FILE = 1,  
                MOVE N''YourFileName'' TO ''YourRestoreLocation' + @DBName + '.mdf'', 
                MOVE N''YourFileName_log'' TO ''YourRestoreLocation' + @DBName + '.ldf'''
                exec(@sql1)
                print(@sql1)
                END



                set @counter = @counter + 1
END

GO
Use [em_support]
GO
truncate table [em_support].[dbo].[BackupList]
GO
DBCC CHECKIDENT (BackupList, RESEED, 0)
GO


--> Modify DB properties
DECLARE @DBName SYSNAME
DECLARE @Login SYSNAME
DECLARE @SQL NVARCHAR(4000)
DECLARE curModifyProperties CURSOR FOR
                SELECT [name]
                FROM sys.databases
                WHERE [name] LIKE '%WhateverYoursIsLike'

OPEN curModifyProperties
FETCH NEXT FROM curModifyProperties
INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN

                --> BEG - Set trustworthy property
                SET @SQL = 'ALTER DATABASE '+QUOTENAME(@DBName)+' SET TRUSTWORTHY ON'
                EXEC (@SQL)
                --> END - Set trustworthy property

                --> BEG - Set database owner
                SET @SQL = QUOTENAME(@DBName)+'.[dbo].[sp_changedbowner] ''sa'''
                EXEC (@SQL)
                --> END - Set database owner

FETCH NEXT FROM curModifyProperties
INTO @DBName
END
CLOSE curModifyProperties
DEALLOCATE curModifyProperties 
GO

I hope that helps or at least points you in the right direction.

Running this on a sufficiently corrupted database results in the following error message:

Msg 7909, Level 20, State 1, Line 1

The emergency-mode repair failed.You must restore from backup.

This leads me to estimate that REPAIR is simply the short form of REPAIR_ALLOW_DATA_LOSS. In the very least, I would treat it as the same when it comes to expectations around recoverability of the database in question. In other words, this should be considered the operation of last resort, only to be tried when all else fails. Make sure you make a physical copy of the database before running this process.