Saturday, 16 February 2013

Script to construct a restore/recovery script

construct a recovery script from most recent full backup then all subsequent log file backups. Can override backup folder and data/log files WITH MOVE if restoring to a test server with different folder structure. Uses checksum where possible to verify backups.





-- OVERRIDE VALUES FROM EXISTING
DECLARE @ToFileFolder VARCHAR(2000);
DECLARE @ToLogFolder VARCHAR(2000);
DECLARE @BackupDeviceFolder VARCHAR(2000);

SET @ToFileFolder = 'E:\Data\';
SET @ToLogFolder = 'D:\Logs\';
SET @BackupDeviceFolder = 'F:\Backups\';

--------------------------------------------------------------------------------------------------------------
-- Most recent full backup + subsequent log file backups
--------------------------------------------------------------------------------------------------------------
WITH CTE 
(
 database_name
 ,current_compatibility_level
 ,current_is_read_only
 ,current_state_desc
 ,current_recovery_model_desc
 ,has_backup_checksums
 ,[type]
 ,backupmediasetid
 ,backupfinishdate
 ,physical_device_name
)
AS
(
 SELECT 
   bs.database_name
  ,d.[compatibility_level] AS current_compatibility_level
  ,d.[is_read_only] AS current_is_read_only
  ,d.[state_desc] AS current_state_desc
  ,d.[recovery_model_desc] current_recovery_model_desc
  ,bs.has_backup_checksums 
  ,'D' AS [type]
  ,bs.media_set_id AS backupmediasetid
  ,x.backup_finish_date AS backupfinishdate
  ,mf.physical_device_name
 FROM msdb.dbo.backupset bs
 INNER JOIN sys.databases d
  ON bs.database_name = d.name
 INNER JOIN
 (
  SELECT 
    database_name
   ,MAX(backup_finish_date) backup_finish_date 
  FROM msdb.dbo.backupset a
  JOIN msdb.dbo.backupmediafamily b
   ON a.media_set_id = b.media_set_id 
  WHERE a.[type] = 'D'
  AND b.[Device_Type] = 2
  GROUP BY database_name
 ) x 
  ON x.database_name = bs.database_name
  AND x.backup_finish_date = bs.backup_finish_date

 JOIN msdb.dbo.backupmediafamily mf
  ON mf.media_set_id = bs.media_set_id 

 --------------------------
 UNION
 --------------------------
 SELECT 
   bs.database_name
  ,d.[compatibility_level] AS current_compatibility_level
  ,d.[is_read_only] AS current_is_read_only
  ,d.[state_desc] AS current_state_desc
  ,d.[recovery_model_desc] current_recovery_model_desc
  ,bs.has_backup_checksums 
  ,'L' AS [type]
  ,y.media_set_id AS backupmediasetid
  ,y.backup_finish_date as backupfinishdate
  ,mf.physical_device_name
   
 FROM msdb.dbo.backupset bs
 INNER JOIN sys.databases d
  ON bs.database_name = d.name
 INNER JOIN
 (
  SELECT 
    database_name
   ,MAX(backup_finish_date) backup_finish_date 
  FROM msdb.dbo.backupset a
  JOIN msdb.dbo.backupmediafamily b
   ON a.media_set_id = b.media_set_id 
  WHERE a.[type] = 'D'
  AND b.[Device_Type] = 2
  GROUP BY database_name
 ) x 
  ON x.database_name = bs.database_name
  AND x.backup_finish_date = bs.backup_finish_date
 INNER JOIN 
 (
  SELECT 
    database_name
   ,backup_finish_date
   ,a.media_set_id   
  FROM msdb.dbo.backupset a
  JOIN msdb.dbo.backupmediafamily b
   ON a.media_set_id = b.media_set_id 
  WHERE a.[type] = 'L'
  AND b.[Device_Type] = 2
 ) y
  ON y.database_name = bs.database_name
  AND y.backup_finish_date > x.backup_finish_date
 
 JOIN msdb.dbo.backupmediafamily mf
  ON mf.media_set_id = y.media_set_id 
 
)

---------------------------------------------------------------
-- RESULT SET COMES FROM HERE
SELECT
  @@SERVERNAME 
 ,a.Sequence
 ,a.Database_Name
 ,a.BackupDevice
 ,a.backupfinishdate
 ,a.Command
FROM 
(
 --------------------------------------------------------------------
 -- Most recent full backup
 --------------------------------------------------------------------
 SELECT 
  ';RESTORE DATABASE ' + d.[name] + SPACE(1) +
  'FROM DISK = ' + '''' + 
   CASE ISNULL(@BackupDeviceFolder,'Actual')
   WHEN 'Actual' THEN CTE.physical_device_name
   ELSE @BackupDeviceFolder + SUBSTRING(CTE.physical_device_name,LEN(CTE.physical_device_name) - CHARINDEX('\',REVERSE(CTE.physical_device_name),1) + 2, CHARINDEX('\',REVERSE(CTE.physical_device_name),1) + 1)
   END + '''' + SPACE(1) + 
  'WITH REPLACE,' + 
  CASE CTE.has_backup_checksums WHEN 1 THEN 'CHECKSUM,' ELSE ' ' END + 
  'NORECOVERY,' + SPACE(1) +
  'MOVE ' + '''' + x.LogicalName + '''' + ' TO ' + 
  '''' + 
   CASE ISNULL(@ToFileFolder,'Actual')
   WHEN 'Actual' THEN x.PhysicalName
   ELSE @ToFileFolder + SUBSTRING(x.PhysicalName,LEN(x.PhysicalName) - CHARINDEX('\',REVERSE(x.PhysicalName),1) + 2, CHARINDEX('\',REVERSE(x.PhysicalName),1) + 1)
   END + '''' + ',' + SPACE(1) +
 
  'MOVE ' + '''' + y.LogicalName + '''' + ' TO ' + 
  '''' + 
   CASE ISNULL(@ToLogFolder,'Actual')
   WHEN 'Actual' THEN y.PhysicalName
   ELSE @ToLogFolder + SUBSTRING(y.PhysicalName,LEN(y.PhysicalName) - CHARINDEX('\',REVERSE(y.PhysicalName),1) + 2, CHARINDEX('\',REVERSE(y.PhysicalName),1) + 1)
   END + '''' AS Command,
   1 AS Sequence,
   d.name AS database_name,
   CTE.physical_device_name AS BackupDevice,
   CTE.backupfinishdate
 
 FROM sys.databases d
 JOIN
 (
  SELECT 
    DB_NAME(mf.database_id) AS name
   ,mf.Physical_Name AS PhysicalName
   ,mf.Name AS LogicalName
  FROM sys.master_files mf
  WHERE type_desc = 'ROWS'
  AND mf.file_id = 1
 ) x
  ON d.name = x.name
 JOIN
 (
  SELECT 
    DB_NAME(mf.database_id) AS name
   ,mf.Physical_Name PhysicalName
   ,mf.Name AS LogicalName
  FROM sys.master_files mf
  WHERE type_desc = 'LOG'
 
 ) y
  ON d.name = y.name
 
 JOIN CTE 
  ON CTE.database_name = d.name
 
 WHERE CTE.[type] = 'D'
 AND d.name NOT IN ('master','model','msdb')
 
 --------------------------------------------------------------------
 UNION -- Restore Log backups taken since most recent full
 --------------------------------------------------------------------
 SELECT 
  ';RESTORE LOG ' + d.[name] + SPACE(1) +
  'FROM DISK = ' + '''' + --CTE.physical_device_name + '''' + SPACE(1) +
   CASE ISNULL(@BackupDeviceFolder,'Actual')
   WHEN 'Actual' THEN CTE.physical_device_name
   ELSE @BackupDeviceFolder + SUBSTRING(CTE.physical_device_name,LEN(CTE.physical_device_name) - CHARINDEX('\',REVERSE(CTE.physical_device_name),1) + 2, CHARINDEX('\',REVERSE(CTE.physical_device_name),1) + 1)
   END + '''' +
  ' WITH NORECOVERY' AS Command,
  32769 AS Sequence,
  d.name AS database_name,
  CTE.physical_device_name AS BackupDevice,
  CTE.backupfinishdate
 
 FROM sys.databases d
 
 JOIN CTE
  ON CTE.database_name = d.name
 
 WHERE CTE.[type] = 'L'
 AND d.name NOT IN ('master','model','msdb')
 
 --------------------------------------------------------------------
 UNION -- Restore WITH RECOVERY
 --------------------------------------------------------------------
 SELECT 
  ';RESTORE DATABASE ' + d.[name] + SPACE(1) + 'WITH RECOVERY' AS Command,
  32770 AS Sequence,
  d.name AS database_name,
  '' AS BackupDevice,
  CTE.backupfinishdate
 
 FROM sys.databases d
 
 JOIN CTE
  ON CTE.database_name = d.name
 
 WHERE CTE.[type] = 'D'
 AND d.name NOT IN ('master','model','msdb')
 
 --------------------------------------------------------------------
 UNION -- CHECKDB
 --------------------------------------------------------------------
 SELECT 
  ';DBCC CHECKDB(' + '''' + d.[name] + '''' + ') WITH NO_INFOMSGS IF @@ERROR > 0 PRINT N''CONSISTENCY PROBLEMS IN DATABASE : ' + d.name + ''' ELSE PRINT N''CONSISTENCY GOOD IN DATABASE : ' + d.name + '''' AS Command,
  32771 AS Sequence,
  d.name AS database_name,
  '' AS BackupDevice,
  CTE.backupfinishdate
 
 FROM sys.databases d
 
 JOIN CTE
  ON CTE.database_name = d.name
 
 WHERE CTE.[type] = 'D'
 AND d.name NOT IN ('master','model','msdb')
 
 ---------------------------------------------------------------------------------------------------------------------------------------------------
 UNION -- MOVE Secondary data files
 ---------------------------------------------------------------------------------------------------------------------------------------------------

 SELECT 
  ', MOVE ' + '''' + b.name + '''' + ' TO ' + 
  '''' + 
   CASE ISNULL(@ToFileFolder,'Actual')
   WHEN 'Actual' THEN b.physical_name
   ELSE @ToFileFolder + SUBSTRING(b.Physical_Name,LEN(b.Physical_Name) - CHARINDEX('\',REVERSE(b.Physical_Name),1) + 2, CHARINDEX('\',REVERSE(b.Physical_Name),1) + 1)
   END + '''',
  b.file_id AS Sequence,
  DB_NAME(b.database_id) AS database_name,
  '' AS BackupDevice,
  CTE.backupfinishdate
 
 FROM sys.master_files b
 INNER JOIN CTE
  ON CTE.database_name = DB_NAME(b.database_id)
 
 WHERE CTE.[type] = 'D'
 AND b.type_desc = 'ROWS'
 AND b.file_id > 2
 
) a

ORDER BY 
 database_name, 
 sequence,
 backupfinishdate

No comments:

Post a Comment