Script to construct a restore/recovery script

By -
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

Comments

Post a Comment

Popular posts from this blog

Index Clean-Up Scripts

By -
dropping un-used ones, adding missing ones, and fine-tuning the ones I already have. I thought I’d share some of the scripts I’ve been using to accomplish this. Un-Used Indexes Script Declare @dbid int     , @dbName varchar(100);   Select @dbid = DB_ID()     , @dbName = DB_Name();   With partitionCTE (object_id, index_id, row_count, partition_count)  As (     Select [object_id]         , index_id         , Sum([rows]) As 'row_count'         , Count(partition_id) As 'partition_count'     From sys.partitions     Group By [object_id]         , index_id )    Select Object_Name(i.[object_id]) as objectName         , i.name         , Case              When i.is_unique = 1          ...
Read more

forgot sa password and no logins are added

By -
Image
Stop the SQL services Edit the properties of the SQL Service Change the startup parameters of the SQL service by adding a  –m;  in front of the existing parameters Start the SQL services. These are now running in Single User Mode. Start CMD on tthe SQL server Start the SQLCMD command. Now we create a new user. Enter following commands CREATE LOGIN recovery WITH PASSWORD = ‘TopSecret 1′ (Remember SQL server has default strong password policy Go Now this user is created Now we grant the user a SYSADMIN roles using the same SQLCMD window. sp_addsrvrolemember ‘recovery’, ‘sysadmin’ go
Read more

The SQL Server DBA’s Guide to Teradata

By -
Being a long-time devotee of all things Microsoft SQL Server, I had quite a bit of learning to do to get up to speed on our new Teradata appliance. This guide attempts to distill some of what I have learned. I will primarily focus on what I have found most lacking: examples of how to convert SQL Server T-SQL to Teradata SQL. This guide will use the vendor-supplied sample databases:  SQL Server’s AdventureWorksDW2012 , and the  Teradata’s Express 14.0 Retail database . Also, while this is probably self explanatory, the “mufford” object referenced in the Teradata examples is simply my own personal sandbox. You’ll need to replace any “mufford” reference to whatever your development sandbox may be named. Release History: 2012-08-17 – Added UPDATE examples; reformatted 2012-08-12 – Added date examples 2012-08-08 – Initial Release SELECT Statements Simple SELECT SQL Server: Best practice is to qualify the table schema; qualifying the database is optional unless ...
Read more