Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Script Library
 Create Restore Command from multiple DB Backup Fil

Author  Topic 

Lumbago
Norsk Yak Master

3271 Posts
Posted - 2008-04-30 : 08:02:29
This script was originally posted by Michael Valentine Jones in this thread -> http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=71665. I simply added the functionality to script the restore command for all backup-files in a given folder + the posibility to alter the location of the data/log-files.
/*
Create Restore Command from multiple DB Backup Files
Originally created by Michael Valentine Jones, SQLTeam.com
- Modified by Henning Frettem
*/

SET NOCOUNT ON
GO

DECLARE 
  @backup_path nvarchar(500),
  @totCount int,
  @Current int,
  @dirCmd varchar(200),
  @Filename varchar(200),
  @tab varchar(1), 
  @cr varchar(2),
  @new_data_path varchar(500),
  @new_log_path varchar(500)

SELECT @backup_path =   'D:\Backups\'           --> Folder where full database backupfiles are located
SELECT @new_data_path = 'D:\new_data_path\'     --> Optional, leave blank to use the default from the backup file
SELECT @new_log_path =  'D:\new_log_path\'      --> Optional, leave blank to use the default from the backup file

CREATE TABLE #BackupFiles ( 
  ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
  [Filename] VARCHAR(500)
)

SET @dirCmd = 'dir /b "' + @backup_path + '" '

INSERT INTO #BackupFiles ([Filename])
EXEC xp_cmdshell @dirCmd

--> Delete excess row in #BackupFiles
DELETE #BackupFiles WHERE [Filename] IS NULL

CREATE TABLE #header (
  BackupName        nvarchar(128) NULL,
  BackupDescription nvarchar(128) NULL,
  BackupType        int           NOT NULL,
  ExpirationDate    datetime      NULL,
  Compressed        int           NOT NULL,
  Position          int           NOT NULL,
  DeviceType        int           NOT NULL,
  UserName          nvarchar(128) NOT NULL,
  ServerName        nvarchar(128) NOT NULL,
  DatabaseName      nvarchar(128) NOT NULL,
  DatabaseVersion   int           NOT NULL,
  DatabaseCreationDate  datetime  NOT NULL,
  BackupSize        decimal(28,0) NOT NULL,
  FirstLsn          decimal(28,0) NOT NULL,
  LastLsn           decimal(28,0) NOT NULL,
  CheckpointLsn     decimal(28,0) NOT NULL,
  DatabaseBackupLsn decimal(28,0) NOT NULL,
  BackupStartDate   datetime      NOT NULL,
  BackupFinishDate  datetime      NOT NULL,
  SortOrder         int           NOT NULL,
  CodePage          int           NOT NULL,
  UnicodeLocaleId   int           NOT NULL,
  UnicodeComparisonStyle  int     NOT NULL,
  CompatibilityLevel      int     NOT NULL,
  SoftwareVendorId        int     NULL,
  SoftwareVersionMajor    int     NULL,
  SoftwareVersionMinor    int     NULL,
  SoftwareVersionBuild    int     NULL,
  MachineName       nvarchar(128) NOT NULL,
  Flags             int           NULL,
  BindingID         uniqueidentifier NULL,
  RecoveryForkID    uniqueidentifier NULL,
  Collation         nvarchar(128) NULL,
  Seq               int           NOT NULL identity(1,1),
)

CREATE TABLE #filelist (
  LogicalName   nvarchar(128) NOT NULL,
  PhysicalName  nvarchar(128) NOT NULL,
  Type          nvarchar(10)  NOT NULL,
  FileGroupName nvarchar(128) NULL,
  Size          decimal(28,0) NOT NULL,
  MAXSize       decimal(28,0) NOT NULL,
  Seq           int           NOT NULL identity(1,1),
)


SET @totCount = (SELECT COUNT(*) FROM #BackupFiles)
SET @Current = 1
SELECT @tab = char(9), @cr = char(13)+Char(10)

--> Loop through the files that hasn't been restored before and restore them one by one
WHILE (@Current <= @totCount AND @totCount > 0)
  BEGIN
    SELECT @Filename = [Filename] FROM #BackupFiles WHERE ID = @Current

    INSERT INTO #header
    EXEC ('RESTORE HeaderOnly FROM DISK = ''' + @backup_path + @Filename + '''')

    INSERT INTO #filelist
    EXEC ('RESTORE FilelistOnly FROM DISK = ''' + @backup_path + @Filename + '''')

    --> Replace the old data file path with a new one if specified
    IF @new_data_path <> ''
      UPDATE #filelist 
        SET PhysicalName = @new_data_path + right(physicalname, CHARINDEX('\',REVERSE(physicalname))-1)
      WHERE Type = 'D'

    --> Replace the old log file path with a new one if specified
    IF @new_data_path <> ''
      UPDATE #filelist 
        SET PhysicalName = @new_log_path + right(physicalname, CHARINDEX('\',REVERSE(physicalname))-1)
      WHERE Type = 'L'

    --> Print restore script
    SELECT
      [--Restore--] = CASE
        WHEN a.Seq = 1 THEN @cr +
          @cr + 'RESTORE DATABASE ' + c.DatabaseName +
          @cr + 'FROM DISK =' + @cr + @tab + '''' +
          @backup_path + @Filename + '''' + @cr + 'WITH' + @cr
        ELSE ''
        END 
        +
        @tab + 'MOVE ''' + a.LogicalName + ''' TO ''' + a.PhysicalName+''' ,'
        +
        CASE WHEN a.Seq = b.Seq THEN
          @cr + @tab + 'REPLACE, STATS = 5 , NORECOVERY'
        ELSE ''
        END
    FROM
      #filelist a
      CROSS JOIN
      (SELECT Seq = MAX(b1.Seq) FROM #filelist b1 ) b
      CROSS JOIN
      (SELECT DatabaseName = MAX(c1.DatabaseName) 
        FROM #header c1) c
    ORDER BY
      a.Seq

    SET @Current = @Current + 1

    TRUNCATE TABLE #header
    TRUNCATE TABLE #filelist
  END

DROP TABLE #header
DROP TABLE #filelist
DROP TABLE #BackupFiles


--
Lumbago

JohnnyG
Starting Member

1 Post
Posted - 2008-08-08 : 09:41:32
Would it be possible to update the script to do a recursive loop thru the backup directory to get any additional backup files in subdirectories underneath the original backup path?
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts
Posted - 2009-03-13 : 10:33:08
Wow...just noticed the reply to this thread now...6 months late [:S]

But yes, this is actually *really* simple!! Just add a /s to the dir command:

SET @dirCmd = 'dir /b /s "' + @backup_path + '" '



- Lumbago
Go to Top of Page

cshah1
Constraint Violating Yak Guru

347 Posts
Posted - 2009-04-10 : 15:50:03
Lumbago,
in SQL 2005 when I run your script getting this error

Msg 213, Level 16, State 7, Line 1
Insert Error: Column name or number of supplied values does not match table definition


???
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts
Posted - 2009-04-14 : 03:07:59
Script for 2005:
/*
Create Restore Command from multiple DB Backup Files
Originally created by Michael Valentine Jones, SQLTeam.com
- Modified by Henning Frettem
REMEMBER TO INCREASE MAX CHARACTER OUTPUT FOR EACH COLUMN BEFORE RUNNING THIS SCRIPT!!
*/
SET NOCOUNT ON
GO

DECLARE
  @backup_path varchar(500),
  @totCount int,
  @Current int,
  @dirCmd varchar(200),
  @Filename varchar(200),
  @tab varchar(1),
  @cr varchar(2),
  @new_data_path varchar(500),
  @new_log_path varchar(500)

SELECT @backup_path = 'D:\Backups\' --> Folder where full database backupfiles are located
SELECT @new_data_path = 'D:\new_data_file_path\' --> Optional, leave blank to use the default from the backup file
SELECT @new_log_path = 'D:\new_log_file_path\' --> Optional, leave blank to use the default from the backup file

CREATE TABLE #BackupFiles (
  ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
  [Filename] VARCHAR(500)
)

SET @dirCmd = 'dir /b "' + @backup_path + 'Full*.bak" '

INSERT INTO #BackupFiles ([Filename])
EXEC xp_cmdshell @dirCmd

--> Delete excess row in #BackupFiles
DELETE #BackupFiles WHERE [Filename] IS NULL OR [Filename] NOT LIKE '%.bak'

CREATE TABLE #header (
  BackupName varchar(128) NULL,
  BackupDescription varchar(255) NULL,
  BackupType smallint NULL,
  ExpirationDate datetime NULL,
  Compressed tinyint NULL,
  Position smallint NULL,
  DeviceType tinyint NULL,
  UserName varchar(128) NULL,
  ServerName varchar(128) NULL,
  DatabaseName varchar(128) NULL,
  DatabaseVersion int NULL,
  DatabaseCreationDate datetime NULL,
  BackupSize numeric(20,0) NULL,
  FirstLSN numeric(25,0) NULL,
  LastLSN numeric(25,0) NULL,
  CheckpointLSN numeric(25,0) NULL,
  DatabaseBackupLSN numeric(25,0) NULL,
  BackupStartDate datetime NULL,
  BackupFinishDate datetime NULL,
  SortOrder smallint NULL,
  CodePage smallint NULL,
  UnicodeLocaleId int NULL,
  UnicodeComparisonStyle int NULL,
  CompatibilityLevel tinyint NULL,
  SoftwareVendorId int NULL,
  SoftwareVersionMajor int NULL,
  SoftwareVersionMinor int NULL,
  SoftwareVersionBuild int NULL,
  MachineName varchar(128) NULL,
  Flags int NULL,
  BindingID uniqueidentifier NULL,
  RecoveryForkID uniqueidentifier NULL,
  Collation varchar(128) NULL,
  FamilyGUID uniqueidentifier NULL,
  HasBulkLoggedData bit NULL,
  IsSnapshot bit NULL,
  IsReadOnly bit NULL,
  IsSingleUser bit NULL,
  HasBackupChecksums bit NULL,
  IsDamaged bit NULL,
  BeginsLogChain bit NULL,
  HasIncompleteMetaData bit NULL,
  IsForceOffline bit NULL,
  IsCopyOnly bit NULL,
  FirstRecoveryForkID uniqueidentifier NULL,
  ForkPointLSN numeric(25,0) NULL,
  RecoveryModel varchar(60) NULL,
  DifferentialBaseLSN numeric(25,0) NULL,
  DifferentialBaseGUID uniqueidentifier NULL,
  BackupTypeDescription varchar(60) NULL,
  BackupSetGUID uniqueidentifier NULL,
  Seq int NOT NULL identity(1,1)
)

CREATE TABLE #filelist (
  LogicalName varchar(128) NULL,
  PhysicalName varchar(260) NULL,
  Type char(1) NULL,
  FileGroupName varchar(128) NULL,
  Size numeric(20,0) NULL,
  MaxSize numeric(20,0) NULL,
  FileID bigint NULL,
  CreateLSN numeric(25,0) NULL,
  DropLSN numeric(25,0) NULL,
  UniqueID uniqueidentifier NULL,
  ReadOnlyLSN numeric(25,0) NULL,
  ReadWriteLSN numeric(25,0) NULL,
  BackupSizeInBytes bigint NULL,
  SourceBlockSize int NULL,
  FileGroupID int NULL,
  LogGroupGUID uniqueidentifier NULL,
  DifferentialBaseLSN numeric(25,0) NULL,
  DifferentialBaseGUID uniqueidentifier NULL,
  IsReadOnly bit NULL,
  IsPresent bit NULL,
  Seq int NOT NULL identity(1,1)
)

SET @totCount = (SELECT COUNT(*) FROM #BackupFiles)
SET @Current = 1

SELECT @tab = char(9), @cr = char(13)+Char(10)

--> Loop through the files that hasn't been restored before and restore them one by one
WHILE (@Current <= @totCount AND @totCount > 0)
  BEGIN
    SELECT @Filename = [Filename] FROM #BackupFiles WHERE ID = @Current
    INSERT INTO #header
    EXEC ('RESTORE HeaderOnly FROM DISK = ''' + @backup_path + @Filename + '''')

    INSERT INTO #filelist
    EXEC ('RESTORE FilelistOnly FROM DISK = ''' + @backup_path + @Filename + '''')

    --> Replace the old data file path with a new one if specified
    IF @new_data_path <> ''
      UPDATE #filelist
      SET PhysicalName = @new_data_path + right(physicalname,
      CHARINDEX('\',REVERSE(physicalname))-1)
      WHERE Type = 'D'

    --> Replace the old log file path with a new one if specified
    IF @new_data_path <> ''
      UPDATE #filelist
      SET PhysicalName = @new_log_path + right(physicalname,
      CHARINDEX('\',REVERSE(physicalname))-1)
      WHERE Type = 'L'

    --> Print restore script
    SELECT
      [--Restore--] = CASE
        WHEN a.Seq = 1 THEN @cr +
          @cr + 'RESTORE DATABASE [' + c.DatabaseName + ']' +
          @cr + 'FROM DISK =' + @cr + @tab + '''' +
          @backup_path + @Filename + '''' + @cr + 'WITH' + @cr
        ELSE ''
        END
        +
        @tab + 'MOVE ''' + a.LogicalName + ''' TO ''' + a.PhysicalName + ''' ,'
        +
        CASE WHEN a.Seq = b.Seq THEN
          @cr + @tab + 'REPLACE, STATS = 5 , --NORECOVERY'
        ELSE ''
        END
    FROM
      #filelist a
      CROSS JOIN
        (SELECT Seq = MAX(b1.Seq) FROM #filelist b1 ) b
      CROSS JOIN
        (SELECT DatabaseName = MAX(c1.DatabaseName) FROM #header c1) c
    ORDER BY
      a.Seq

    SET @Current = @Current + 1

    TRUNCATE TABLE #header
    TRUNCATE TABLE #filelist
END

DROP TABLE #header
DROP TABLE #filelist
DROP TABLE #BackupFiles


- Lumbago
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts
Posted - 2011-01-06 : 04:22:41
2008-version:
/*
Create Restore Command from multiple DB Backup Files
Originally created by Michael Valentine Jones, SQLTeam.com
- Modified by Henning Frettem
REMEMBER TO INCREASE MAX CHARACTER OUTPUT FOR EACH COLUMN BEFORE RUNNING THIS SCRIPT!!
*/
SET NOCOUNT ON
GO

DECLARE
  @backup_path varchar(500),
  @totCount int,
  @Current int,
  @dirCmd varchar(200),
  @Filename varchar(200),
  @DatabaseName varchar(200),
  @CreateDBFolder bit,
  @tab varchar(1),
  @cr varchar(2),
  @new_data_path varchar(500),
  @new_data_path2 varchar(500),
  @new_log_path varchar(500),
  @new_log_path2 varchar(500)

SELECT @backup_path = 'e:\full_Backups\' --> Folder where full database backupfiles are located
SELECT @new_data_path = 'E:\MSSQL2005\Data\' --> Optional, leave blank to use the default from the backup file
SELECT @new_log_path = 'F:\MSSQL2005\Logs\' --> Optional, leave blank to use the default from the backup file
SELECT @CreateDBFolder = 1  --> If 1 is specidied the data/log files will be placed in a separate folder named after the database

CREATE TABLE #BackupFiles (
  ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
  [Filename] VARCHAR(500)
)

SET @dirCmd = 'dir /b "' + @backup_path + '*full.bak" '

INSERT INTO #BackupFiles ([Filename])
EXEC xp_cmdshell @dirCmd

--> Delete excess row in #BackupFiles
DELETE #BackupFiles WHERE [Filename] IS NULL OR [Filename] NOT LIKE '%.bak'

CREATE TABLE #header (
  BackupName nvarchar(128),
  BackupDescription nvarchar(255),
  BackupType smallint,
  ExpirationDate datetime,
  Compressed bit,
  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) NULL,
  RecoveryModel nvarchar(60),
  DifferentialBaseLSN numeric(25,0) NULL,
  DifferentialBaseGUID uniqueidentifier,
  BackupTypeDescription nvarchar(60),
  BackupSetGUID uniqueidentifier NULL,
  CompressedBackupSize bigint,
  Seq int NOT NULL identity(1,1)
  )
  
CREATE TABLE #filelist (
  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,
  FileGroupID int,
  LogGroupGUID uniqueidentifier NULL,
  DifferentialBaseLSN numeric(25,0) NULL,
  DifferentialBaseGUID uniqueidentifier,
  IsReadOnly bit,
  IsPresent bit,
  TDEThumbprint varbinary(32),
  Seq int NOT NULL identity(1,1)
  )

SET @totCount = (SELECT COUNT(*) FROM #BackupFiles)
SET @Current = 1

SELECT @tab = char(9), @cr = char(13)+Char(10)

--> Loop through the files that hasn't been restored before and restore them one by one
WHILE (@Current <= @totCount AND @totCount > 0)
  BEGIN
    SELECT @Filename = [Filename] FROM #BackupFiles WHERE ID = @Current
    INSERT INTO #header
    EXEC ('RESTORE HeaderOnly FROM DISK = ''' + @backup_path + @Filename + '''')

    INSERT INTO #filelist
    EXEC ('RESTORE FilelistOnly FROM DISK = ''' + @backup_path + @Filename + '''')

    SELECT 
      @DatabaseName = Databasename,
      @new_data_path2 = CASE 
        WHEN @CreateDBFolder = 1 THEN @new_data_path + DatabaseName + '\' 
        ELSE @new_data_path
        END,
      @new_log_path2 = CASE 
        WHEN @CreateDBFolder = 1 THEN @new_log_path + DatabaseName + '\' 
        ELSE @new_log_path
        END
    FROM #header

    --> Replace the old data file path with a new one if specified
    IF @new_data_path2 <> ''
      UPDATE #filelist
      SET PhysicalName = @new_data_path2 + right(physicalname,
      CHARINDEX('\',REVERSE(physicalname))-1)
      WHERE Type = 'D'

    --> Replace the old log file path with a new one if specified
    IF @new_log_path2 <> ''
      UPDATE #filelist
      SET PhysicalName = @new_log_path2 + right(physicalname,
      CHARINDEX('\',REVERSE(physicalname))-1)
      WHERE Type = 'L'

    --> Print restore script
    SELECT
      [--Restore--] = 
        CASE 
          WHEN a.Seq = 1 AND @CreateDBFolder = 1 THEN 
            @cr + 'EXEC xp_cmdshell ''md ' + @new_data_path2 + '''' +
            @cr + 'EXEC xp_cmdshell ''md ' + @new_log_path2 + ''''
          ELSE ''
        END
        +
        CASE
          WHEN a.Seq = 1 THEN @cr +
            @cr + 'RESTORE DATABASE [' + c.DatabaseName + ']' +
            @cr + 'FROM DISK =' + @cr + @tab + '''' +
            @backup_path + @Filename + '''' + @cr + 'WITH' + @cr
          ELSE ''
        END
        +
        @tab + 'MOVE ''' + a.LogicalName + ''' TO ''' + a.PhysicalName + ''' ,'
        +
        CASE 
          WHEN a.Seq = b.Seq THEN
            @cr + @tab + 'REPLACE, STATS = 5, --NORECOVERY'
          ELSE ''
        END
    FROM
      #filelist a
      CROSS JOIN
        (SELECT Seq = MAX(b1.Seq) FROM #filelist b1 ) b
      CROSS JOIN
        (SELECT DatabaseName = MAX(c1.DatabaseName) FROM #header c1) c
    ORDER BY
      a.Seq

    SET @Current = @Current + 1

    TRUNCATE TABLE #header
    TRUNCATE TABLE #filelist
END

DROP TABLE #header
DROP TABLE #filelist
DROP TABLE #BackupFiles


- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site