Multiple database batch restore script needed

• johnnyrmtl

  Hall of Fame

  Points: 3505

  More actions

  February 6, 2015 at 2:08 pm

  #1776174

  TheSQLGuru (2/6/2015)

  ---

  Try this at your own risk. I have used it in the past, but pretty sure it was for single-file/filegroup databases and they didn't have filestream.

  drop table #files

  drop table #dbfiles

  drop table #bdev

  go

  declare @SourceDirBackupFiles nvarchar(200), @DestDirDbFiles nvarchar(200), @destdirlogfiles nvarchar(200)

  select @SourceDirBackupFiles = 'full_path_to_backup_files_here', @DestDirDbFiles = 'full_path_to_data_file_location_here', @destdirlogfiles = 'full_path_to_log_file_location_here'

  SET NOCOUNT ON

  --Table to hold each backup file name in

  CREATE TABLE #files(fname varchar(200),depth int, file_ int)

  INSERT #files

  EXECUTE master.dbo.xp_dirtree @SourceDirBackupFiles, 1, 1

  --Table to hold the result from RESTORE HEADERONLY. Needed to get the database name out from

  CREATE TABLE #bdev

  (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) NULL,

  RecoveryModel nvarchar(60),

  DifferentialBaseLSN numeric(25,0) NULL,

  DifferentialBaseGUID uniqueidentifier,

  BackupTypeDescription nvarchar(60),

  BackupSetGUID uniqueidentifier NULL,

  CompressedBackupSize bigint,

  containment bit)

  --Table to hold result from RESTORE FILELISTONLY. Need to generate the MOVE options to the RESTORE command

  CREATE TABLE #dbfiles(

  LogicalName VARCHAR(500), PhysicalName VARCHAR(500), TYPE VARCHAR(1),

  FileGroupName VARCHAR(500), SIZE VARCHAR(100), MaxSize VARCHAR(100),

  FileID BIGINT, CreateLSN NUMERIC(25,0), DropLSN NUMERIC(25,0),

  UniqueID UNIQUEIDENTIFIER, ReadOnlyLSN NUMERIC(25,0),

  ReadWriteLSN NUMERIC(25,0), BackupSizeInBytes BIGINT, SourceBlockSize INT,

  FileGroupID INT, LogGroupGUID UNIQUEIDENTIFIER, DifferentialBaseLSN NUMERIC(25,0),

  DifferentialBaseGUID UNIQUEIDENTIFIER, IsReadOnly BIT, IsPresent BIT

  ,TDEThumbprint VARBINARY(32) NULL)

  DECLARE @fname varchar(200)

  DECLARE @dirfile varchar(300)

  DECLARE @LogicalName nvarchar(128)

  DECLARE @PhysicalName nvarchar(260)

  DECLARE @type char(1)

  DECLARE @DbName sysname

  DECLARE @sql nvarchar(1000)

  DECLARE files CURSOR FOR

  SELECT fname FROM #files

  DECLARE dbfiles CURSOR FOR

  SELECT LogicalName, PhysicalName, Type FROM #dbfiles

  OPEN files

  FETCH NEXT FROM files INTO @fname

  WHILE @@FETCH_STATUS = 0

  BEGIN

  SET @dirfile = @SourceDirBackupFiles + @fname

  --Get database name from RESTORE HEADERONLY, assumes there's only one backup on each backup file.

  TRUNCATE TABLE #bdev

  INSERT #bdev

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

  SET @DbName = (SELECT DatabaseName FROM #bdev)

  --Construct the beginning for the RESTORE DATABASE command

  SET @sql = 'RESTORE DATABASE ' + @DbName + ' FROM DISK = ''' + @dirfile + '''' + char(10) + ' WITH MOVE '

  --Get information about database files from backup device into temp table

  TRUNCATE TABLE #dbfiles

  INSERT #dbfiles

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

  OPEN dbfiles

  FETCH NEXT FROM dbfiles INTO @LogicalName, @PhysicalName, @type

  --For each database file that the database uses

  WHILE @@FETCH_STATUS = 0

  BEGIN

  SET @sql = @sql + '''' + @LogicalName + ''' TO ''' + CASE WHEN @type = 'D' then @destdirdbfiles WHEN @type = 'L' then @destdirlogfiles else 'ERROROROROROROR' end + '\' + @LogicalName

  IF @type = 'D'

  SET @sql = @sql + '.mdf'', ' + char(10) + + 'MOVE '

  ELSE IF @type = 'L'

  SET @sql = @sql + '.ldf'''

  FETCH NEXT FROM dbfiles INTO @LogicalName, @PhysicalName, @type

  END

  --Here's the actual RESTORE command

  PRINT @sql

  print 'GO'

  --Remove the comment below if you want the procedure to actually execute the restore command.

  --EXEC(@sql)

  CLOSE dbfiles

  FETCH NEXT FROM files INTO @fname

  END

  CLOSE files

  DEALLOCATE dbfiles

  DEALLOCATE files

  This script works well as it generates the restore script