daily pastebin goal
28%
SHARE
TWEET

Untitled

a guest Feb 16th, 2019 67 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /**************
  2.     Script out Backup Creation
  3.     by clifton_h (stackoverflow)
  4. **************/
  5.  
  6. DECLARE @directory NVARCHAR(2000) = N'C:Program FilesMicrosoft SQL ServerMSSQL14.MSSQLSERVERMSSQLBackup'
  7.       , @datafile_destination NVARCHAR(3000) = N'Z:DATADISK1sql_dat'
  8.       , @logfile_destination NVARCHAR(3000) = N'Z:LOGDISK1sql_log'
  9.       , @file   NVARCHAR(2000)
  10.       , @uniqueHash VARBINARY(100)
  11.  
  12. CREATE TABLE #BackupHeader (BackupName  nvarchar(128)
  13. , BackupDescription nvarchar(255)  
  14. , BackupType    SMALLINT
  15. , ExpirationDate    datetime
  16. , Compressed    CHAR(1)
  17. , Position  smallint
  18. , DeviceType    tinyint
  19. , UserName  nvarchar(128)
  20. , ServerName    nvarchar(128)
  21. , DatabaseName  nvarchar(128)
  22. , DatabaseVersion   INT
  23. , DatabaseCreationDate  datetime
  24. , BackupSize    numeric(20,0)
  25. , FirstLSN  numeric(25,0)
  26. , LastLSN   numeric(25,0)
  27. , CheckpointLSN numeric(25,0)
  28. , DatabaseBackupLSN numeric(25,0)
  29. , BackupStartDate   datetime
  30. , BackupFinishDate  datetime
  31. , SortOrder smallint
  32. , CodePage  smallint
  33. , UnicodeLocaleId   INT
  34. , UnicodeComparisonStyle    INT
  35. , CompatibilityLevel    tinyint
  36. , SoftwareVendorId  INT
  37. , SoftwareVersionMajor  INT
  38. , SoftwareVersionMinor  INT
  39. , SoftwareVersionBuild  INT
  40. , MachineName   nvarchar(128)
  41. , Flags INT
  42. , BindingID uniqueidentifier
  43. , RecoveryForkID    uniqueidentifier
  44. , Collation nvarchar(128)
  45. , FamilyGUID    uniqueidentifier
  46. , HasBulkLoggedData bit
  47. , IsSnapshot    bit
  48. , IsReadOnly    bit
  49. , IsSingleUser  bit
  50. , HasBackupChecksums    bit
  51. , IsDamaged bit
  52. , BeginsLogChain    bit
  53. , HasIncompleteMetaData bit
  54. , IsForceOffline    bit
  55. , IsCopyOnly    bit
  56. , FirstRecoveryForkID   uniqueidentifier
  57. , ForkPointLSN  numeric(25,0)
  58. , RecoveryModel nvarchar(60)
  59. , DifferentialBaseLSN   numeric(25,0)
  60. , DifferentialBaseGUID  uniqueidentifier
  61. , BackupTypeDescription nvarchar(60)
  62. , BackupSetGUID uniqueidentifier
  63. , CompressedBackupSize  bigint
  64. , Containment   tinyint NOT NULL
  65. , KeyAlgorithm  nvarchar(32)
  66. , EncryptorThumbprint   varbinary(20)
  67. , EncryptorType nvarchar(32)
  68. /*ADDED columns so we can track this*/
  69. , bak_location  NVARCHAR(2000)
  70. , unique_Hash AS HASHBYTES('SHA1', CAST(ServerName AS VARBINARY(100) ) + CAST(DatabaseName AS VARBINARY(100) ) + CAST(BackupStartDate AS VARBINARY(100) ) + CAST(DatabaseBackupLSN AS VARBINARY(100) ))
  71. )
  72. /*  get list of expected files and their logical names  */
  73. CREATE TABLE #FileList (LogicalName varchar(128),[PhysicalName] varchar(128), [Type] varchar, [FileGroupName] varchar(128), [Size] varchar(128),
  74.             [MaxSize] varchar(128), [FileId]varchar(128), [CreateLSN]varchar(128), [DropLSN]varchar(128), [UniqueId]varchar(128), [ReadOnlyLSN]varchar(128), [ReadWriteLSN]varchar(128),
  75.             [BackupSizeInBytes]varchar(128), [SourceBlockSize]varchar(128), [FileGroupId]varchar(128), [LogGroupGUID]varchar(128), [DifferentialBaseLSN]varchar(128), [DifferentialBaseGUID]varchar(128), [IsReadOnly]varchar(128), [IsPresent]varchar(128), [TDEThumbprint]varchar(128), SnapshotUrl VARCHAR(4000)
  76. /*ADDED columns so we can track this*/
  77.             , unique_HASH VARBINARY(100)
  78. )
  79.  
  80. DECLARE @List_Bak AS TABLE (FileName VARCHAR(1000), depth INT , [file] INT)
  81. /*******************
  82.  [1] Prepare List
  83. ******************/
  84. INSERT INTO @List_Bak
  85. EXEC Master.dbo.xp_dirtree @directory, 0, 1
  86.  
  87.     DECLARE cursor_bak CURSOR
  88.         FOR SELECT @directory + '' + FileName
  89.         FROM @List_Bak
  90.         WHERE [FileName] LIKE '%.bak'
  91.     OPEN cursor_bak
  92.     FETCH NEXT FROM cursor_bak INTO @file
  93.     WHILE @@FETCH_STATUS = 0
  94.         BEGIN
  95.         INSERT INTO #BackupHeader (BackupName, BackupDescription, BackupType, ExpirationDate, Compressed, Position, DeviceType, UserName, ServerName, DatabaseName, DatabaseVersion, DatabaseCreationDate, BackupSize, FirstLSN, LastLSN, CheckpointLSN, DatabaseBackupLSN, BackupStartDate, BackupFinishDate, SortOrder, CodePage, UnicodeLocaleId, UnicodeComparisonStyle, CompatibilityLevel, SoftwareVendorId, SoftwareVersionMajor, SoftwareVersionMinor, SoftwareVersionBuild, MachineName, Flags, BindingID, RecoveryForkID, Collation, FamilyGUID, HasBulkLoggedData, IsSnapshot, IsReadOnly, IsSingleUser, HasBackupChecksums, IsDamaged, BeginsLogChain, HasIncompleteMetaData, IsForceOffline, IsCopyOnly, FirstRecoveryForkID, ForkPointLSN, RecoveryModel, DifferentialBaseLSN, DifferentialBaseGUID, BackupTypeDescription, BackupSetGUID, CompressedBackupSize, Containment, KeyAlgorithm, EncryptorThumbprint, EncryptorType) --, bak_location
  96.         EXEC('RESTORE HEADERONLY FROM DISK = N''' + @file + '''')      
  97.  
  98.         SELECT @uniqueHash = unique_Hash
  99.         FROM #BackupHeader
  100.         WHERE bak_location IS NULL
  101.  
  102. /* Hash will allow us to easily compare files with their backup sets    */
  103.         UPDATE #BackupHeader
  104.             SET bak_location = @file
  105.             WHERE bak_location IS NULL
  106.  
  107.         INSERT INTO #FileList ( LogicalName, PhysicalName, Type, FileGroupName, Size, MaxSize, FileId, CreateLSN, DropLSN, UniqueId, ReadOnlyLSN, ReadWriteLSN, BackupSizeInBytes, SourceBlockSize, FileGroupId, LogGroupGUID, DifferentialBaseLSN, DifferentialBaseGUID, IsReadOnly, IsPresent, TDEThumbprint, SnapshotUrl)
  108.         EXEC('
  109.         RESTORE FILELISTONLY
  110.            FROM DISK=''' +@file+ '''
  111.            ')
  112.  
  113.         UPDATE #FileList
  114.             SET unique_Hash = @uniqueHash
  115.             WHERE unique_Hash IS NULL
  116.  
  117.         FETCH NEXT FROM cursor_bak INTO @file
  118.         END
  119.     CLOSE cursor_bak
  120.  
  121.     DEALLOCATE cursor_bak
  122.  
  123. /*******************
  124.  [2] Script Out Backup Sets
  125. ******************/
  126. ;WITH List AS (
  127.     SELECT bak_location, BH.ServerName, BH.DatabaseName, DatabaseCreationDate, BH.CheckpointLSN, BH.LastLSN, BackupSize / 1024.0 / 1024 /1024 AS BackupSizeGB, CompatibilityLevel/*, SoftwareVendorId, MachineName*/
  128.     /*   , IsSnapshot, HasBackupChecksums, IsDamaged, HasIncompleteMetaData, IsCopyOnly, RecoveryModel, BackupTypeDescription, KeyAlgorithm*/, EncryptorType
  129.         , BH.Position, BH.Unique_Hash
  130.     FROM #BackupHeader BH
  131.     WHERE DeviceType = 2
  132. )
  133. SELECT DISTINCT BH.ServerName, BH.DatabaseName, BH.CheckpointLSN, LastLSN, D.Param
  134. FROM List BH
  135. LEFT OUTER JOIN (SELECT A.Unique_Hash
  136. , '--CheckPoint: ' + CAST(MAX(A.CheckpointLSN) AS VARCHAR(100) ) + ' | LastLSN: ' +  CAST( MAX(A.LastLSN) AS VARCHAR(100) ) + '
  137. RESTORE DATABASE ' + A.DatabaseName + '
  138.     FROM '
  139. /*  Backup file list    */  
  140.     + STUFF(( SELECT DISTINCT ',  DISK =''' + bak_location + ''''
  141.     + CHAR(13) + CHAR(9)
  142.         FROM #BackupHeader
  143.         WHERE unique_Hash = A.Unique_Hash
  144.         FOR XML PATH(''),TYPE)
  145.             .value('.','NVARCHAR(MAX)'),1,2,'')
  146. /*  Move Locations  */
  147. + ' WITH ' + STUFF(( SELECT ',  MOVE ''' + FL.LogicalName
  148.             /*  Make this the directory you need    */
  149.     + ''' TO ''' + CASE WHEN FL.Type = 'D' THEN @datafile_destination WHEN FL.Type = 'L' THEN @logfile_destination END + '''' + FL.LogicalName
  150.                 + CASE WHEN FL.Type = 'D' AND FL.FileId = 1 THEN '.mdf'
  151.                         WHEN FL.Type = 'D' THEN '.mdf'
  152.                         WHEN FL.Type = 'L' THEN '.ldf' END + ''''
  153.     + CHAR(13) + CHAR(9)
  154.         FROM ( SELECT DISTINCT unique_Hash, LogicalName, Type, FileID
  155.                FROM #FileList) FL
  156.         LEFT OUTER JOIN ( SELECT DISTINCT unique_Hash FROM #BackupHeader) BH ON BH.unique_Hash = FL.unique_HASH
  157.         WHERE FL.unique_Hash = A.Unique_Hash
  158.         FOR XML PATH(''),TYPE)
  159.             .value('.','NVARCHAR(MAX)'),1,2,'')                    
  160. /*Recover?*/        + ', NORECOVERY'
  161.             AS [Param]
  162.         FROM List A
  163.         GROUP BY A.Unique_Hash, A.DatabaseName
  164.         ) D ON D.Unique_Hash = BH.Unique_Hash
  165. GO
  166.    
  167. --CheckPoint: 179000000011500001 | LastLSN: 179000000011800001
  168. RESTORE DATABASE Testing
  169.     FROM  DISK ='C:Program FilesMicrosoft SQL ServerMSSQL14.MSSQLSERVERMSSQLBackupTtrial.bak'
  170.     ,  DISK ='C:Program FilesMicrosoft SQL ServerMSSQL14.MSSQLSERVERMSSQLBackupunknown.bak'
  171.      WITH  MOVE 'Testing' TO 'Z:DISK1sql_datTesting.mdf'
  172.     ,  MOVE 'Testing_log' TO 'Z:DISK1sql_datTesting_log.ldf'
  173.     , NORECOVERY
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top