Script to provide backup sequence with the shortest restore time - Interview Question

Scenario 

You may be working in a team and there are scenarios where database backups are generated on an adhoc basis. Taking database backups in an adhoc manner breaks a backup plan that is scheduled in an production environment (We are not talking about COPY_ONLY backups). Later, it is difficult to keep a track and identify the right backup sequence to perform restore.
This script will help you to retrieve information about Full, Differential and Log backups for the databases residing on the same instance. It also shows the backup chain that will help us to restore this databases in the shortest time.
This question was asked to me during an interview.




T-SQL Script 


/*******************************************
Script: Provides backup sequence with the shortest restore time.
*******************************************/

DECLARE @DatabaseName Varchar(MAX)
DECLARE @BackupSequence TABLE 
                  (    
                        Server_Name SYSNAME,
                        [Database_Name] SYSNAME,
                        Backup_Finish_Date DateTime,
                        Backup_Type Varchar(30),          
                        Backup_Size Decimal(10,2),
                        Recovery_Model Varchar(20),
                        Physical_Device_Name Varchar(500),
                        [User_Name] SYSNAME
                  )

--Here, we can filter the database as per the requirement. In this script we will be listing result for all the databases except temp database.
DECLARE cur CURSOR FOR
      Select [Name] From sys.databases WHERE name <> 'tempdb'
OPEN cur
FETCH NEXT FROM cur INTO @DatabaseName

WHILE(@@FETCH_STATUS = 0)
BEGIN
;with FullBkp AS --listing full database backup
(
      SELECT bkp.Server_Name,
       bkp.[Database_Name],
       bkp.Backup_Finish_Date,
       'FULL' AS Backup_Type,       
       bkp.Backup_Size/1024/1024 AS Backup_Size,
       bkp.Recovery_Model,
       bmf.Physical_Device_Name,
       bkp.[User_Name]
      FROM msdb.dbo.BackupMediaFamily bmf
      INNER JOIN msdb.dbo.BackupSet bkp
      ON bkp.Media_Set_ID = bmf.media_set_id
      WHERE bkp.[Database_Name] = @DatabaseName
      AND bkp.Backup_Finish_Date =
            (
                  SELECT MAX(Backup_Finish_Date)
                        FROM msdb.dbo.BackupSet
            WHERE Database_Name = @DatabaseName
                        AND type = 'D' AND is_copy_only = 0
            )
      AND bkp.type = 'D'
)
,DiffBkp AS --listing differential database backup
(
      SELECT bkp.Server_Name,
           bkp.[Database_Name],
           bkp.Backup_Finish_Date,
           'Differential' AS Backup_Type,           
           bkp.Backup_Size/1024/1024 AS Backup_Size,
           bkp.Recovery_Model,
           bmf.Physical_Device_Name,
           bkp.[User_Name]
      FROM msdb.dbo.BackupMediaFamily bmf
      INNER JOIN msdb.dbo.BackupSet bkp
      ON bkp.Media_Set_ID = bmf.media_set_id
      WHERE bkp.Database_Name = @DatabaseName
      AND bkp.type = 'I' AND bkp.Backup_Finish_Date =
      (
            SELECT MAX(Backup_Finish_Date)
        FROM msdb.dbo.BackupSet
        WHERE Database_Name = @DatabaseName
            AND type = 'I' AND is_copy_only = 0 AND Backup_Finish_Date >
            (
                  SELECT Backup_Finish_Date FROM FullBkp
            )
      )
)
,LogBkp AS --listing log database backup
(
      SELECT bkp.Server_Name,
               bkp.[Database_Name],
           bkp.Backup_Finish_Date,
           'Log' AS Backup_Type,        
           bkp.Backup_Size/1024/1024 AS Backup_Size,
           bkp.Recovery_Model,
           bmf.Physical_Device_Name,
           bkp.[User_Name]
      FROM msdb.dbo.BackupMediaFamily bmf
      INNER JOIN msdb.dbo.BackupSet bkp
      ON bkp.Media_Set_ID = bmf.media_set_id
      WHERE bkp.[Database_Name] = @DatabaseName
      AND bkp.type = 'L' AND bkp.Backup_Finish_Date >
      (
            CASE
                  WHEN (SELECT COUNT(*) FROM DiffBkp) <> 0
                        THEN (SELECT Backup_Finish_Date FROM DiffBkp)
                  ELSE (SELECT Backup_Finish_Date FROM FullBkp)
            END
      )
)

INSERT @BackupSequence
SELECT * FROM LogBkp
      UNION ALL
SELECT * FROM DiffBkp
      UNION ALL
SELECT * FROM FullBkp
ORDER BY Backup_Finish_Date

FETCH NEXT FROM cur
INTO @DatabaseName
END

CLOSE cur
DEALLOCATE cur

SELECT Server_Name AS InstanceName,
                [Database_Name] AS DatabaseName,
                Backup_Type AS BackupType,
                Physical_Device_Name AS BackupLocation,
                Backup_Finish_Date AS BackupTime,
                Backup_Size AS 'BkpSize [MB]',
                Recovery_Model AS RecoveryModel,
               [User_Name] AS BackupUser
FROM @BackupSequence

No comments:

Post a Comment