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