Automating Database Restores

  • Divine Flame

    SSCoach

    Points: 15941

    ggoble (4/23/2014)


    How does this script handle multiple files and filegroups?

    Edit: Does this script handle multiple file and file groups?

    Yes. It handles multiple file & filegroups.

    Here is an example for you to test:

    CREATE DATABASE FGTest

    ON

    (NAME='FGTest', FILENAME='D:\SQLTestLab\FGTest.mdf') ,

    (NAME='FGTest_2', FILENAME='D:\SQLTestLab\FGTest_2.ndf')

    LOG ON

    (NAME='FGTest_Log', FILENAME='D:\SQLTestLab\FGTest_Log.ldf')

    GO

    ALTER DATABASE FGTest ADD FILEGROUP [FG2]

    GO

    ALTER DATABASE FGTest ADD FILE (NAME='FGTest_3',FILENAME='D:\SQLTestLab\FGTest_3.ndf')

    TO FILEGROUP [FG2]

    GO

    BACKUP DATABASE FGTest TO DISK = 'D:\SQLTestLab\FGTest.BAK' WITH STATS=1, INIT

    Once you have created the database & the backup has been taken, you can execute this procedure to check the script printed by it

    GO

    EXECUTE SPS_RestoreDatabases @BackupFilesDirectory = 'D:\SQLTestLab',

    @NewDataFilesDirectory = 'D:\SQLTestLab\DatabaseFiles\DataFiles',

    @NewLogFilesDirectory = 'D:\SQLTestLab\DatabaseFiles\LogFiles',

    @NewOtherFilesDirectory = 'D:\SQLTestLab\DatabaseFiles\LogFiles',

    @BackupFileExtension = 'bak',

    @ReplaceExistingDatabases = 0,

    @IncludeSystemDatabases = 0,

    @AutoExecute = 0

    GO

    The script printed by the procedure is:

    --======================================== Restore Command For Database : [FGTest]

    RESTORE DATABASE [FGTest] FROM DISK ='D:\SQLTestLab\FGTest.BAK'

    WITH

    MOVE'FGTest' TO 'D:\SQLTestLab\DatabaseFiles\DataFiles\FGTest.mdf',

    MOVE'FGTest_2' TO 'D:\SQLTestLab\DatabaseFiles\DataFiles\FGTest_2.ndf',

    MOVE'FGTest_3' TO 'D:\SQLTestLab\DatabaseFiles\DataFiles\FGTest_3.ndf',

    MOVE 'FGTest_Log' TO 'D:\SQLTestLab\DatabaseFiles\LogFiles\FGTest_Log.ldf',

    STATS = 1;

    --==================================================================================================================================


    Sujeet Singh

  • Divine Flame

    SSCoach

    Points: 15941

    jswong05 (4/23/2014)


    http://dbace.us

    Actually on SQLSaturday#308, I will show you how to generate a restore script from MSDB using T-SQL.

    Jason

    http://dbace.us

    Hi Jason,

    Actually I have seen that already, what you are going to show on SQLSaturday#308. You can also see it here on SSC on following link:

    T-SQL Restore Script Generator By Paul Brewer

    [/url]

    jswong05 (4/23/2014)

    I wrote this and use it for many years on many jobs.

    I am sure whatever little You & I have written, has already been written by many others (whether or not they shared their work with the outer world).

    jswong05 (4/23/2014)

    Two advantages:

    1) the MSDB catalog has LSN info as well.

    Sure MSDB has got the LSN info, but so does the backup file itself. You can get this information using RESTORE HEADERONLY.

    In fact I am thinking to write a similar procedure which generates the restore script based on LSN so that I can include the Differential & Log backup restore functionality as well in my current procedure (currently it supports FULL backups only).

    However I will do that by reading the backup files from the directory so that the procedure works even when the MSDB is lost due to a server crash or something (we can use Paul Brewer's script when we have MSDB available).

    Now, regarding the benefits you told :

    jswong05 (4/23/2014)

    Two advantages:

    1) the MSDB catalog has LSN info as well.

    2) in case someone did an ad-hoc backup that is stored somewhere else, the restore script generated from backup directory files won't work for broken chain. :-D:-P:w00t:

    1.In case MSDB on the original server has been lost you will be unable to generate the restore script that depends on MSDB to work properly.

    2.In case someone did an ad-hoc backup & deleted that backup file afterwards the restore script generated from MSDB system tables won't work either 😉

    As you can see, my point is, that there is nothing that can guarantee to work 100% in all cases. So, the procedure provided in the article & the procedure that you are going to show in SQLSaturday, they both have their places in different situations & environments.


    Sujeet Singh

  • safazam

    Valued Member

    Points: 65

    That was great. Very handy for DR. I am trying to restore all user databases with one script after restoring system databases.

    I am having this error with that script:

    I must have missed something

    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.

    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.

  • JoseM

    SSC Enthusiast

    Points: 122

    Do a restore header only and check what field is not present in the output in the table definition.

  • safazam

    Valued Member

    Points: 65

    Here is what I got:

    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

    Dummy_backup_2014_04_24_142404_6342177 NULL 1 NULL 1 1 2 NT AUTHORITY\SYSTEM DummyInstance Dummy 655 2013-12-09 15:22:31.000 5596160 59000000046100037 59000000047700001 59000000046100037 59000000043600037 2014-04-24 14:24:05.000 2014-04-24 14:24:05.000 52 0 1033 196609 100 4608 10 0 1600 DummyInstance 512 5DBEB00A-CEC3-4AF6-8752-8D1C5B20C4E1 6A68F82C-88F7-492A-BEC8-B55ABC7B3C63 SQL_Latin1_General_CP1_CI_AS 6A68F82C-88F7-492A-BEC8-B55ABC7B3C63 0 0 0 0 0 0 0 0 0 0 6A68F82C-88F7-492A-BEC8-B55ABC7B3C63 NULL SIMPLE NULL NULL Database 0E70B246-6128-4CAB-9070-72ED0858EEA5 2004365

  • Divine Flame

    SSCoach

    Points: 15941

    safazam (4/24/2014)


    That was great. Very handy for DR. I am trying to restore all user databases with one script after restoring system databases.

    I am having this error with that script:

    I must have missed something

    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.

    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.

    Hi Safazam,

    I think you got those errors when you were running the script on a SQL Server version below SQL Server 2012.

    You were getting those errors because of the minor changes in output of RESTORE HEADERONLY command in various SQL Server versions. The output of RESTORE HEADERONLY command is different in SQL Server 2012 from what you get in SQL Server 2008 or SQL Server 2005.

    Same is true for RESTORE FILELISTONLY command also.

    I have updated the script now so that it can detect the version & executes accordingly.

    Updated script is given below (it is also attached as a txt file under my signature in this post) & this should work equally well on SQL Server 2005, SQL Server 2008 , SQL Server 2008R2 & SQL Server 2012.

    Let me know in case it is still not working on any of the specified versiosn.

    Script:

    /********************************************************************************************************************

    -----------> Script to restore all backup files inside a directory to a SQL Server instance. <-----------

    Author : Sujeet Pratap Singh

    Procedure : SPS_RestoreBackup

    Description : This stored procedure takes the directory location of sql server database backup files and restores all the database

    backup files from given location.

    Parameters : @BackupFilesDirectory = Directory path where all the backup files are stored.

    @NewDataFilesDirectory = Directory path to store the Data files of restored databases.

    @NewLogFilesDirectory = Directory path to store the Log files of restored databases.

    @NewOtherFilesDirectory = Directory path to store the other files (such as Full Text Search files etc.) of restored databases.

    @BackupFileExtension = Extension for backup files without dot (.) such as bak for native SQL Server backup files ( you can specify * for all files)

    @ReplaceExistingDatabases = Setting this to 1 will replace any existing database.

    @IncludeSystemDatabases = Setting this to 1 will generate the restore commands for system database as well

    @AutoExecute = It can be set to 0 OR 1. When set to 0 (default), it will only print the restore commands for backup files where as setting it to 1 will EXECUTE the command automatically along with printing.

    Execution : EXECUTE SPS_RestoreDatabases @BackupFilesDirectory = 'D:\SQLTestLab\Backups',

    @NewDataFilesDirectory = 'D:\SQLTestLab\DatabaseFiles\DataFiles',

    @NewLogFilesDirectory = 'D:\SQLTestLab\DatabaseFiles\LogFiles',

    @NewOtherFilesDirectory = 'D:\SQLTestLab\DatabaseFiles\LogFiles',

    @BackupFileExtension = 'bak',

    @ReplaceExistingDatabases = 0,

    @IncludeSystemDatabases = 0,

    @AutoExecute = 0

    Last modified : 25-April-2014 (Modified for SQL Server 2012 & above.)

    ********************************************************************************************************************/

    IF OBJECT_ID('SPS_RestoreDatabases') IS NOT NULL

    BEGIN

    DROP PROCEDURE SPS_RestoreDatabases;

    END

    GO

    CREATE PROCEDURE [dbo].[SPS_RestoreDatabases]

    (

    @BackupFilesDirectory VARCHAR(500),

    @NewDataFilesDirectory VARCHAR(500),

    @NewLogFilesDirectory VARCHAR(500),

    @NewOtherFilesDirectory VARCHAR(500),

    @BackupFileExtension VARCHAR(20) = 'bak',

    @ReplaceExistingDatabases BIT = 0,

    @IncludeSystemDatabases BIT = 0,

    @AutoExecute BIT = 0

    )

    AS

    SET NOCOUNT ON

    BEGIN

    DECLARE @FileName VARCHAR(1000)

    DECLARE @DataFileName VARCHAR(1000)

    DECLARE @LogFileName VARCHAR(1000)

    DECLARE @FTSFileName VARCHAR(1000)

    DECLARE @DataFileMoveStatements VARCHAR(1000)

    DECLARE @LogFileMoveStatements VARCHAR(1000)

    DECLARE @OtherFileMoveStatements VARCHAR(1000)

    DECLARE @GetFileListCMD VARCHAR(1000)

    DECLARE @DatabaseName VARCHAR(1000)

    DECLARE @FileID INT

    DECLARE @DatabaseRestoreCommand VARCHAR(1000)

    DECLARE @DataFileCounter INT

    DECLARE @LogFileCounter INT

    DECLARE @OtherFileCounter INT

    DECLARE @RestrictDatabaseAccess VARCHAR(1000)

    DECLARE @Ext VARCHAR(11)

    DECLARE @SQLServerVersion VARCHAR(30)

    DECLARE @Major VARCHAR(5)

    DECLARE @Minor VARCHAR(5)

    IF (@BackupFilesDirectory IS NULL)

    BEGIN

    RAISERROR ( 'Provide a valid backup directory path.',16,1)

    END

    ELSE IF (@NewDataFilesDirectory IS NULL)

    BEGIN

    RAISERROR ( 'Provide a valid path for @NewDataFilesDirectory.',16,1)

    END

    ELSE IF (@NewLogFilesDirectory IS NULL)

    BEGIN

    RAISERROR ( 'Provide a valid path for @NewLogFilesDirectory.',16,1)

    END

    ELSE IF (@NewOtherFilesDirectory IS NULL)

    BEGIN

    RAISERROR ( 'Provide a valid path for @NewOtherFilesDirectory.',16,1)

    END

    ELSE IF (@IncludeSystemDatabases = 1 AND @AutoExecute = 1)

    BEGIN

    RAISERROR ( 'AutoExecute cannot be used with system databases. Set @AutoExecute = 0 to print the restore commands for system databases & then restore them manually OR set @IncludeSystemDatabases = 0 to exclude system databases. ',16,1)

    END

    ELSE

    BEGIN

    SET @Major = PARSENAME(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128)),4)

    SET @Minor = PARSENAME(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128)),3)

    IF (@Major = '9' AND @Minor='00')

    BEGIN

    SET @SQLServerVersion = 'SQLServer2005'

    END

    IF (@Major = '10' AND @Minor='00')

    BEGIN

    SET @SQLServerVersion = 'SQLServer2008'

    END

    IF (@Major = '10' AND @Minor='50')

    BEGIN

    SET @SQLServerVersion = 'SQLServer2008R2'

    END

    IF (@Major = '11' AND @Minor='0')

    BEGIN

    SET @SQLServerVersion = 'SQLServer2012'

    END

    --======================= Storing backup file names

    SET @GetFileListCMD = 'DIR '+@BackupFilesDirectory+'\*.'+@BackupFileExtension+' /B /OD'

    IF OBJECT_ID('tempdb..#BackupFiles') IS NOT NULL

    BEGIN

    DROP TABLE #BackupFiles

    END

    CREATE TABLE #BackupFiles

    (

    FileID INT IDENTITY(1,1),

    [FileName] VARCHAR(1000)

    )

    INSERT INTO #BackupFiles ([FileName])

    EXEC MASTER..xp_cmdshell @GetFileListCMD

    DELETE FROM #BackupFiles WHERE [FileName] IS NULL OR [FileName] LIKE '%File Not Found%'

    --======================= Temporary table to store backup header information

    IF OBJECT_ID('tempdb..#RestoreHeaderResults') IS NOT NULL

    BEGIN

    DROP TABLE #RestoreHeaderResults

    END

    CREATE TABLE #RestoreHeaderResults

    (

    BackupName VARCHAR(250),

    BackupDescription VARCHAR(500),

    [BackupType] INT,

    ExpirationDate DATETIME,

    Compressed VARCHAR(2),

    Position INT,

    DeviceType INT,

    UserName VARCHAR(250),

    ServerName VARCHAR(250),

    DatabaseName VARCHAR(250),

    DatabaseVersion VARCHAR(250),

    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 INT,

    [CodePage] Numeric(18,2),

    UnicodeLocaleId INT,

    UnicodeComparisonStyle Numeric(18,2),

    CompatibilityLevel VARCHAR(10),

    SoftwareVendorId VARCHAR(10),

    SoftwareVersionMajor Varchar (250),

    SoftwareVersionMinor Varchar (250),

    SoftwareVersionBuild Varchar (250),

    MachineName Varchar (250),

    Flags Varchar (250),

    BindingID Varchar (250),

    RecoveryForkID Varchar (250),

    Collation Varchar (250),

    FamilyGUID Varchar (250),

    HasBulkLoggedData Varchar (250),

    IsSnapshot Varchar (250),

    IsReadOnly Varchar (250),

    IsSingleUser Varchar (250),

    HasBackupChecksums Varchar (250),

    IsDamaged Varchar (250),

    BeginsLogChain Varchar (250),

    HasIncompleteMetaData Varchar (250),

    IsForceOffline Varchar (250),

    IsCopyOnly Varchar (250),

    FirstRecoveryForkID Varchar (250),

    ForkPointLSN Varchar (250),

    RecoveryModel Varchar (250),

    DifferentialBaseLSN Varchar (250),

    DifferentialBaseGUID Varchar (250),

    BackupTypeDescription Varchar (250),

    BackupSetGUID Varchar (250),

    CompressedBackupSize Numeric(18,2),

    Containment TINYINT

    )

    --======================= Temporary table to store details of database files from backup file

    IF OBJECT_ID('tempdb..#RestoreFileListResults') IS NOT NULL

    BEGIN

    DROP TABLE #RestoreFileListResults

    END

    CREATE TABLE #RestoreFileListResults

    (

    RowID INT IDENTITY(1,1),

    LogicalName VARCHAR(250),

    PhysicalName VARCHAR(500),

    [Type] VARCHAR(2),

    FileGroupName VARCHAR(100),

    Size VARCHAR(100),

    MaxSize VARCHAR(100),

    FileId INT,

    CreateLSN VARCHAR(250),

    DropLSN VARCHAR(250),

    UniqueId VARCHAR(250),

    ReadOnlyLSN VARCHAR(250),

    ReadWriteLSN VARCHAR(250),

    BackupSizeInBytes VARCHAR(250),

    SourceBlockSize INT,

    FileGroupId INT,

    FileGroupGUID VARCHAR(250),

    DifferentialBaseLSN VARCHAR(250),

    DifferentialBaseGUID VARCHAR(250),

    IsReadOnly VARCHAR(2),

    IsPresent VARCHAR(2),

    TDEThumbPrint VARCHAR(250)

    )

    --======================= Getting complete path of backup file

    DECLARE @BackupFileName VARCHAR(100)

    DECLARE @CompleteBackupFilePath VARCHAR(500)

    --======================= Looping through the backup files to generate database restore commands

    DECLARE BackupFilesList CURSOR FAST_FORWARD FOR SELECT [FileName] FROM #BackupFiles ORDER BY [FileName],[FileID]

    OPEN BackupFilesList

    FETCH NEXT FROM BackupFilesList INTO @BackupFileName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @CompleteBackupFilePath = @BackupFilesDirectory + '\' + @BackupFileName

    --======================= Storing backup header information in temporary table

    TRUNCATE TABLE #RestoreHeaderResults;

    --===== Version : SQL Server 2005

    IF (@SQLServerVersion = 'SQLServer2005')

    BEGIN

    INSERT INTO #RestoreHeaderResults ( 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

    )

    EXEC('RESTORE HEADERONLY FROM DISK = '+''''+@CompleteBackupFilePath+'''')

    END

    --===== Version : SQL Server 2008 & 2008 R2

    IF (@SQLServerVersion = 'SQLServer2008' OR @SQLServerVersion = 'SQLServer2008R2')

    BEGIN

    INSERT INTO #RestoreHeaderResults ( 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

    )

    EXEC('RESTORE HEADERONLY FROM DISK = '+''''+@CompleteBackupFilePath+'''')

    END

    --===== Version : SQL Server 2012

    IF (@SQLServerVersion = 'SQLServer2012')

    BEGIN

    INSERT INTO #RestoreHeaderResults ( 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,

    Containment)

    EXEC('RESTORE HEADERONLY FROM DISK = '+''''+@CompleteBackupFilePath+'''')

    END

    --======================= Storing internal details of data & log files on the backup file

    TRUNCATE TABLE #RestoreFileListResults;

    IF (@SQLServerVersion = 'SQLServer2005')

    BEGIN

    INSERT INTO #RestoreFileListResults (

    LogicalName,

    PhysicalName,

    [Type],

    FileGroupName,

    Size,

    MaxSize,

    FileId,

    CreateLSN,

    DropLSN,

    UniqueId,

    ReadOnlyLSN,

    ReadWriteLSN,

    BackupSizeInBytes,

    SourceBlockSize,

    FileGroupId,

    FileGroupGUID,

    DifferentialBaseLSN,

    DifferentialBaseGUID,

    IsReadOnly,

    IsPresent

    )

    EXEC('RESTORE FILELISTONLY FROM DISK = '+''''+@CompleteBackupFilePath+'''')

    END

    ELSE IF (@SQLServerVersion = 'SQLServer2008' OR @SQLServerVersion = 'SQLServer2008R2' OR @SQLServerVersion='SQLServer2012')

    BEGIN

    INSERT INTO #RestoreFileListResults (

    LogicalName,

    PhysicalName,

    [Type],

    FileGroupName,

    Size,

    MaxSize,

    FileId,

    CreateLSN,

    DropLSN,

    UniqueId,

    ReadOnlyLSN,

    ReadWriteLSN,

    BackupSizeInBytes,

    SourceBlockSize,

    FileGroupId,

    FileGroupGUID,

    DifferentialBaseLSN,

    DifferentialBaseGUID,

    IsReadOnly,

    IsPresent,

    TDEThumbPrint)

    EXEC('RESTORE FILELISTONLY FROM DISK = '+''''+@CompleteBackupFilePath+'''')

    END

    --======================= Getting the name of the new database to be restored

    SELECT @DatabaseName = DatabaseName FROM #RestoreHeaderResults

    SET @DataFileMoveStatements = ''

    SET @LogFileMoveStatements = ''

    SET @OtherFileMoveStatements = ''

    --======================= Generating statments to move data files

    SELECT @DataFileMoveStatements = @DataFileMoveStatements + 'MOVE'+''''+LogicalName+''''+' TO '+''''+@NewDataFilesDirectory+'\'+ RIGHT(RFLR.PhysicalName, CHARINDEX('\', REVERSE(RFLR.PhysicalName))-1) +''','+CHAR(13)

    FROM #RestoreFileListResults RFLR

    WHERE RFLR.[Type] = 'D'

    ORDER BY RFLR.FileId

    --======================= Generating statments to move log files

    SELECT @LogFileMoveStatements = @LogFileMoveStatements+'MOVE '+''''+LogicalName+''''+' TO '+''''+@NewLogFilesDirectory+'\'+ RIGHT(RFLR.PhysicalName, CHARINDEX('\', REVERSE(RFLR.PhysicalName))-1) +''','+CHAR(13)

    FROM #RestoreFileListResults RFLR

    WHERE RFLR.[Type] = 'L'

    ORDER BY RFLR.FileId

    --======================= Generating statments to move other files such as FileStream or FullTextCatalog files

    SELECT @OtherFileMoveStatements = @OtherFileMoveStatements+'MOVE '+''''+LogicalName+''''+' TO '+''''+@NewOtherFilesDirectory+'\'+ RIGHT(RFLR.PhysicalName, CHARINDEX('\', REVERSE(RFLR.PhysicalName))-1) +''','+CHAR(13)

    FROM #RestoreFileListResults RFLR

    WHERE RFLR.[Type] NOT IN ('D','L')

    ORDER BY RFLR.FileId

    --======================= Preparing database restore commands

    IF (@ReplaceExistingDatabases = 1)

    BEGIN

    SET @RestrictDatabaseAccess = ''

    IF DB_ID(@DatabaseName) IS NOT NULL

    BEGIN

    SET @RestrictDatabaseAccess = 'ALTER DATABASE ['+ @DatabaseName + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;'

    END

    SET @DatabaseRestoreCommand = 'RESTORE DATABASE ['+@DatabaseName+'] FROM DISK ='+''''+ @CompleteBackupFilePath+''''+CHAR(13)+'WITH '+CHAR(13)+@DataFileMoveStatements + @LogFileMoveStatements + @OtherFileMoveStatements + 'STATS = 1,REPLACE;'+CHAR(13)+''

    SET @DatabaseRestoreCommand = @RestrictDatabaseAccess + CHAR(13) + CHAR(13) + @DatabaseRestoreCommand

    END

    ELSE

    BEGIN

    SET @DatabaseRestoreCommand = 'RESTORE DATABASE ['+@DatabaseName+'] FROM DISK ='+''''+ @CompleteBackupFilePath+''''+CHAR(13)+'WITH '+CHAR(13)+@DataFileMoveStatements + @LogFileMoveStatements + @OtherFileMoveStatements + 'STATS = 1;'+CHAR(13)+''

    END

    --======================= Preparing & printing database restore commands

    IF (@AutoExecute = 0 AND @IncludeSystemDatabases = 1)

    BEGIN

    PRINT CHAR(13) + '--' + REPLICATE('=',40) + ' Restore Command For Database : ['+@DatabaseName+']' + CHAR(13)

    PRINT @DatabaseRestoreCommand

    PRINT CHAR(13) + '--' + REPLICATE('=',130)

    END

    IF (@AutoExecute = 0 AND @IncludeSystemDatabases = 0)

    BEGIN

    IF (UPPER(@DatabaseName) NOT IN ('MASTER','MSDB','MODEL','TEMPDB','DISTRIBUTION') AND NOT EXISTS (SELECT * FROM sys.databases WHERE [name] = @DatabaseName AND is_distributor = 1))

    BEGIN

    PRINT CHAR(13) + '--' + REPLICATE('=',40) + ' Restore Command For Database : ['+@DatabaseName+']' + CHAR(13)

    PRINT @DatabaseRestoreCommand

    PRINT CHAR(13) + '--' + REPLICATE('=',130)

    END

    END

    IF (@AutoExecute = 1 AND @IncludeSystemDatabases = 0)

    BEGIN

    IF (UPPER(@DatabaseName) NOT IN ('MASTER','MSDB','MODEL','TEMPDB','DISTRIBUTION') AND NOT EXISTS (SELECT * FROM sys.databases WHERE [name] = @DatabaseName AND is_distributor = 1))

    BEGIN

    PRINT CHAR(13) + '--' + REPLICATE('=',40) + ' Executing Restore Command For Database : ['+@DatabaseName+']' + CHAR(13)

    PRINT @DatabaseRestoreCommand

    EXECUTE (@DatabaseRestoreCommand)

    PRINT CHAR(13) + '--' + REPLICATE('=',130)

    END

    END

    FETCH NEXT FROM BackupFilesList INTO @BackupFileName

    END

    CLOSE BackupFilesList

    DEALLOCATE BackupFilesList

    END

    END


    Sujeet Singh

  • safazam

    Valued Member

    Points: 65

    Thank you for your reply. I was using sql2008 and sql 2012

    This is from 2008

    Now it says command completed successfully ,but no command printed, I don't see those tables in tempdb temporary tables either after creating SP.

    EXECUTE SPS_RestoreDatabases

    @BackupFilesDirectory = 'C:\Backup',

    @NewDataFilesDirectory = 'C:\Program Files\Microsoft SQL Server\MSSQL10.TFB\MSSQL\DATA',

    @NewLogFilesDirectory = 'C:\Program Files\Microsoft SQL Server\MSSQL10.TFB\MSSQL\DATA',

    @NewOtherFilesDirectory = 'C:\Program Files\Microsoft SQL Server\MSSQL10.TFB\MSSQL\DATA',

    @BackupFileExtension = 'bak',

    @ReplaceExistingDatabases = 1,

    @IncludeSystemDatabases = 0,

    @AutoExecute = 0

    no result,messages

    Command(s) completed successfully.

  • Divine Flame

    SSCoach

    Points: 15941

    safazam (4/24/2014)


    Thank you for your reply. I was using sql2008 and sql 2012

    This is from 2008

    Now it says command completed successfully ,but no command printed, I don't see those tables in tempdb temporary tables either after creating SP.

    EXECUTE SPS_RestoreDatabases

    @BackupFilesDirectory = 'C:\Backup',

    @NewDataFilesDirectory = 'C:\Program Files\Microsoft SQL Server\MSSQL10.TFB\MSSQL\DATA',

    @NewLogFilesDirectory = 'C:\Program Files\Microsoft SQL Server\MSSQL10.TFB\MSSQL\DATA',

    @NewOtherFilesDirectory = 'C:\Program Files\Microsoft SQL Server\MSSQL10.TFB\MSSQL\DATA',

    @BackupFileExtension = 'bak',

    @ReplaceExistingDatabases = 1,

    @IncludeSystemDatabases = 0,

    @AutoExecute = 0

    no result,messages

    Command(s) completed successfully.

    Hi Safazam,

    I had tested the procedure on SQL Server 2005, 2008 R2 & 2012. However, I couldn't test it on SQL Server 2008 as I didn't have it.

    Could you run following command & let me know the output:

    SELECT PARSENAME(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128)),4) AS Major , PARSENAME(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128)),3) AS Minor

    If you go through the procedure you will get a section like below. Change the values of @Major & @Minor variables with the values you get by running the command given above :

    IF (@Major = '10' AND @Minor='00')

    BEGIN

    SET @SQLServerVersion = 'SQLServer2008'

    END


    Sujeet Singh

  • safazam

    Valued Member

    Points: 65

    Major Minor

    10 0

    I have changed it to '0' and it printed the command.

    Now I am trying to add diff and log files for restore. I will keep you posted if anything.

    Thank you for your help.

  • steveyeadon

    SSC Rookie

    Points: 28

    Have you never heard of powershell...

  • Divine Flame

    SSCoach

    Points: 15941

    steveyeadon (5/7/2014)


    Have you never heard of powershell...

    You must have felt better after saying that. I am glad for you :cool:.


    Sujeet Singh

  • tatlatme

    Newbie

    Points: 7

    I know I am about two years late to the party but this script is great. We are migrating 500 databases to a new instance and this saved me so much time.

  • Divine Flame

    SSCoach

    Points: 15941

    tatlatme (9/23/2016)


    I know I am about two years late to the party but this script is great. We are migrating 500 databases to a new instance and this saved me so much time.

    Thanks Tatlatme.


    Sujeet Singh

Viewing 13 posts - 16 through 28 (of 28 total)

You must be logged in to reply to this topic. Login to reply