USE [master]
GO
IF OBJECT_ID('dbo.sp_Restore') IS NULL
EXEC('CREATE PROCEDURE dbo.sp_Restore AS SELECT 1 AS ID')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE dbo.sp_Restore
@BackupPath VARCHAR(256) = NULL,
@DatabaseName VARCHAR(256) = NULL,
@ListBackupHeaderOnly BIT = 0,
@RestorePosition SMALLINT = NULL,
@RestoreLastPosition BIT = 0,
@FilePostFix VARCHAR(128) = NULL,
@LiteSpeed BIT = 1,
@DropExisting BIT = 1,
@DeductExistingDBFileSpace BIT = 1,
@KeepCDC BIT = 0,
@PrintSQL BIT = 1,
@ExecSQL BIT = 0,
@SetDbOwner VARCHAR(128) = 'sa',
@SetReadOnly BIT = 0,
@SQL VARCHAR(MAX) = NULL OUTPUT
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @MaxPosition SMALLINT,
@MinPosition SMALLINT,
@PositionCount INT,
@RestorePositionExists BIT
SET @FilePostFix = ISNULL(@FilePostFix, '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19), GETDATE(), 121), ' ', '_'), ':', ''), '-', ''))
IF (@BackupPath IS NULL
OR (@DatabaseName IS NULL
AND @ListBackupHeaderOnly = 0))
BEGIN
EXEC master.dbo.sp_ExecTemplate
@ObjectName = 'sp_Restore',
@PrintDefault = 0,
@DeclareReturn = 0,
@PrintReturnLine = 0,
@DoNotDeclareVar = 1
RETURN
END
IF NOT EXISTS ( SELECT *
FROM Util.FS.GetFileInfo(@BackupPath) )
BEGIN
RAISERROR ('File %s does not exists!', 16, 1, @BackupPath)
RETURN
END
--#region Backup Header Definitions
DECLARE @MSBACKUP TABLE (BackupName VARCHAR(256),
BackupDescription VARCHAR(256),
BackupType SMALLINT,
ExpirationDate DATETIME,
Compressed TINYINT,
[POSITION] SMALLINT,
DeviceType TINYINT,
UserName VARCHAR(128),
[SERVERNAME] VARCHAR(128),
DatabaseName VARCHAR(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 VARCHAR(128),
Flags INT,
BindingID UNIQUEIDENTIFIER,
RecoveryForkID UNIQUEIDENTIFIER,
[COLLATION] VARCHAR(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 VARCHAR(60),
DifferentialBaseLSN NUMERIC(25, 0),
DifferentialBaseGUID UNIQUEIDENTIFIER,
BackupTypeDescription VARCHAR(60),
BackupSetGUID UNIQUEIDENTIFIER,
CompressedBackupSize BIGINT)
DECLARE @LITESPEEDBACKUP TABLE (FileNumber INT,
BackupFormat VARCHAR(128),
Guid UNIQUEIDENTIFIER,
BackupName VARCHAR(256),
BackupDescription VARCHAR(256),
BackupType VARCHAR(128),
ExpirationDate DATETIME,
Compressed TINYINT,
[POSITION] SMALLINT,
DeviceType TINYINT,
UserName VARCHAR(128),
[SERVERNAME] VARCHAR(128),
DatabaseName VARCHAR(128),
DatabaseVersion INT,
DatabaseCreationDate DATETIME,
BackupSize NUMERIC(20, 0),
FirstLsn NUMERIC(25, 0),
LastLsn NUMERIC(25, 0),
CheckpointLsn NUMERIC(25, 0),
DifferentialBaseLsn NUMERIC(25, 0),
BackupStartDate DATETIME,
BackupFinishDate DATETIME,
SortOrder SMALLINT,
[Codepage] SMALLINT,
CompatibilityLevel TINYINT,
SoftwareVendorId INT,
SoftwareVersionMajor INT,
SoftwareVersionMinor INT,
SoftwareVersionBuild INT,
MachineName VARCHAR(128),
BindingId UNIQUEIDENTIFIER,
RecoveryForkId UNIQUEIDENTIFIER,
ENCRYPTION INT,
IsCopyOnly VARCHAR(128))
--#endregion
--#region Populate Backup Header
IF @LiteSpeed = 1
BEGIN
BEGIN TRY
INSERT @LITESPEEDBACKUP
EXEC master.dbo.xp_restore_headeronly
@filename = @BackupPath
END TRY
BEGIN CATCH
INSERT @MSBACKUP
EXEC ('RESTORE HEADERONLY FROM DISK = ''' + @BackupPath + '''')
SET @LiteSpeed = 0
END CATCH
END
ELSE
IF @LiteSpeed = 0
BEGIN
BEGIN TRY
INSERT @MSBACKUP
EXEC ('RESTORE HEADERONLY FROM DISK = ''' + @BackupPath + '''')
END TRY
BEGIN CATCH
INSERT @LITESPEEDBACKUP
EXEC master.dbo.xp_restore_headeronly
@filename = @BackupPath
SET @LiteSpeed = 1
END CATCH
END
--#endregion
IF @ListBackupHeaderOnly = 1
BEGIN
IF @LiteSpeed = 1
SELECT 1 AS LiteSpeed,
*
FROM @LITESPEEDBACKUP
ELSE
SELECT 0 AS LiteSpeed,
*
FROM @MSBACKUP
RETURN
END
SELECT @MaxPosition = MAX([POSITION]),
@MinPosition = MIN([POSITION]),
@PositionCount = COUNT(*),
@RestorePositionExists = MAX(CASE WHEN @RestorePosition = [POSITION] THEN 1
ELSE 0
END)
FROM (SELECT [POSITION]
FROM @LITESPEEDBACKUP
UNION ALL
SELECT [POSITION]
FROM @MSBACKUP) k
IF @PositionCount = 1
OR @RestoreLastPosition = 1
SET @RestorePosition = @MaxPosition
ELSE
IF @RestorePosition IS NOT NULL
AND @RestorePositionExists = 1
BEGIN
SET @RestorePositionExists = 1 -- DUMMY
END
ELSE
BEGIN
IF @LiteSpeed = 1
SELECT 1 AS LiteSpeed,
*
FROM @LITESPEEDBACKUP
ELSE
SELECT 0 AS LiteSpeed,
*
FROM @MSBACKUP
RAISERROR('Restore Position %d does not exists', 16, 1, @RestorePosition)
RETURN 40
END
IF OBJECT_ID('tempdb..#RestoreFileList') IS NOT NULL
DROP TABLE #RestoreFileList
CREATE TABLE #RestoreFileList (LogicalName VARCHAR(128),
PhysicalName VARCHAR(128),
[TYPE] CHAR(1),
FileGroupName VARCHAR(128),
SizeGB NUMERIC(20, 4),
BackupSizeGB NUMERIC(20, 4),
MaxSizeGB NUMERIC(20, 4),
FileGroupId INT,
FileId INT,
DifferentialBaseLSN NUMERIC(25, 0) NULL,
Litespeed BIT,
Volume VARCHAR(1000))
EXEC dbo.sp_BackupFileList
@BackupPath = @BackupPath,
@Position = @RestorePosition,
@LiteSpeed = @LiteSpeed OUTPUT,
@DoNotCheckFileExists = 1
IF OBJECT_ID('tempdb..#Volumes') IS NOT NULL
DROP TABLE #Volumes
SELECT Volume,
FreeGB,
CASE WHEN Volume LIKE '%log%' THEN 'L'
ELSE 'D'
END AS [TYPE]
INTO #Volumes
FROM Util.fs.Volumes
WHERE Available = 1
IF @DeductExistingDBFileSpace = 1
UPDATE v
SET FreeGB = FreeGB + v2.SizeGb
FROM #Volumes v
INNER JOIN (SELECT v.Volume,
SUM(m.SIZE * 8.0 / 1024 / 1024) AS SizeGb
FROM sys.master_files m (NOLOCK)
CROSS APPLY (SELECT TOP 1
Volume
FROM #Volumes
WHERE SUBSTRING(physical_name, 1, LEN(volume)) = Volume
ORDER BY LEN(Volume) DESC) v
WHERE database_id = DB_ID(@DatabaseName)
GROUP BY v.Volume) v2 ON v2.Volume = v.Volume
WHILE 1 = 1
BEGIN
;
WITH Volumes
AS (SELECT Volume,
FreeGB - RestoredGB AS FreeGB,
RestoredGB,
[TYPE]
FROM #Volumes t
CROSS APPLY (SELECT ISNULL ((SELECT SUM (sizeGB) FROM #RestoreFileList f WHERE f.Volume = t.Volume), 0) AS RestoredGB) b),
Files
AS (SELECT LogicalName,
[TYPE],
SizeGB,
Volume
FROM (SELECT LogicalName,
NewType AS TYPE,
SizeGB,
Volume,
ROW_NUMBER() OVER (PARTITION BY NewType ORDER BY SizeGB DESC) AS ROW
FROM #RestoreFileList
CROSS APPLY (SELECT CASE WHEN Type = 'S' THEN 'D' ELSE Type END AS NewType) nt
WHERE Volume IS NULL) k
WHERE ROW = 1)
UPDATE f
SET Volume = b.Volume
FROM files f
CROSS APPLY (SELECT TOP 1
*
FROM Volumes a
WHERE a.[Type] = f.[Type]
AND a.FreeGB >= f.SizeGB
ORDER BY a.FreeGB DESC) b
IF @@ROWCOUNT = 0
BREAK
END
IF EXISTS ( SELECT *
FROM #RestoreFileList
WHERE Volume IS NULL )
BEGIN
SELECT Volume,
FreeGB AS InitialFreeGB,
FreeGB - RestoredGB AS FreeGB,
RestoredGB,
[TYPE]
FROM #Volumes t
CROSS APPLY (SELECT ISNULL ((SELECT SUM (sizeGB) FROM #RestoreFileList f WHERE f.Volume = t.Volume), 0) AS RestoredGB) b
ORDER BY [TYPE] ASC,
3 DESC
SELECT *
FROM #RestoreFileList
ORDER BY [TYPE] ASC,
SizeGB DESC
RAISERROR ('There is not enough space to restore all the files',16,1)
RETURN 60
END
IF @LiteSpeed = 1
SELECT @SQL = 'USE master
' + CASE WHEN @DropExisting = 1 THEN 'EXEC sp_DBDrop
@DBName = ''' + @DatabaseName + ''',
@PrintSQL = 1,
@ExecuteSQL = 1
' ELSE ''
END + 'EXEC master.dbo.xp_restore_database @database = N''' + @DatabaseName + ''' ,
@filename = N''' + @BackupPath + ''',
@filenumber = ' + CAST(@RestorePosition AS VARCHAR) + ',
@jobp = ''BoUgFehNGE3+lGZVVXl+GSUGv1/VYKBE8GPE0SHCCuMdcVvvcJwscgBzOnTDZwXV'',
@with = N''REPLACE'',' + CASE WHEN @KeepCDC = 1 THEN ' @with = N''KEEP_CDC'','
ELSE ''
END + '
@with = N''STATS = 10'',
' + ISNULL(Util.dbo.StringConcat('@with = N''' + REPLACE('MOVE N''' + Logicalname + ''' TO N''' + Volume + @DatabaseName + '_' + Logicalname + @FilePostFix
+ CASE WHEN FileGroupName = 'PRIMARY' THEN '.mdf'
WHEN FileGroupName IS NULL THEN '.ldf'
WHEN [TYPE] = 'D' THEN '.ndf'
ELSE ''
END + '''', '''', '''''') + ''',', '
'), 'N/A') + '
@affinity = 0,
@logging = 0'
FROM #RestoreFileList
ELSE
SELECT @SQL = 'USE master
' + ISNULL(@SQL, '') + 'RESTORE DATABASE [' + @DatabaseName + ']
FROM DISK = N''' + @BackupPath + '''
WITH FILE = ' + CAST(@RestorePosition AS VARCHAR) + ',
' + ISNULL(Util.dbo.StringConcat('MOVE N''' + Logicalname + ''' TO N''' + Volume + @DatabaseName + '_' + Logicalname + @FilePostFix
+ CASE WHEN FileGroupName = 'PRIMARY' THEN '.mdf'
WHEN FileGroupName IS NULL THEN '.ldf'
WHEN [TYPE] = 'D' THEN '.ndf'
ELSE ''
END + '''', ',
'), 'N/A') + ',
NOUNLOAD, REPLACE, ' + CASE WHEN @KeepCDC = 1 THEN 'KEEP_CDC, '
ELSE ''
END + 'STATS = 10'
FROM #RestoreFileList
SET @SQL = @SQL + CASE WHEN @KeepCDC = 1 THEN '
EXEC(''' + REPLACE('
USE [' + @DatabaseName + ']
EXEC sys.sp_cdc_drop_job @job_type = N''capture'';
EXEC sys.sp_cdc_drop_job @job_type = N''cleanup'';
EXEC sys.sp_cdc_add_job @job_type = ''capture''
EXEC sys.sp_cdc_add_job @job_type = ''cleanup''
', '''', '''''') + ''')'
ELSE ''
END + ISNULL('
EXEC [' + @DatabaseName + '].dbo.sp_changedbowner @loginame = N''' + @SetDbOwner + ''', @map = false
', '') + CASE WHEN @SetReadOnly = 1 THEN '
USE [master]
ALTER DATABASE [' + @DatabaseName + '] SET READ_ONLY WITH ROLLBACK IMMEDIATE
' ELSE ''
END
IF @PrintSQL = 1
EXEC Util.dbo.PrintLargeText
@SQL
IF @ExecSQL = 1
EXEC(@SQL)
GO
EXEC sys.sp_ms_marksystemobject
sp_Restore
GO