Technical Article

Restore Script Generator

,

If you have a database with multiple files that you need to restore from one server to another, it can be a real pain.  But this script makes it easy.  Just put in the file path of where your backup file lives and it will create a fully formed restore script for you.  Just update the location of where you want to restore the backup to and you are good to go. 

This is especially helpful when you have a database with a dozen or more file groups.

I did not write this and I don't want to take credit for it, but it is a great tool to have.  Whoever JenS@Microsoft is, my hat is off to them.

SET NOCOUNT ON      

declare @BackupFolder VARCHAR(MAX) = '\\<File Path of backup files goes here>\'
declare @BackupFilePattern VARCHAR(MAX) = '*.bak'
declare @Overwrite int = 0
declare @DropDatabaseFirst int = 0
declare @IncludeMoveOperation int = 1
declare @Debug int = 0

      
PRINT '--BackupFile restore (JensS@Microsoft.com), 08.09.2011 V0.2'      
PRINT '--========================================================='      
      
IF(@BackupFilePattern IS NULL)      
BEGIN      
 RAISERROR('No BackupFilePattern was passed, using the default wildcard pattern *.bak',10,1)      
 SET @BackupFilePattern = '*.bak'      
END      
      
/* DECLARE Variables*/      
DECLARE @FileCounter INT = 0      
      
DECLARE @BackupCommand NVARCHAR(MAX) = ''       
DECLARE @BackupFile NVARCHAR(MAX) = ''       
DECLARE @DatabaseName SYSNAME =  ''      
DECLARE @IncludeMoveOperationScript NVARCHAR(MAX) = ''      
      
      
DECLARE @dirList TABLE(id int identity(1,1),dirline nvarchar(MAX),isFile BIT)      
DECLARE @Files TABLE(id int identity(1,1),name nvarchar(MAX))      
      
      
DECLARE @RestoreInformation TABLE      
(      
 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,      
 IsReadOnly bit,      
 IsPresent bit,      
 TDEThumbprint varbinary(32)       
)      
      
      
DECLARE @BackupHeader Table      
(      
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,      
CompressedBackupSize BIGINT      
)      
      
SET @BackupFilePattern = 'xp_cmdshell ''dir ' + @BackupFolder + @BackupFilePattern + ''''      
      
/* Get all the files within the pattern */      
INSERT INTO @dirList (dirline) EXEC(@BackupFilePattern)      
      
/* Filter the shell output to files only */      
;WITH TempTable(id,dirline)      
AS      
(      
  SELECT      
 id,      
 dirline      
  FROM @dirList      
  WHERE id > (      
    SELECT MIN(id) FROM @dirList WHERE dirline LIKE '%Directory of%'      
  ) AND id < (SELECT MAX(id) - 2 FROM @dirList)      
  and dirline IS NOT NULL      
)      
INSERT INTO @Files(name)      
SELECT       
 dirline = @BackupFolder +       
  (      
   CASE WHEN T.dirline LIKE '__/__/%' THEN SUBSTRING(T.dirline,40,100) ELSE      
   SUBSTRING(T.dirline,37,100) END      
  )      
FROM TempTable T      
INNER JOIN @dirList d      
ON d.id = T.id;      
      
/* Get the number of real files*/      
SET @FileCounter = @@ROWCOUNT      
      
IF (@FileCounter = 0)      
BEGIN      
 RAISERROR('No files with the pattern found. The dir of the passed directory comes back with the following information',10,1)      
 select @BackupFilePattern
 --EXEC(@BackupFilePattern)      
 RETURN      
END      
      
WHILE @FileCounter >0      
BEGIN      
      
 SELECT       
  @BackupFile = name       
 FROM @Files      
 WHERE id = @FileCounter      
      
 PRINT '--Generating command for found file ' + @BackupFile      
           
 /* Create the Backup command */       
 INSERT INTO @RestoreInformation      
 EXEC sp_executesql N'RESTORE FILELISTONLY FROM DISK = @BackupFile',N'@BackupFile NVARCHAR(MAX)', @BackupFile      
           
 SET @IncludeMoveOperationScript = ''

 IF @IncludeMoveOperation = 0x1
BEGIN
      SELECT @IncludeMoveOperationScript += CHAR(13) + ' MOVE ' + QUOTENAME(R.LogicalName, CHAR(39)) + ' TO ' + QUOTENAME(R.PhysicalName, CHAR(39)) + ','
      FROM @RestoreInformation R
END
   
 INSERT INTO @BackupHeader      
 EXEC sp_executesql N'RESTORE HEADERONLY FROM DISK = @BackupFile',N'@BackupFile NVARCHAR(MAX)', @BackupFile      
       
 SELECT @DatabaseName = DatabaseName FROM @BackupHeader      
        
 IF @DropDatabaseFirst = 0x1  
 SET @BackupCommand = 'ALTER DATABASE [' + @DatabaseName + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; ' + CHAR(13) + 'DROP DATABASE [' + @DatabaseName + '];' + CHAR(13)  
 ELSE  
 SET @BackupCommand = ''  
   
 SET @BackupCommand += 'RESTORE DATABASE [' + @DatabaseName + '] FROM DISK=' + QUOTENAME(@BackupFile, CHAR(39)) + ' WITH NOUNLOAD, '  + CASE WHEN @Overwrite = 0x1 THEN 'REPLACE,' ELSE '' END + @IncludeMoveOperationScript + ' STATS = 10'      
      
 IF @Debug = 0x1      
  SELECT @BackupCommand      
 ELSE      
  PRINT(@BackupCommand)      
      
 PRINT CHAR(13)      
      
 /* Decrement counter for the next file*/       
 SET @FileCounter-=1      
      
 /* Clean the house */      
 DELETE FROM @RestoreInformation      
 DELETE FROM @BackupHeader      
 SET @BackupCommand = ''      
END

Rate

5 (1)

Share

Share

Rate

5 (1)