USE [master]
GO
DECLARE@dbnameVARCHAR(128)
,@TGTDBNameSYSNAME = ''--TestDB'
,@BackupPathandFile NVARCHAR(2048) =
--'\\server\Backup\folder\FULL\dbname_FULL_20220214_001157.bak'
'F:\Data\MSSQL14.CORP\MSSQL\Backup\agtest_full_20220215.bak'
,@DataFilePathVARCHAR(256) = ''
--@DataFilepath VARCHAR(256)='C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA'
,@LogFilePathVARCHAR(256) = ''
--@LogFilePath VARCHAR(256)='C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA'
,@MediaVARCHAR(5) = 'disk'
,@DebugINT = 1
,@recoverCHAR(1) = 'y'
,@replaceINT = 1
,@RestoreCommandNVARCHAR(MAX)
,@SQLCommandNVARCHAR(MAX)
SET NOCOUNT ON
SET CONCAT_NULL_YIELDS_NULL OFF
--Does the backup path and file name exist?
--If not raise an error and exit the proc
DECLARE @result INT
IF UPPER(@Media) = 'DISK'
BEGIN
EXEC master.sys.xp_fileexist @BackupPathandFile, @result OUTPUT
IF @result = 0
BEGIN
PRINT '--The file "' + @BackupPathandFile + '" does not exist, check path and filename!' + CHAR(10) + '--Ensure you have not specified a URL backup for a disk based path.' + CHAR(10)
RETURN
END
ELSE PRINT '--The file "' + @backuppathandfile + '" is present' + CHAR(10)
END
--Check the media type, if invalid return
IF UPPER(@Media) <> 'DISK'
BEGIN
PRINT 'Media type invalid, check the media type specified and re run the procedure'
RETURN
END
--Are the database file paths null or empty string?
--If they are get the default locations from the instance
DECLARE @DefaultData VARCHAR(256),@DefaultLog VARCHAR(256),@instancename VARCHAR(64),@instance VARCHAR(128),@prodmajver VARCHAR(4)
SELECT @prodmajver = CAST(SERVERPROPERTY('ProductMajorVersion') AS VARCHAR(4))
SELECT @Instance= 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL' + @prodmajver + '.' +
CASE
WHEN SERVERPROPERTY('InstanceName') IS NULL THEN 'MSSQLSERVER'
ELSE CAST(SERVERPROPERTY('InstanceName') AS VARCHAR(64))
END
+ '\MSSQLServer'
IF @DataFilepath = '' or @DataFilepath IS NULL
BEGIN
--EXEC [master].dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @DefaultData OUTPUT
EXEC [master].dbo.xp_regread N'HKEY_LOCAL_MACHINE', @instance, N'DefaultData', @DefaultData OUTPUT
IF (@DefaultData IS NULL)
BEGIN
PRINT 'Check the DefaultData registry key is set in the SQL Server instance hive at:' + CHAR(10) + 'HKEY_LOCAL_MACHINE\' + @instance + CHAR(10) +
'and retry, or supply a valid datafile path to the procedure'
RETURN
END
ELSE SELECT @DataFilePath = @DefaultData
END
IF @LogFilePath = '' OR @LogFilePath IS NULL
BEGIN
--EXEC [master].dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @DefaultLog OUTPUT
EXEC [master].dbo.xp_regread N'HKEY_LOCAL_MACHINE', @instance, N'DefaultLog', @DefaultLog OUTPUT
IF (@DefaultLog IS NULL)
BEGIN
PRINT 'Check the DefaultLog registry key is set in the SQL Server instance hive at:' + CHAR(10) + 'HKEY_LOCAL_MACHINE\' + @instance + CHAR(10) +
'and retry, or supply a valid logfile path to the procedure'
RETURN
END
ELSE SELECT @LogFilePath = @DefaultLog
END
--Create a temporary table to store the results from the RESTORE FILELISTONLY command
IF OBJECT_ID('tempdb..#filelist', 'U') IS NOT NULL
BEGIN
DROP TABLE #filelist
END
CREATE TABLE #filelist (
[LogicalName]NVARCHAR(128)
,[PhysicalName]NVARCHAR(260)
,[Type]CHAR(1)
,[FileGroupName]NVARCHAR(128)
,[Size]NUMERIC(20,0)
,[MaxSize]NUMERIC(20,0)
,[FileID]INT
,[CreateLSN]NUMERIC(25,0)
,[DropLSN]NUMERIC(25,0) NULL
,[UniqueID]UNIQUEIDENTIFIER
,[ReadOnlyLSN]NUMERIC(25,0) NULL
,[ReadWriteLSN]NUMERIC(25,0) NULL
,[BackupSizeInBytes]BIGINT
,[SourceBlockSize]INT
,[FileGroupID]INT
,[LogGroupGUID]UNIQUEIDENTIFIER NULL
,[DifferentialBaseLSN]NUMERIC(25,0) NULL
,[DifferentialBaseGUID]UNIQUEIDENTIFIER
,[IsReadOnly]BIT
,[IsPresent]BIT
,[TDEThumbprint]VARBINARY(32)
,[SnapshotURL]VARCHAR(1024)
)
--Create a temporary table to store the results from the RESTORE HEADERONLY command
IF OBJECT_ID('tempdb..#filehead', 'U') IS NOT NULL
BEGIN
DROP TABLE #filehead
END
CREATE TABLE #filehead(
BackupNameVARCHAR(128)
,BackupDescriptionVARCHAR(255)
,BackupTypeSMALLINT
,ExpirationDateDATETIME
,CompressedBIT
,PositionSMALLINT
,DeviceTypeTINYINT
,UserNameVARCHAR(128)
,ServerNameVARCHAR(128)
,DatabaseNameVARCHAR(128)
,DatabaseVersionINT
,DatabaseCreationDateDATETIME
,BackupSizeNUMERIC(20,0)
,FirstLSNNUMERIC(25,0)
,LastLSNNUMERIC(25,0)
,CheckpointLSNNUMERIC(25,0)
,DatabaseBackupLSNNUMERIC(25,0)
,BackupStartDateDATETIME
,BackupFinishDateDATETIME
,SortOrderSMALLINT
,[CodePage]SMALLINT
,UnicodeLocaleIdINT
,UnicodeComparisonStyleINT
,CompatibilityLevelTINYINT
,SoftwareVendorIdINT
,SoftwareVersionMajorINT
,SoftwareVersionMinorINT
,SoftwareVersionBuildINT
,MachineNameVARCHAR(128)
,FlagsINT
,BindingIDUNIQUEIDENTIFIER
,RecoveryForkIDUNIQUEIDENTIFIER
,CollationVARCHAR(128)
,FamilyGUIDUNIQUEIDENTIFIER
,HasBulkLoggedDataBIT
,IsSnapshotBIT
,IsReadOnlyBIT
,IsSingleUserBIT
,HasBackupChecksumsBIT
,IsDamagedBIT
,BeginsLogChainBIT
,HasIncompleteMetaDataBIT
,IsForceOfflineBIT
,IsCopyOnlyBIT
,FirstRecoveryForkIDUNIQUEIDENTIFIER
,ForkPointLSNNUMERIC(25,0)
,RecoveryModelVARCHAR(60)
,DifferentialBaseLSNNUMERIC(25,0)
,DifferentialBaseGUIDUNIQUEIDENTIFIER
,BackupTypeDescriptionVARCHAR(60)
,BackupSetGUIDUNIQUEIDENTIFIER
,CompressedBackupSizeBIGINT
,containmentTINYINT
,KeyAlgorithmVARCHAR(32)
,EncryptorThumbprintVARBINARY(20)
,EncryptorTypeVARCHAR(32)
)
SET @RestoreCommand = 'RESTORE HEADERONLY FROM ' + UPPER(@Media) + '=''' + @BackupPathandFile + ''''
BEGIN TRY
INSERT INTO #filehead
EXEC (@RestoreCommand)
IF @Debug = 1
BEGIN
PRINT '--DEBUG MSG: The header restore command used is;' + CHAR(13) + @RestoreCommand + CHAR(10)
END
END TRY
BEGIN CATCH
PRINT 'Error restoring filelist, check the media type matches the backup file path\name specified'
END CATCH
SELECT @dbname = databasename FROM #filehead
IF @Debug = 1
PRINT '--The database name from the backup file is [' + @dbname + ']' + CHAR(13)
/* fetch list of database files from source backup file */
SET @RestoreCommand = ''
SET @RestoreCommand = 'RESTORE FILELISTONLY FROM ' + UPPER(@Media) + '=''' + @BackupPathandFile + ''''
BEGIN TRY
INSERT INTO #filelist
EXEC (@RestoreCommand)
IF @Debug = 1
BEGIN
PRINT '--DEBUG MSG: The filelist restore command used is;' + CHAR(13) + @RestoreCommand + CHAR(10)
END
END TRY
BEGIN CATCH
PRINT 'Error restoring filelist, check the media type matches the backup file path\name specified'
END CATCH
IF (SELECT [type] FROM #filelist WHERE type = 'S') IS NOT NULL
BEGIN
IF (SELECT [value] FROM sys.configurations WHERE [name] = N'filestream access level') = 0
BEGIN
PRINT '--Filestream db detected, check filestream is enabled'
IF @Debug = 1
BEGIN
PRINT '--DEBUG MSG: Filestream is not enabled, enable FileStream and retry' + CHAR(10)
RETURN
END
END
END
--Restoring to same db or to new name?
--If the target name is null or empty string get the dbname from the backupfile header
IF @TGTDBName IS NULL OR @TGTDBName = ''
BEGIN
IF @Debug = 1
PRINT '--DEBUG MSG: Target name not supplied, using name ' + QUOTENAME(@dbname) + ' from the backupset' + CHAR(10) + CHAR(13)
SELECT @TGTDBName = @dbname
END
ELSE
BEGIN
IF @Debug = 1
PRINT '--DEBUG MSG: Target name supplied, using name ' + QUOTENAME(@TGTDBName) + CHAR(10) --+ CHAR(13)
END
--If the existing database is online, take it offline first to kill connections
IF DATABASEPROPERTYEX(@TGTDBName,'Status') = 'ONLINE'
BEGIN
SET @SQLCommand = 'ALTER DATABASE [' + @TGTDBName + '] SET OFFLINE WITH ROLLBACK IMMEDIATE' + CHAR(10) + CHAR(13)
IF @Debug = 0
BEGIN
EXEC (@SQLCommand)
END
ELSE PRINT '--DEBUG MSG: ' + @SQLCommand
END
/* Construct the restore command */
SELECT @RestoreCommand = ''
SET @RestoreCommand = 'RESTORE DATABASE [' + @TGTDBName + '] FROM ' + UPPER(@Media) + '=N''' + @BackupPathandFile + ''' WITH ' + CHAR(10)
--Data and Log files
SELECT @RestoreCommand += 'MOVE N''' + LogicalName +
CASE [type]
WHEN 'D' THEN ''' TO N''' + @DataFilePath + '\'
WHEN 'L' THEN ''' TO N''' + @LogFilePath + '\'
END +
REPLACE(REVERSE(SUBSTRING(REVERSE(PhysicalName),0, CHARINDEX('\', REVERSE(PhysicalName), 0))), @dbname, @TGTDBName)
+ ''',' + CHAR(10)
FROM #filelist
WHERE type <> 'S'
ORDER BY [Type],[FileID]
--Get the FileStream paths too if there are any
SELECT @RestoreCommand += 'MOVE N''' + LogicalName +
CASE type
WHEN 'S' THEN ''' TO N''' + @DataFilePath + '\'
END +
REPLACE(REVERSE(SUBSTRING(REVERSE(PhysicalName),0, CHARINDEX('\', REVERSE(PhysicalName), 0))), @dbname, @TGTDBName)
+ ''',' + CHAR(10)
FROM #filelist
WHERE type = 'S'
SELECT @RestoreCommand +=
CASE @replace
WHEN 0 THEN 'STATS=10'
ELSE 'REPLACE,STATS=10'
END +
CASE UPPER(@recover)
WHEN 'Y' THEN ''
WHEN 'N' THEN ',norecovery'
END
/* Perform the restore */
PRINT '--Restore command is: ' + CHAR(10) + @RestoreCommand
IF @Debug = 0
BEGIN TRY
EXEC (@RestoreCommand)
END TRY
BEGIN CATCH
PRINT 'Restore failed, check the SQL Server error log for more detail'
END CATCH
/* Perform the restore */
PRINT '--Restore command is: ' + CHAR(10) + @RestoreCommand
IF @Debug = 0
BEGIN TRY
EXEC (@RestoreCommand)
END TRY
BEGIN CATCH
PRINT 'Restore failed, check the SQL Server error log for more detail'
END CATCH
/* Tidy up */
DROP TABLE #filelist
DROP TABLE #filehead
GO