Restore Script Generator

  • Comments posted to this topic are about the item Restore Script Generator

  • 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

  • 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.

  • 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

  • Wow, that is a lot. Thanks.

  • 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