@SETLOCAL

@SET instanceName=%1
@SET databaseName=%2
@SET backupFileName=%~f3

@SET RETURN_ERRORLEVEL=0

sqlcmd -E -S "%instanceName%" -d "master" -b -Q "IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'%databaseName%') BEGIN ALTER DATABASE %databaseName% SET OFFLINE WITH ROLLBACK AFTER 5 END"
@IF ERRORLEVEL 1 GOTO Error

@SET backupSetColumns=BackupName NVARCHAR(128),BackupDescription NVARCHAR(255),BackupType smallint,ExpirationDate datetime,Compressed tinyint,Position smallint,DeviceType tinyint,UserName NVARCHAR(128),ServerName NVARCHAR(128),DatabaseName NVARCHAR(128),DatabaseVersion INT,DatabaseCreationDate datetime,BackupSize numeric(20,0),FirstLSN numeric(25,0),LastLSN numeric(25,0),CheckpointLSN numeric(25,0),DatabaseBackupLSN numeric(25,0),BackupStartDate datetime,BackupFinishDate datetime,SortOrder smallint,CodePage smallint,UnicodeLocaleId INT,UnicodeComparisonStyle INT,CompatibilityLevel tinyint,SoftwareVendorId INT,SoftwareVersionMajor INT,SoftwareVersionMinor INT,SoftwareVersionBuild INT,MachineName NVARCHAR(128),Flags INT,BindingID uniqueidentifier,RecoveryForkID uniqueidentifier,Collation NVARCHAR(128),FamilyGUID uniqueidentifier,HasBulkLoggedData bit,IsSnapshot bit,IsReadOnly bit,IsSingleUser bit,HasBackupChecksums bit,IsDamaged bit,BeginsLogChain bit,HasIncompleteMetaData bit,IsForceOffline bit,IsCopyOnly bit,FirstRecoveryForkID uniqueidentifier,ForkPointLSN numeric(25,0),RecoveryModel NVARCHAR(128),DifferentialBaseLSN numeric(25,0),DifferentialBaseGUID uniqueidentifier,BackupTypeDescription NVARCHAR(128),BackupSetGUID uniqueidentifier
@SET backupInformationColumns=LogicalName varchar(128),PhysicalName varchar(260),Type varchar(1),FileGroupName varchar(128) ,Size numeric(20,0) ,MaxSize numeric(20,0),FileId bigint,CreateLSN numeric(25,0),DropLSN numeric(25,0),UniqueId uniqueidentifier,ReadOnlyLSN numeric(25,0),ReadWriteLSN numeric(25,0),BackupSizeInBytes bigint,SourceBlockSize int,FileGroupId int,LogGroupGUID uniqueidentifier,DifferentialBaseLSN numeric(25,0),DifferentialBaseGUID uniqueidentifier,IsReadOnly bit, IsPresent bit,TDEThumbprint varbinary(32) 


@ECHO Attempting restore with SQL2008/SQL2008R2 backup schema.
@CALL :RestoreDB "%backupSetColumns%,CompressedBackupSize BigInt" "%backupInformationColumns%"
@IF NOT ERRORLEVEL 1 GOTO EnableDB

:RestoreFailed
@REM IF THE RESTORE FAILS THEN THIS IS THE MOST IMPORTANT ERROR CODE TO RETURN.
@SET RETURN_ERRORLEVEL=%ERRORLEVEL%
@ECHO Failed to restore

:EnableDB
sqlcmd -E -S "%instanceName%" -d "master" -b -Q "ALTER DATABASE %databaseName% SET ONLINE"
@IF ERRORLEVEL 1 GOTO Error

:RestoreMapping
sqlcmd -E -S "%instanceName%" -d "%databaseName%" -b -i ResetUserMappings.sql
@IF ERRORLEVEL 1 (
  ECHO Failed to reset user mappings. Please check Sophos DB Admins user-mappings after the restore. Continuing.
  CALL :ResetErrorLevel
)

:End
@REM ITS IMPORTANT TO RETURN %RETURN_ERRORLEVEL% ON THE SAME LINE AS ENDLOCAL, AS ENDLOCAL WILL RESET ALL ENVIRONMENT VARIABLES ON THE NEXT STATEMENT.
@ENDLOCAL && EXIT /B %RETURN_ERRORLEVEL%

:Error
@ECHO.
@ECHO Failed
@ECHO.
@IF %RETURN_ERRORLEVEL% EQU 0 SET RETURN_ERRORLEVEL=%ERRORLEVEL%
@GOTO End

:RestoreDB
sqlcmd -E -S "%instanceName%" -d "master" -b -Q "CREATE TABLE #backupSetInfo (%~1); CREATE TABLE #backupInformation (%~2); INSERT #backupSetInfo EXEC('RESTORE HEADERONLY FROM DISK=N' + '''%backupFileName%'''); INSERT #backupInformation EXEC('RESTORE FILELISTONLY FROM DISK=N' + '''%backupFileName%'''); DECLARE @logicalNameD varchar(255); DECLARE @logicalNameL varchar(255); DECLARE @backupsetnumber smallint; SET @backupsetnumber = (SELECT TOP(1) Position FROM #backupSetInfo ORDER BY BackupFinishDate DESC); select top 1 @logicalNameD = LogicalName from #backupInformation where Type = 'D'; select top 1 @logicalNameL = LogicalName from #backupInformation where Type = 'L'; RESTORE DATABASE %databaseName% FROM DISK = '%backupFileName%' WITH FILE = @backupsetnumber, REPLACE, RECOVERY, MOVE @logicalNameD TO 'E:\MSSQL\MSSQL10_50.SOPHOS\MSSQL\Data\%DatabaseName%.MDF',MOVE @logicalNameL TO 'E:\MSSQL\MSSQL10_50.SOPHOS\MSSQL\Data\%DatabaseName%_log.LDF'; DROP TABLE #backupSetInfo; DROP TABLE #backupInformation;"
@EXIT /B %ERRORLEVEL%

:ResetErrorLevel
@EXIT /B 0