Planning restore operations ahead of time is important to determine the space needed and in automation task Here, we provide the actual BAK file and its path, we read the backup file using the RESTORE HEADERONLY and FILELISTONLY operations.
Make sure your account has privileges to read the backup if its stored on a network location.
/*
DESCRIPTION Script Generator
It creates the RESTORE scripts for ONE backup (bak) file
and lists the space needed.
Configuration At the top:
Author: Miguel Quintana
Compatibility list: MSSQL2016 MSSQL2012 MSSQL2008 MSSQL2005
not tested on 2014
Does not work:
MSSQL2005 for SQL2005 to work, the two tables for restore fileheaderonly and filelistonly need to be modIFied.
*/
SET NOCOUNT ON
--************** CONFIGURATION **************
DECLARE @path VARCHAR(1000)
DECLARE @DataPath NVARCHAR(3000)
DECLARE @TlogPath NVARCHAR(3000)
DECLARE @RELOCATE INT
DECLARE @OVERRIDE INT
-- Enter ful path to the BACK file
SET @path = 'k:\MSSQL11.SQL2012\MSSQL\Backup\AMTENTSQL1202T$SQL2012\Clratsdb7\FULL\AMTENTSQL1202T$SQL2012_Clratsdb7_FULL_20150501_010000.bak'
-- Overrider: Say 1 IF the database already exist and needs to be over written
-- Overrider: Say 0 IF the database is new to this server
SET @OVERRIDE = 1
--RELOCATE: IF you want to relocate the data files, say 1 and fill in the following:
-- IF not, say 0 and skip.
SET @RELOCATE = 1
-- Data files must end wit ha backslash
SET @DataPath = 'E:\MSSQL\DATA\'
-- Tlog Files must end wit ha backslash
SET @TlogPath = 'F:\TlogFolder\'
--************** END CONFIGURATION **************
DECLARE @databasename NVARCHAR(100)
DECLARE @physicalName NVARCHAR(100)
DECLARE @filename NVARCHAR(100)
DECLARE @filepath NVARCHAR (1000)
DECLARE @temp1 NVARCHAR (100)
DECLARE @temp2 NVARCHAR (100)
DECLARE @rownum INT
DECLARE @move NVARCHAR (1000)
DECLARE @bksizeTotal BIGINT
DECLARE @DATAsizeTotal BIGINT
DECLARE @TLOGsizeTotal BIGINT
-- initialize size counters
SET @bksizeTotal = 0
SET @DATAsizeTotal = 0
SET @TLOGsizeTotal = 0
IF OBJECT_ID('tempdb..#filelist') IS NOT NULL
DROP TABLE #filelist
IF OBJECT_ID('tempdb..#header') IS NOT NULL
DROP TABLE #header
CREATE TABLE #filelist
(
LogicalName NVARCHAR(1024),PhysicalName NVARCHAR(1024),[Type] char(1),FileGroupName NVARCHAR(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,SourceBlocSize INT,FileGroupId INT,
LogGroupGUID UNIQUEIDENTIFIER,DIFferentialBaseLSN NUMERIC(25,0),DIFferentialBaseGUID UNIQUEIDENTIFIER,
IsReadOnly BIT,IsPresent BIT
)
CREATE TABLE #header
(
BackupName NVARCHAR(512),BackupDescription NVARCHAR(255),[BackupType] SMALLINT,ExpirationDate DATETIME,Compressed BIT,
Position SMALLINT,DeviceType TINYINT,UserName NVARCHAR (128)
,ServerName NVARCHAR (128),DatabaseName NVARCHAR(512),DatabaseVersion INT
,DatabaseCreationDate datetime,BackupSize numeric(20,0),FirstLSN numeric(25,0),LastLSN NUMERIC(25,0),CheckPointLSN NUMERIC (25,0),
DBBKLSN NUMERIC (25,0),BackupStartDate DATETIME,BackupFinishDate DATETIME
,SortOrder SMALLINT,[CodePage] INT,UnicodeLocal INT,UnicodeComp INT,CompLevl INT,softwVendId INT,SoftVersionMajor INT,
SoftVersionMinor INT,SoftVersionBuild INT,MachineName NVARCHAR(128)
,flag INT,BindingId UNIQUEIDENTIFIER,RecoveryForkID UNIQUEIDENTIFIER,collation Nvarchar(128)
,FamilyGUID UNIQUEIDENTIFIER,hasbulkloggedata BIT,issnapshot BIT,isreadonly BIT
,issingleuser bit,hadbackupchecksums bit, isdamaged bit, beginslogchain bit, HasIncomplMetaData bit
, isforceoffline INT,iscopyonly INT,FirstRecoveryForkID UNIQUEIDENTIFIER, forkpointlsn NUMERIC (25,0), RecoveryModel NVARCHAR(60),
dIFfBaseLSN NUMERIC (25,0), dIFfbaseGUID UNIQUEIDENTIFIER
,backupTypeDesc NVARCHAR (50),BKSETGUID UNIQUEIDENTIFIER
)
DECLARE @Version numeric(18,10)
SET @Version = CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - 1) + '.' + REPLACE(RIGHT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)), LEN(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))),'.','') AS numeric(18,10))
-- If this is SQL2008 or higher, add more columns to support compression
IF @VERSION >= 10
BEGIN
ALTER TABLE #filelist
ADD TDEThumbPRINT bit
ALTER TABLE #header
ADD CompressedBackupSize BIGINT
END
-- If this is SQL2012 or higher, add more columns to support contained database
IF @Version >= 11
BEGIN
ALTER TABLE #Header
ADD containmnet bit
END
-- If this is SQL2016 or higher, add more columns to support contained database
IF @Version >= 13
BEGIN
ALTER TABLE #Header
ADD KeyAlgorithm nvarchar(32)
ALTER TABLE #Header
ADD EncryptorThumbprint varbinary(20)
ALTER TABLE #Header
ADD EncryptorType nvarchar(32)
ALTER TABLE #filelist
add SnapshotURL nvarchar(360)
END
-- Insert the data in our tables
INSERT #header
EXEC ('restore headeronly FROM disk = ''' + @path + '''')
INSERT #filelist
EXEC ('restore filelistonly FROM disk = ''' + @path + '''')
SELECT @databasename=DatabaseName FROM #header
SET @move = '
-- *************************************************
-- Restore Script For Database ' + @databasename + '
-- *************************************************
'
IF @OVERRIDE=1 -- if override, place database on single_user mode
SET @move=@move+'ALTER DATABASE ['+@databasename+'] SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
SET @move=@move+
'
RESTORE DATABASE ['+@databasename+']
FROM DISK ='''+@path+'''
WITH FILE = 1, -- assumes only one database backup per media family
'
-- First WHILE for data files.
DECLARE @DataFileTable table
(rowcnt INT identity(1,1), LogicalName varchar(4000), physpath varchar(4000))
INSERT INTO @DataFileTable
SELECT LogicalName,
reverse(left(reverse(PhysicalName),charindex('\',reverse(PhysicalName),1) - 1)) FROM #filelist --'
where [type]= 'D'
SET @rownum =1
WHILE @rownum < = (select count(*) from @DataFileTable)
BEGIN
SELECT @temp1=LogicalName, @temp2=physpath FROM @DataFileTable WHERE rowcnt = @rownum
-- start adding size info
SELECT @DATAsizeTotal=@DATAsizeTotal+size FROM #filelist where LogicalName =@temp1
-- SET the new path
SET @temp2 = @DataPath+@temp2
-- IF Relocate is 0, gets the current physical path and assign it to @temp2
IF @RELOCATE=0
SELECT @temp2 = PhysicalName FROM #filelist where LogicalName = @temp1
-- concatenante the move portion of the restore script.
SET @move = @move + 'Move N'''+ @temp1 + ''' to N'''+ @temp2 + ''',
'
set @rownum = @rownum+1
END
-- Second cursor for Tlog files.
DECLARE @TlogFileTable table
(rowcnt INT identity(1,1), LogicalName varchar(4000), physpath varchar(4000))
INSERT INTO @TlogFileTable
SELECT LogicalName,
reverse(left(reverse(PhysicalName),charindex('\',reverse(PhysicalName),1) - 1)) FROM #filelist
where [type]= 'L'
SET @rownum =1
WHILE @rownum <= (select count(*) from @TlogFileTable)
BEGIN
SELECT @temp1=LogicalName, @temp2=physpath FROM @TlogFileTable WHERE rowcnt = @rownum
-- start adding size info
SELECT @TLOGsizeTotal=@TLOGsizeTotal+size FROM #filelist where LogicalName =@temp1
-- IF Relocate is 0, gets the current physical path and assign it to @temp2
IF @RELOCATE=0
SELECT @temp2 = PhysicalName FROM #filelist
where LogicalName = @temp1
SET @temp2 = @TlogPath+@temp2
SET @move = @move + 'Move N'''+ @temp1 + ''' to N'''+ @temp2 + ''',
'
SET @rownum = @rownum+1
END
SET @move = @move +'
NOUNLOAD, REPLACE, STATS = 10
GO
'
IF @OVERRIDE=1
SET @move=@move+'ALTER DATABASE ['+@databasename+'] SET MULTI_USER
GO
SET @BKSizeTotal = @DATASizeTotal + @TLOGSizeTotal
'
PRINT @move
PRINT ' /* ***********************--'
PRINT 'Total Size Needed in all disks '+ cast (@BkSizeTotal/1024/1024/1024 as varchar(10))+ ' GB'
PRINT ' --'
PRINT 'Total Size Needed for DATA files '+ cast (@DATASizeTotal/1024/1024/1024 as varchar(10))+ ' GB'
PRINT ' --'
PRINT 'Total Size Needed for TLOG files '+ cast (@TLOGSizeTotal/1024/1024/1024 as varchar(10))+ ' GB'
PRINT ' --'
PRINT ' *********************** */'
DROP TABLE #filelist
DROP TABLE #headervers