Generate a Restore Statement Based upon the Backup File.

• satnam.singh 44358

  Ten Centuries

  Points: 1259

  More actions

  April 14, 2020 at 4:47 am

  #3742609

  Team,

  Can somebody please help with a Logic to Generate a Restore Statement based upon the Backup File. I have written the below logic which generates the Restore Statement for a Database with 1 Data and Log File. The Input Parameters would be Database Name and Backup File. I want a help to modify the same for Database with Multiple Data Files. Any Help would be Highly Appreciated.

  CREATE PROCEDURE CREATESQLRESTORESTATEMENT

  @dbname varchar(8000),

  @backupfolder varchar(8000)

  AS

  BEGIN

  SET NOCOUNT ON

  CREATE TABLE #headers

  ( BackupName varchar(256), BackupDescription varchar(256), BackupType varchar(256),

  ExpirationDate varchar(256), Compressed varchar(256), Position varchar(256), DeviceType varchar(256),

  UserName varchar(256), ServerName varchar(256), DatabaseName varchar(256), DatabaseVersion varchar(256),

  DatabaseCreationDate varchar(256), BackupSize varchar(256), FirstLSN varchar(256), LastLSN varchar(256),

  CheckpointLSN varchar(256), DatabaseBackupLSN varchar(256), BackupStartDate varchar(256), BackupFinishDate varchar(256),

  SortOrder varchar(256), CodePage varchar(256), UnicodeLocaleId varchar(256), UnicodeComparisonStyle varchar(256),

  CompatibilityLevel varchar(256), SoftwareVendorId varchar(256), SoftwareVersionMajor varchar(256),

  SoftwareVersionMinor varchar(256), SoftwareVersionBuild varchar(256), MachineName varchar(256), Flags varchar(256),

  BindingID varchar(256), RecoveryForkID varchar(256), Collation varchar(256), FamilyGUID varchar(256),

  HasBulkLoggedData varchar(256), IsSnapshot varchar(256), IsReadOnly varchar(256), IsSingleUser varchar(256),

  HasBackupChecksums varchar(256), IsDamaged varchar(256), BeginsLogChain varchar(256), HasIncompleteMetaData varchar(256),

  IsForceOffline varchar(256), IsCopyOnly varchar(256), FirstRecoveryForkID varchar(256), ForkPointLSN varchar(256),

  RecoveryModel varchar(256), DifferentialBaseLSN varchar(256), DifferentialBaseGUID varchar(256),

  BackupTypeDescription varchar(256), BackupSetGUID varchar(256), CompressedBackupSize varchar(256),containment varchar(256),keyalgorithm varchar(8000),

  encryptorthumbpoint varchar(8000),encryptortype varchar(8000))

  create table #Backup_File_Details

  (

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

  UniqueID uniqueidentifier,

  ReadOnlyLSN numeric(25,0),

  ReadWriteLSN numeric(25,0),

  BackupSizeInBytes bigint,

  SourceBlockSize int,

  FileGroupID int,

  LogGroupGUID uniqueidentifier,

  DifferentialBaseLSN numeric(25,0),

  DifferentialBaseGUID uniqueidentifier,

  IsReadOnl bit,

  IsPresent bit,

  TDEThumbprint varbinary(32), -- remove this column if using SQL 2005

  snapshoturl varchar(8000)

  )

  INSERT INTO #headers

  EXEC ('restore headeronly from disk = '''+ @BackupFolder + '''');

  INSERT INTO #Backup_File_Details

  EXEC ('restore filelistonly from disk = '''+ @BackupFolder + '''');

  declare @databasename varchar(3000)

  select @databasename = databasename from #headers

  declare @logicalprimaryname varchar(3000)

  declare @logicalsecondaryname varchar(3000)

  declare @logicallogname varchar(3000)

  select @logicalprimaryname = logicalname from #backup_file_details where fileid=1

  select @logicallogname = logicalname from #backup_file_details where fileid=2

  create table #sysfiles

  (

  fileid int,

  [name] varchar(2000),

  filename varchar(8000)

  )

  insert #sysfiles

  select fileid,[name],[filename] from master..sysaltfiles

  where dbid in(select dbid from master..sysdatabases where [name]=@dbname)

  declare @sysfilesprimarydata varchar(8000)

  declare @sysfileslog varchar(8000)

  select @sysfilesprimarydata = [filename] from #sysfiles where fileid=1

  select @sysfileslog = [filename] from #sysfiles where fileid=2

  declare @statement varchar(max)

  select @statement = 'RESTORE DATABASE '+convert(varchar(2000),@dbname)+'

  FROM DISK='''+convert(varchar(8000),@backupfolder)+'''

  WITH

  MOVE '''+convert(varchar(8000),@logicalprimaryname)+''' TO '''+convert(varchar(8000),@sysfilesprimarydata)+''',

  MOVE '''+convert(varchar(8000),@logicallogname)+''' TO '''+convert(varchar(8000),@sysfileslog)+''',

  RECOVERY,REPLACE,STATS=10'

  EXEC(@statement)

  drop table #headers

  drop table #Backup_File_Details

  drop table #sysfiles

  SET NOCOUNT OFF

  END

   

   

• Ken McKelvey

  SSCoach

  Points: 18484

  More actions

  April 14, 2020 at 2:40 pm

  #3742758

  You can generate the MOVEs from the FILELIST.

  Personally I would not EXEC the results of anything like this. I would just print the result and then eyeball/alter it before copying to another query window.

  DECLARE @SQL nvarchar(4000) = '';
  
  WITH Moves(M, rn)
  AS
  (
  SELECT CHAR(9) + ',MOVE ''' + F.LogicalName + ''' TO ''' + F.PhysicalName + '''' + CHAR(13) + CHAR(10)
  ,ROW_NUMBER() OVER (ORDER BY F.[Type], F.FileId)
  FROM #Backup_File_Details F
  )
  SELECT @SQL = @SQL + M
  FROM Moves
  ORDER BY rn;
  SET @SQL = LEFT(@SQL, LEN(@SQL) - 2) + ';';
  
  PRINT @SQL;