DarkMagic With RedGate Virtual Restore and a folder full of Backups

Sorry, blog, for puking all over on this one. I got to excited and had to put it somewhere for my own amazement. This came about cause I got a ton of DBs to restore for our Staging environment and our EMC SnapShot/Clone not completely dialed in yet. So exciting.

Basically, I created a backup folder on the staging server and copied a full set of backups to it. Then using the script below, with minor tweaks to environment specific paths, I can generate a script to do a virtual restore of all my backups. The GUI is great, but I got 50+ DB to get through, and I need to get this to a less manual process for weekly refreshes. Anywho... Enjoy! Sharing is caring.

The "--#region" stuff is from SSMS PACK, get yours.

IF OBJECT_ID('tempdb..#tmp_VirtualRestore') IS NOT NULL DROP PROCEDURE #tmp_VirtualRestore
GO
CREATE PROCEDURE #tmp_VirtualRestore
 @FilePath VARCHAR(256)
 ,@DataFilePath VARCHAR(256) = 'D:\SQL_DATA\'
 ,@LogsFilePath VARCHAR(256) = 'L:\SQL_LOGS\'
 ,@AppendSuffix bit = 1
AS 
BEGIN
SET NOCOUNT ON 

DECLARE  
 @SQL VARCHAR(4000)

IF LEN(ISNULL(@FilePath,'')) < 5 
BEGIN
 PRINT 'Eh, Invalid FilePath?  Please review: ' + ISNULL(@FilePath,'')
 RETURN                                 
END
 
--#region Interrogate Backup file for meta info

 --//Get/Store Header info from backup file(s)
 IF OBJECT_ID('tempdb..#header') IS NOT NULL DROP TABLE #header
 CREATE TABLE #header
 (
  IDX INT identity(1,1)
  , 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
 );

 INSERT #header EXEC ('RESTORE HEADERONLY FROM DISK = ''' + @FilePath + '''');
 
 IF OBJECT_ID('tempdb..#filelist') IS NOT NULL DROP TABLE #filelist
  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)
    , 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)
  
 INSERT #fileList EXEC ('RESTORE FILELISTONLY FROM DISK = ''' + @FilePath + '''');

--#endregion  

--#region Clean DataFileName

IF OBJECT_ID('tempdb..#kissfilelist') IS NOT NULL DROP TABLE #kissfilelist
  CREATE TABLE #kissfilelist
  (
  FileID BIGINT
  , LogicalName NVARCHAR(128)
    , DataFileName NVARCHAR(260)
    , [Type] CHAR(1))
INSERT INTO #kissfilelist
 SELECT 
  FileID
  ,LogicalName
  ,LTRIM(RTRIM(REVERSE(SUBSTRING(REVERSE(PhysicalName),0,CHARINDEX('\', REVERSE(PhysicalName),0)))))
  ,Type
 FROM #fileList

--#endregion

IF EXISTS(SELECT DatafileName FROM #kissfilelist WHERE
 RIGHT(DataFileName,4) NOT IN ('.ldf','.mdf','.ndf'))
BEGIN
 PRINT 'ERROR, Unexpected extention in DataFileName '
 SELECT  'ERROR, Unexpected extention in DataFileName ' + DatafileName 
  FROM #kissfilelist WHERE RIGHT(DatafileName,4) NOT IN ('.ldf','.mdf','.ndf')

 RETURN
END 

 UPDATE #kissfilelist
  SET DataFileName = 
   REPLACE(REPLACE(REPLACE(DataFileName,'.ldf','.vldf'),'.mdf','.vmdf'),'.ndf','.vndf')

 SELECT @SQL = ( 
 SELECT '
--// [' + DatabaseName + '] '  + CONVERT(VARCHAR(50),GETDATE(),109) + ' 
RESTORE DATABASE [' + DatabaseName + CASE WHEN ISNULL(@AppendSuffix,0) = 1 THEN '_Virtual' ELSE '' END + '] FROM
DISK=N' + QUOTENAME(@FilePath,CHAR(39)) + '
WITH' 
 FROM #header
  FOR XML PATH('')
 )
 
 SELECT @SQL = @SQL + (
 SELECT '
 ' +
  CASE WHEN ROW_NUMBER() OVER(ORDER BY FileID ASC) > 1 THEN  ',' ELSE '' END
  + 'MOVE N' + QUOTENAME(LogicalName,CHAR(39)) + 
   ' TO N' + QUOTENAME(CASE WHEN Type ='L' THEN @LogsFilePath ELSE @DataFilePath END 
    + DataFileName
    ,CHAR(39))    
 FROM 
  #kissfilelist
 FOR XML PATH('')
 )
 
 SELECT @SQL = @SQL + (
  SELECT '
 ,NORECOVERY, STATS=10, REPLACE
 RESTORE DATABASE  [' + DatabaseName + CASE WHEN ISNULL(@AppendSuffix,0) = 1 THEN '_Virtual' ELSE '' END + '] WITH RECOVERY'
FROM #header
  FOR XML PATH('')
 )
 SELECT @SQL = REPLACE(@SQL,'&#x0D;','') 
 
 PRINT @SQL
END 
GO
SET NOCOUNT ON
DECLARE 
 @BackUpFolder VarChar(512)
 ,@CMD VarChar(1028)
 ,@FileCount INT
 ,@Counter INT
 ,@DBFileName VARCHAR(256)
 
 ,@DataFilePath VARCHAR(256)
 ,@LogsFilePath VARCHAR(256)
 ,@AppendSuffix bit

SELECT  
 @BackUpFolder = 'D:\SQL_BACKUPS\SQLa\'
 ,@DataFilePath = 'D:\SQL_DATA\'
 ,@LogsFilePath = 'L:\SQL_LOGS\'
 ,@AppendSuffix = 0
--#region Meat and Potaters
 
--//Temp table to store cmd outputs
IF object_id('tempdb..#tmpDetails') IS NOT NULL drop table #tmpDetails
 create table #tmpDetails(details varchar(4000), DateLogged DateTime Default(getDate()))

--// basic cmd line  
SELECT
 @cmd = 'dir /A-D /ON ' + @BackUpFolder

--// Capture files 
IF object_id('tempdb..#tmpFiles') IS NOT NULL drop table #tmpFiles
CREATE TABLE #tmpFiles (Files VARCHAR(500))
INSERT  INTO #tmpFiles
  EXEC xp_cmdshell @cmd

DECLARE  @tmpDate TABLE (IDX INT IDENTITY(1,1),fileDate DateTime, FileName VarChar(500))

INSERT INTO @tmpDate
   SELECT
  CONVERT(DATETIME, LEFT(files, 20))
  ,LTRIM(RIGHT(files, CHARINDEX(' ', REVERSE(files)))) 
   FROM
  #tmpFiles
   WHERE
  ISDATE(LEFT(files, 20)) = 1
SELECT @FileCount = @@ROWCOUNT, @Counter = 1

--#endregion
WHILE @Counter < @FileCount
BEGIN
 SELECT 
  @DBFileName = @BackUpFolder + FILENAME 
 FROM @tmpDate WHERE IDX = @Counter
 --//PRINT @DBFileName
 
 EXEC #tmp_VirtualRestore @DBFileName,@DataFilePath,@LogsFilePath,  @AppendSuffix
 
 SELECT @Counter = @Counter + 1
END


I just cared all over you :-P

Comments :

1
Brad SQLPhilosopher said...
on 

I am sorry it took me so long to get this put together, here is a link to my script: http://www.sqlphilosopher.com/wp/2013/01/my-red-gate-hyperbac-dbcc-checkdb-script/