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