May 31, 2013 at 10:40 am
Comments posted to this topic are about the item Restore Script Generator
June 18, 2013 at 5:31 am
When @filecounter = 0 we only see the value of @BackupFilePattern. The next line is --EXEC(@BackupFilePattern).
When you remove the -- you will see why @filecounter = 0.
Kees
June 18, 2013 at 8:54 am
I got an error when using this script in SQL Server 2012 SP1 -
--BackupFile restore (JensS@Microsoft.com), 08.09.2011 V0.2
--=========================================================
--Generating command for found file \\p015\Sql_backup\$DEV2\DBname\FULL\$DEV2_DBname_FULL.bak
Msg 213, Level 16, State 7, Line 1
Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE HEADERONLY is terminating abnormally.
June 18, 2013 at 10:19 am
Here is a stored procedure which I have successfully tested on SQL Server 2012 : this might help to solve your problem.
I had to write specific code for SQL Server 2012 because of the different output of RESTORE HEADERONLY.
Moreover the procedure can deal with point in time recovery by restoring appropriate log backups.
This procedure is however totally different from the original post because I've developed it myself a few months ago.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/***** Author ******/
/* Gilles Ducassou */
/*******************/
/*********************************************** WARNING ********************************************/
/* If the value for parameter @only_generate_restore_script is not the default value, */
/* the database will be restored and OVERWRITE ANY DATABASE WITH THE SAME NAME as @restored_dbname */
/****************************************************************************************************/
/*********/
/* Notes */
/*********/
/*
- Has been tested on
SQL Server 2005 (no support for full text indexes backups)
SQL Server 2008
SQL Server 2008 R2
SQL Server 2012 (no test regarding contained database)
Windows 2003 R2
Windows 2008 R2
Clustered and non clustered instances
- Known limitations :
This procedure is designed to deal with a directory containing several files containing only on backup (log backup or differential backup or full backup)
Backup files containing more than one backup are not properly supported
No test has been done on encrypted backups
*/
/********************/
/* Parameters usage */
/********************/
/*
@backup_directory NVARCHAR(255)
- e.g. N'S:\MSSQL10_50.MSSQLSERVER\MSSQL\Backup'
- No default value
,@restored_dbname sysname
- e.g. N'MyRestoredDatabase'
- No default value
,@data_directory NVARCHAR(255) = NULL
- e.g. N'R:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA'
- Default value --> initialization from registry key HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServer\DefaultData
,@log_directory NVARCHAR(255) = NULL
- e.g. N'M:\MSSQL10_50.MSSQLSERVER\MSSQL\Data'
- Default value --> initialization from registry key HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServer\DefaultLog
,@original_dbname sysname = NULL
- e.g. N'MyDatabase'
- Default value is OK is @backup_directory contains backup files from a unique database
,@stop_at_time datetime = NULL
- e.g. N'2013-18-06 18:00:00.000'
- Default value --> restores the most recent available backup
,@only_generate_restore_script bit = 1
- if 0 : Restore script will be printed but not executed ; script to fix orphaned users will not be generated
- if 1 : Restore script will be printed and executed
AND ANY EXISTING DATABASE WITH THE SAME NAME AS @restored_dbname WILL BE OVERWRITTEN
,@new_owner sysname = NULL
- e.g N'MyDatabaseOwner
- Default value --> sa will be the owner of the restored database in case @only_generate_restore_script = 1
,@auto_fix_orphaned_users bit = 1
- if 1 and @only_generate_restore_script = 0 : will 'attach' users from the restored database to existing logins
- if 0 or @only_generate_restore_script = 1 : no script for 'attaching' users from the restored database to existing logins
,@pre_restore_script NVARCHAR(MAX) = NULL
- e.g 'EXEC xp_readerrorlog;'
,@post_restore_script NVARCHAR(MAX) = NULL
- e.g 'EXEC xp_readerrorlog;'
,@old_path_part NVARCHAR(255) = NULL
,@new_path_part NVARCHAR(255) = NULL
Used to alter the location where data files and lof files will be restored, based on the original location of these files.
I use this for restoring production database from a clustered instance on a preproduction clustered instance on a daily basis.
For instance, consider the following :
- original database :
- data file stored within I:\ProductionNamedInstance1\DataMountPoint
- log file stored within I:\ProductionNamedInstance1\LogMountPoint
- restored database :
- data file must be stored within I:\PreprodNamedInstance1\DataMountPoint
- log file must be stored within I:\PreprodNamedInstance1\LogMountPoint
- parameters :
- @old_path_part must be set to N'ProductionNamedInstance1'
- @new_path_part must be set to N'PreprodNamedInstance1'
*/
CREATE PROCEDURE [dbo].[p_restore_database_from_directory]
@backup_directory NVARCHAR(255) --e.g. N'S:\MSSQL10_50.MSSQLSERVER\MSSQL\Backup'
,@restored_dbname sysname --e.g. N'DBA_VPPRDSQL3'
,@data_directory NVARCHAR(255) = NULL--e.g. N'P:\MSSQL10_50.VPPPRISQL3\MSSQL\DATA'
,@log_directory NVARCHAR(255) = NULL--e.g. N'M:\MSSQL10_50.VPPPRISQL3\MSSQL\Data'
,@original_dbname sysname = NULL --e.g. N'DBA' --used in case there are backups from several databases within the backup directory
,@stop_at_time datetime = NULL
,@only_generate_restore_script bit = 0
,@new_owner sysname = NULL
,@auto_fix_orphaned_users bit = 0
,@pre_restore_script NVARCHAR(MAX) = NULL
,@post_restore_script NVARCHAR(MAX) = NULL
,@old_path_part NVARCHAR(255) = NULL
,@new_path_part NVARCHAR(255) = NULL
AS
BEGIN
DECLARE @major_product_version tinyint
DECLARE @backup_media NVARCHAR(MAX)
DECLARE @stmt NVARCHAR(MAX)
DECLARE @cmd VARCHAR(8000)
DECLARE @full_backup_position smallint
DECLARE @differential_backup_position smallint
DECLARE @move_data_files_script nvarchar(MAX)
DECLARE @move_log_files_script nvarchar(MAX)
DECLARE @binding_id uniqueidentifier
DECLARE @last_log_backup_start_date datetime
DECLARE @first_log_backup_start_date datetime
DECLARE @max_row_id int
DECLARE @orphaned_user sysname
SET NOCOUNT ON
IF LTRIM(RTRIM(@backup_directory)) = N''
BEGIN
SET @backup_directory = NULL;
END;
IF LTRIM(RTRIM(@restored_dbname)) = N''
BEGIN
SET @restored_dbname = NULL;
END;
IF LTRIM(RTRIM(@data_directory)) = N''
BEGIN
SET @data_directory = NULL;
END;
IF LTRIM(RTRIM(@log_directory)) = N''
BEGIN
SET @log_directory = NULL;
END;
IF LTRIM(RTRIM(@original_dbname)) = N''
BEGIN
SET @original_dbname = NULL;
END;
IF LTRIM(RTRIM(@new_owner)) = N''
BEGIN
SET @new_owner = NULL;
END;
IF LTRIM(RTRIM(@pre_restore_script)) = N''
BEGIN
SET @pre_restore_script = NULL;
END;
IF LTRIM(RTRIM(@post_restore_script)) = N''
BEGIN
SET @post_restore_script = NULL;
END;
------------------------------------------------------------------------
--Execute the pre-restore script
------------------------------------------------------------------------
IF (@pre_restore_script IS NOT NULL AND @only_generate_restore_script = 0)
BEGIN
PRINT @pre_restore_script
EXECUTE AS LOGIN = 'sa';
EXEC(@pre_restore_script);
REVERT;
END;
------------------------------------------------------------------------
------------------------------------------------------------------------
--Initializations
------------------------------------------------------------------------
SELECT
@major_product_version = CAST(SUBSTRING(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(14)), 1, CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(14)),
CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(14))) + 1) - 1) AS float)
IF @stop_at_time IS NULL
BEGIN
SET @stop_at_time = GETDATE();
END;
IF @data_directory IS NULL
BEGIN
EXEC master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE'
, N'Software\Microsoft\MSSQLServer\MSSQLServer'
, N'DefaultData'
, @data_directory output;
IF @data_directory IS NULL
BEGIN
EXEC master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE'
, N'Software\Microsoft\MSSQLServer\Setup'
, N'SQLDataRoot'
, @data_directory output;
END;
END;
IF @log_directory IS NULL
BEGIN
EXEC master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE'
, N'Software\Microsoft\MSSQLServer\MSSQLServer'
, N'DefaultLog'
, @log_directory output;
END;
SET @log_directory = ISNULL(@log_directory,@data_directory)
------------------------------------------------------------------------
------------------------------------------------------------------------
--Lists the .BAK and .TRN files within the backup directory and stores the result in a temporary table
------------------------------------------------------------------------
IF OBJECT_ID('tempdb.dbo.#BackupMedium') IS NOT NULL
DROP TABLE #BackupMedium
SET @cmd = 'DIR /B ' + @backup_directory;
CREATE TABLE #BackupMedium (backup_medium NVARCHAR(255) null);
INSERT INTO #BackupMedium EXEC master..xp_cmdshell @cmd;
DELETE FROM #BackupMedium WHERE /*NOT(backup_medium LIKE N'%.TRN' OR backup_medium LIKE N'%.BAK') OR*/ backup_medium IS NULL;
------------------------------------------------------------------------
------------------------------------------------------------------------
--Lists the content of the backup medium and stores the result in a temporary table
------------------------------------------------------------------------
IF OBJECT_ID('tempdb.dbo.#BackupHeader') IS NOT NULL
BEGIN
DROP TABLE #CompleteBackupHeader;
END;
CREATE TABLE #CompleteBackupHeader
(
BackupMedia nvarchar(MAX),
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(60),
DifferentialBaseLSN numeric(25,0),
DifferentialBaseGuid uniqueidentifier,
BackupTypeDescription nvarchar(60),
BackupSetGUID uniqueidentifier,
CompressedBackupSize numeric(20,0)
);
IF(@@ERROR <> 0)
BEGIN
RAISERROR (N'ERROR CREATE temporary table CompleteBackupHeader.',
11,
1) WITH LOG;
END;
IF OBJECT_ID('tempdb.dbo.#BackupHeader') IS NOT NULL
BEGIN
DROP TABLE #BackupHeader;
END;
IF (@major_product_version >= 11)
BEGIN
CREATE TABLE #BackupHeaderV11
(
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(60),
DifferentialBaseLSN numeric(25,0),
DifferentialBaseGuid uniqueidentifier,
BackupTypeDescription nvarchar(60),
BackupSetGUID uniqueidentifier,
CompressedBackupSize numeric(20,0),
containment tinyint
);
END;
CREATE TABLE #BackupHeader
(
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(60),
DifferentialBaseLSN numeric(25,0),
DifferentialBaseGuid uniqueidentifier,
BackupTypeDescription nvarchar(60),
BackupSetGUID uniqueidentifier,
CompressedBackupSize numeric(20,0)
);
IF(@@ERROR <> 0)
BEGIN
RAISERROR (N'ERROR CREATE temporary table BackupHeader.',
11,
1) WITH LOG;
END;
DECLARE c_backup_medium CURSOR FAST_FORWARD FOR
SELECT
@backup_directory + N'\' + bm.backup_medium
FROM
#BackupMedium bm;
OPEN c_backup_medium;
FETCH NEXT FROM c_backup_medium INTO @backup_media;
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@major_product_version >= 11)
BEGIN
TRUNCATE TABLE #BackupHeaderV11;
INSERT INTO #BackupHeaderV11
EXEC ('RESTORE HEADERONLY FROM DISK=''' + @backup_media + '''');
INSERT INTO #BackupHeader(
BackupName
,BackupDescription
,BackupType
,ExpirationDate
,Compressed
,Position
,DeviceType
,UserName
,ServerName
,DatabaseName
,DatabaseVersion
,DatabaseCreationDate
,BackupSize
,FirstLSN
,LastLSN
,CheckpointLSN
,DatabaseBackupLSN
,BackupStartDate
,BackupFinishDate
,SortOrder
,CodePage
,UnicodeLocaleId
,UnicodeComparisonStyle
,CompatibilityLevel
,SoftwareVendorId
,SoftwareVersionMajor
,SoftwareVersionMinor
,SoftwareVersionBuild
,MachineName
,Flags
,BindingID
,RecoveryForkID
,Collation
,FamilyGUID
,HasBulkLoggedData
,IsSnapshot
,isReadOnly
,IsSingleUser
,HasBackupChecksums
,IsDamaged
,BeginsLogChain
,HasIncompleteMetaData
,IsForceOffline
,IsCopyOnly
,FirstRecoveryForkID
,ForkPointLSN
,RecoveryModel
,DifferentialBaseLSN
,DifferentialBaseGuid
,BackupTypeDescription
,BackupSetGUID
,CompressedBackupSize
)
SELECT
BackupName
,BackupDescription
,BackupType
,ExpirationDate
,Compressed
,Position
,DeviceType
,UserName
,ServerName
,DatabaseName
,DatabaseVersion
,DatabaseCreationDate
,BackupSize
,FirstLSN
,LastLSN
,CheckpointLSN
,DatabaseBackupLSN
,BackupStartDate
,BackupFinishDate
,SortOrder
,CodePage
,UnicodeLocaleId
,UnicodeComparisonStyle
,CompatibilityLevel
,SoftwareVendorId
,SoftwareVersionMajor
,SoftwareVersionMinor
,SoftwareVersionBuild
,MachineName
,Flags
,BindingID
,RecoveryForkID
,Collation
,FamilyGUID
,HasBulkLoggedData
,IsSnapshot
,isReadOnly
,IsSingleUser
,HasBackupChecksums
,IsDamaged
,BeginsLogChain
,HasIncompleteMetaData
,IsForceOffline
,IsCopyOnly
,FirstRecoveryForkID
,ForkPointLSN
,RecoveryModel
,DifferentialBaseLSN
,DifferentialBaseGuid
,BackupTypeDescription
,BackupSetGUID
,CompressedBackupSize
FROM
#BackupHeaderV11;
END;
ELSE
BEGIN
INSERT INTO #BackupHeader
EXEC ('RESTORE HEADERONLY FROM DISK=''' + @backup_media + '''');
END;
IF(@@ERROR <> 0)
BEGIN
RAISERROR (N'ERROR RESTORE HEADERONLY.',
11,
1) WITH LOG;
END;
INSERT INTO
#CompleteBackupHeader
SELECT
CAST(@backup_media AS NVARCHAR(MAX)),
bh.*
FROM
#BackupHeader bh;
TRUNCATE TABLE #BackupHeader;
FETCH NEXT FROM c_backup_medium INTO @backup_media;
END;
CLOSE c_backup_medium;
DEALLOCATE c_backup_medium;
--Delete information which does not refers to the desired database backup
IF @original_dbname IS NOT NULL
BEGIN
DELETE FROM #CompleteBackupHeader WHERE DatabaseName <> @original_dbname;
END;
--Retrieves information regarding what should be the only database whose backup files are located in the backup directory
SELECT TOP 1
@original_dbname = ISNULL(@original_dbname, DatabaseName)
,@binding_id = BindingID
FROM
#CompleteBackupHeader
--Raise an error if there are some backups from several databases
IF EXISTS (SELECT TOP 1 1 FROM #CompleteBackupHeader WHERE (DatabaseName <> @original_dbname OR BindingID <> @binding_id))
BEGIN
RAISERROR (N'Error while running [p_restore_database_from_directory] : Backup directory contains backups from several databases.',
11,
1) WITH LOG;
END;
------------------------------------------------------------------------
------------------------------------------------------------------------
--Lists the files' information backed up within the backup medium and stores the result in a temporary table
------------------------------------------------------------------------
IF OBJECT_ID('tempdb.dbo.#BackupFileInformation') IS NOT NULL
BEGIN
DROP TABLE #CompleteBackupFileInformation;
END;
CREATE TABLE #CompleteBackupFileInformation
(
BackupMedia nvarchar(MAX),
LogicalName nvarchar(128),
PhysicalName nvarchar(260),
Type char(1),
FileGroupName nvarchar(128),
Size numeric(20, 0),
MaxSize numeric(20, 0),
FileID bigint,
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,
FleGroupID int,
LogGroupGUID uniqueidentifier NULL,
DifferentialBaseLSN numeric(25, 0) NULL,
DifferentialBaseGUID uniqueidentifier NULL,
IsReadOnly bit,
IsPresent bit,
TDEThumbprint varbinary(32)
);
IF(@@ERROR <> 0)
BEGIN
RAISERROR (N'ERROR CREATE temporary table CompleteBackupFileInformation.',
11,
1) WITH LOG;
END;
IF OBJECT_ID('tempdb.dbo.#BackupFileInformation') IS NOT NULL
BEGIN
DROP TABLE #BackupFileInformation;
END;
CREATE TABLE #BackupFileInformation
(
LogicalName nvarchar(128),
PhysicalName nvarchar(260),
Type char(1),
FileGroupName nvarchar(128),
Size numeric(20, 0),
MaxSize numeric(20, 0),
FileID bigint,
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,
FleGroupID int,
LogGroupGUID uniqueidentifier NULL,
DifferentialBaseLSN numeric(25, 0) NULL,
DifferentialBaseGUID uniqueidentifier NULL,
IsReadOnly bit,
IsPresent bit,
TDEThumbprint varbinary(32)
);
IF(@@ERROR <> 0)
BEGIN
RAISERROR (N'ERROR CREATE temporary table BackupFileInformation.',
11,
1) WITH LOG;
END;
DECLARE c_backup_media CURSOR FAST_FORWARD FOR
SELECT
cbh.BackupMedia
FROM
#CompleteBackupHeader cbh;
OPEN c_backup_media;
FETCH NEXT FROM c_backup_media INTO @backup_media;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #BackupFileInformation
EXEC ('RESTORE FILELISTONLY FROM DISK=''' + @backup_media + '''');
IF(@@ERROR <> 0)
BEGIN
RAISERROR (N'ERROR RESTORE FILELISTONLY.',
11,
1) WITH LOG;
END;
INSERT INTO
#CompleteBackupFileInformation
SELECT
@backup_media,
bh.*
FROM
#BackupFileInformation bh;
TRUNCATE TABLE #BackupFileInformation;
FETCH NEXT FROM c_backup_media INTO @backup_media;
END;
CLOSE c_backup_media;
DEALLOCATE c_backup_media;
INSERT INTO #BackupFileInformation(
LogicalName ,
PhysicalName,
Type,
FileGroupName,
Size,
MaxSize,
FileID,
CreateLSN,
DropLSN,
UniqueID,
ReadOnlyLSN,
ReadWriteLSN,
BackupSizeInBytes,
SourceBlockSize,
FleGroupID,
LogGroupGUID,
DifferentialBaseLSN,
DifferentialBaseGUID,
IsReadOnly,
IsPresent,
TDEThumbprint
)
SELECT DISTINCT
cbfi.LogicalName ,
cbfi.PhysicalName,
cbfi.Type,
cbfi.FileGroupName,
cbfi.Size,
cbfi.MaxSize,
cbfi.FileID,
cbfi.CreateLSN,
cbfi.DropLSN,
cbfi.UniqueID,
cbfi.ReadOnlyLSN,
cbfi.ReadWriteLSN,
cbfi.BackupSizeInBytes,
cbfi.SourceBlockSize,
cbfi.FleGroupID,
cbfi.LogGroupGUID,
cbfi.DifferentialBaseLSN,
cbfi.DifferentialBaseGUID,
cbfi.IsReadOnly,
cbfi.IsPresent,
cbfi.TDEThumbprint
FROM
#CompleteBackupFileInformation cbfi;
------------------------------------------------------------------------
------------------------------------------------------------------------
--Keep Only the useful backups in a dedicated table
------------------------------------------------------------------------
IF OBJECT_ID('tempdb.dbo.#UsefulBackupHeader') IS NOT NULL
BEGIN
DROP TABLE #UsefulBackupHeader;
END;
CREATE TABLE #UsefulBackupHeader
(
ROW_ID int identity(1,1),
BackupMedia nvarchar(MAX),
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(60),
DifferentialBaseLSN numeric(25,0),
DifferentialBaseGuid uniqueidentifier,
BackupTypeDescription nvarchar(60),
BackupSetGUID uniqueidentifier,
CompressedBackupSize numeric(20,0)
);
IF(@@ERROR <> 0)
BEGIN
RAISERROR (N'ERROR CREATE temporary table UsefulBackupHeader.',
11,
1) WITH LOG;
END;
------------------------------------------------------------------------
------------------------------------------------------------------------
--Retrieve the necessary full backup
------------------------------------------------------------------------
INSERT INTO
#UsefulBackupHeader
SELECT TOP 1
cbh.*
FROM
#CompleteBackupHeader cbh
WHERE
cbh.BackupType = 1
AND
cbh.BackupStartDate <= @stop_at_time
ORDER BY
cbh.BackupStartDate DESC
IF(@@ROWCOUNT = 0)
BEGIN
RAISERROR (N'ERROR: No usable full backup found.',
11,
1) WITH LOG;
END;
------------------------------------------------------------------------
------------------------------------------------------------------------
--Retrieve the most recent usable differential backup
------------------------------------------------------------------------
INSERT INTO
#UsefulBackupHeader
SELECT TOP 1
cbh.*
FROM
#CompleteBackupHeader cbh
INNER JOIN #UsefulBackupHeader ubh
ON
cbh.DifferentialBaseGuid = ubh.BackupSetGUID
AND
cbh.DatabaseBackupLSN = ubh.CheckpointLSN
WHERE
cbh.BackupType = 5
AND
cbh.BackupStartDate <= @stop_at_time
ORDER BY
cbh.BackupStartDate DESC
------------------------------------------------------------------------
------------------------------------------------------------------------
--Retrieve all of the necessary log backups
------------------------------------------------------------------------
DECLARE @earliest_log_last_lsn [numeric](25, 0);
SELECT
@earliest_log_last_lsn = MAX(ubh.[LastLSN])
FROM
#UsefulBackupHeader ubh
SET @last_log_backup_start_date = (
SELECT TOP 1
cbh.BackupStartDate
FROM
#CompleteBackupHeader cbh
WHERE
cbh.BackupStartDate > @stop_at_time
AND
cbh.BackupType = 2
ORDER BY
cbh.BackupStartDate
);
IF @last_log_backup_start_date IS NULL
BEGIN
SET @last_log_backup_start_date = (SELECT MAX(cbh.BackupStartDate) FROM #CompleteBackupHeader cbh WHERE cbh.BackupType = 2)
END;
SET @first_log_backup_start_date = (
SELECT
MAX(ubh.BackupStartDate)
FROM
#UsefulBackupHeader ubh
);
INSERT INTO
#UsefulBackupHeader
SELECT
cbh.*
FROM
#CompleteBackupHeader cbh
INNER JOIN #UsefulBackupHeader ubh
ON
cbh.DatabaseBackupLSN = ubh.CheckpointLSN
WHERE
cbh.BackupType = 2
AND
cbh.BackupStartDate >= @first_log_backup_start_date
AND
cbh.BackupStartDate <= @last_log_backup_start_date
AND
cbh.[LastLSN] >= @earliest_log_last_lsn
ORDER BY
cbh.BackupStartDate;
------------------------------------------------------------------------
------------------------------------------------------------------------
--Create the script part for moving data and log files
------------------------------------------------------------------------
IF(@old_path_part IS NOT NULL AND @new_path_part IS NOT NULL)
BEGIN
--Create paths from the database being restored
SELECT
@move_data_files_script = STUFF((
SELECT
', ' + 'MOVE N''' + bfid.[LogicalName] + N''' TO N''' + REPLACE(SUBSTRING(bfid.[PhysicalName], 1, LEN(bfid.[PhysicalName]) - CHARINDEX('\', REVERSE(bfid.[PhysicalName]))), @old_path_part, @new_path_part) + N'\' + @restored_dbname + CASE WHEN ROW_NUMBER() OVER(ORDER BY bfid.[FileId]) > 1 THEN N'_' + RIGHT('000' + cast(ROW_NUMBER() OVER(ORDER BY bfid.[FileId]) as varchar),3) + '.ndf' ELSE '.mdf' END + N''''
FROM
(SELECT DISTINCT bfi.[LogicalName], bfi.[PhysicalName], bfi.[FileId] FROM #BackupFileInformation bfi WHERE bfi.[Type] = 'D') bfid
FOR XML PATH ('')),1,1,'');
SELECT
@move_log_files_script = STUFF((
SELECT
', ' + 'MOVE N''' + bfil.[LogicalName] + N''' TO N''' + REPLACE(SUBSTRING(bfil.[PhysicalName], 1, LEN(bfil.[PhysicalName]) - CHARINDEX('\', REVERSE(bfil.[PhysicalName]))), @old_path_part, @new_path_part) + N'\' + @restored_dbname + CASE WHEN ROW_NUMBER() OVER(ORDER BY bfil.[FileId]) > 1 THEN N'_' + RIGHT('000' + cast(ROW_NUMBER() OVER(ORDER BY bfil.[FileId]) as varchar),3) ELSE '' END + N'.ldf'''
FROM
(SELECT DISTINCT bfi.[LogicalName], bfi.[PhysicalName], bfi.[FileId] FROM #BackupFileInformation bfi WHERE bfi.[Type] = 'L') bfil
FOR XML PATH ('')),1,1,'');
END;
ELSE
BEGIN
--Create paths from default data path and log path
SELECT
@move_data_files_script = STUFF((
SELECT
', ' + 'MOVE N''' + bfid.[LogicalName] + N''' TO N''' + @data_directory + N'\' + @restored_dbname + CASE WHEN ROW_NUMBER() OVER(ORDER BY bfid.[FileId]) > 1 THEN N'_' + RIGHT('000' + cast(ROW_NUMBER() OVER(ORDER BY bfid.[FileId]) as varchar),3) + '.ndf' ELSE '.mdf' END + N''''
FROM
(SELECT DISTINCT bfi.[LogicalName], bfi.[PhysicalName], bfi.[FileId] FROM #BackupFileInformation bfi WHERE bfi.[Type] = 'D') bfid
FOR XML PATH ('')),1,1,'');
SELECT
@move_log_files_script = STUFF((
SELECT
', ' + 'MOVE N''' + bfil.[LogicalName] + N''' TO N''' + @log_directory + N'\' + @restored_dbname + CASE WHEN ROW_NUMBER() OVER(ORDER BY bfil.[FileId]) > 1 THEN N'_' + RIGHT('000' + cast(ROW_NUMBER() OVER(ORDER BY bfil.[FileId]) as varchar),3) ELSE '' END + N'.ldf'''
FROM
(SELECT DISTINCT bfi.[LogicalName], bfi.[PhysicalName], bfi.[FileId] FROM #BackupFileInformation bfi WHERE bfi.[Type] = 'L') bfil
FOR XML PATH ('')),1,1,'');
END;
------------------------------------------------------------------------
------------------------------------------------------------------------
--Generate and execute restore scripts
------------------------------------------------------------------------
IF (@only_generate_restore_script = 0)
BEGIN
IF EXISTS (SELECT name FROM sys.databases WHERE name = @restored_dbname)
BEGIN
SET @stmt = N'
USE [master];
ALTER DATABASE ' + QUOTENAME(@restored_dbname) + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE ' + QUOTENAME(@restored_dbname) + ' SET OFFLINE WITH ROLLBACK IMMEDIATE;
ALTER DATABASE ' + QUOTENAME(@restored_dbname) + ' SET ONLINE WITH ROLLBACK IMMEDIATE;
DROP DATABASE ' + QUOTENAME(@restored_dbname) + ';';
PRINT @stmt
EXEC(@stmt);
END;
END;
SET @max_row_id = (SELECT MAX(ROW_ID) FROM #UsefulBackupHeader)
DECLARE c_stmt CURSOR READ_ONLY FOR
SELECT
N'
RESTORE '
+ CASE
WHEN ubh.BackupType = 2
THEN
N'LOG'
ELSE
N'DATABASE'
END
+ N' '
+ QUOTENAME(@restored_dbname)
+ N' FROM DISK = N'''
+ ubh.BackupMedia
+ N''' WITH FILE='
+ CAST(ubh.Position AS NVARCHAR)
+ N', NOUNLOAD, STATS = 10, '
+ @move_data_files_script
+ ', '
+ @move_log_files_script
+ CASE
WHEN ubh.BackupType <> 1 THEN
N', STOPAT = '''
+ CONVERT(NVARCHAR, @stop_at_time, 126)
+ N''''
ELSE
N''
END
+ N', NORECOVERY;'
FROM
#UsefulBackupHeader ubh
ORDER BY
ROW_ID;
OPEN c_stmt;
FETCH NEXT FROM c_stmt INTO @stmt;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @stmt;
IF (@only_generate_restore_script = 0)
BEGIN
EXEC(@stmt);
END;
FETCH NEXT FROM c_stmt INTO @stmt;
END;
CLOSE c_stmt;
DEALLOCATE c_stmt;
SET @stmt = N'
RESTORE DATABASE ' + QUOTENAME(@restored_dbname) + N' WITH RECOVERY;';
PRINT @stmt;
IF (@only_generate_restore_script = 0)
BEGIN
EXEC(@stmt);
END;
------------------------------------------------------------------------
------------------------------------------------------------------------
--Change the owner of the restored database
------------------------------------------------------------------------
--NB : Requires "GRANT IMPERSONATE ON LOGIN::sa to [xxxxxx]", assuming that the stored procedudre is being run by xxxxxx
IF(@new_owner IS NOT NULL)
BEGIN
SET @stmt = N'
USE ' + QUOTENAME(@restored_dbname) + N'
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N''' + @new_owner + N''')
DROP USER ' + QUOTENAME(@new_owner) + N'
EXEC sp_changedbowner ''' + @new_owner + N'''';
PRINT @stmt;
IF (@only_generate_restore_script = 0)
BEGIN
EXECUTE AS LOGIN = 'sa';
EXEC sp_executesql @stmt;
EXEC(@stmt);
REVERT;
END;
ELSE
BEGIN
SELECT @stmt;
END;
END;
------------------------------------------------------------------------
------------------------------------------------------------------------
--Attach orphaned database users to existing server logins
------------------------------------------------------------------------
IF (@auto_fix_orphaned_users = 1 AND @only_generate_restore_script = 0)
BEGIN
IF OBJECT_ID('tempdb.dbo.##tmp_p_restore_database_from_directory_orphaned_users') IS NOT NULL
BEGIN
DROP TABLE ##tmp_p_restore_database_from_directory_orphaned_users;
END;
SET @stmt = N'
USE ' + QUOTENAME(@restored_dbname) + N';
SELECT
sdp.[name] COLLATE French_CI_AI as [name]
INTO
##tmp_p_restore_database_from_directory_orphaned_users
FROM
sys.database_principals sdp
INNER JOIN sys.server_principals ssp_to_attach
ON sdp.[name] COLLATE French_CI_AI = ssp_to_attach.[name]
LEFT OUTER JOIN sys.server_principals ssp
ON sdp.[sid] = ssp.[sid]
WHERE
sdp.type = N''S''
AND
ssp.[sid] IS NULL
AND
sdp.[name] COLLATE French_CI_AI NOT IN (N''guest'', N''INFORMATION_SCHEMA'', N''sys'');'
EXECUTE AS LOGIN = 'sa';
EXEC(@stmt);
REVERT;
DECLARE c_orphaned_users CURSOR READ_ONLY FOR
SELECT
tmp.[name]
FROM
##tmp_p_restore_database_from_directory_orphaned_users tmp;
OPEN c_orphaned_users;
FETCH NEXT FROM c_orphaned_users INTO @orphaned_user;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @stmt = N'
USE ' + QUOTENAME(@restored_dbname) + N';
EXEC sp_change_users_login N''update_one'', N''' + @orphaned_user + N''', N''' + @orphaned_user + N'''';
PRINT @stmt
EXECUTE AS LOGIN = 'sa';
EXEC(@stmt);
REVERT;
FETCH NEXT FROM c_orphaned_users INTO @orphaned_user;
END;
CLOSE c_orphaned_users;
DEALLOCATE c_orphaned_users;
DROP TABLE ##tmp_p_restore_database_from_directory_orphaned_users;
END;
------------------------------------------------------------------------
------------------------------------------------------------------------
--Execute the post-restore script
------------------------------------------------------------------------
IF (@post_restore_script IS NOT NULL AND @only_generate_restore_script = 0)
BEGIN
PRINT @post_restore_script
EXECUTE AS LOGIN = 'sa';
EXEC(@post_restore_script);
REVERT;
END;
------------------------------------------------------------------------
END
GO
June 18, 2013 at 2:47 pm
Wow, that is a lot. Thanks.
December 31, 2021 at 7:00 am
This was removed by the editor as SPAM
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply