Hello Geeks,
The time is flying by as I rejoice in the aftermath of the SQLServerGeeks Annual Summit 2015. It has been a wonderful event and once in a life time opportunity to be part of the Asia’s first SQL conference. More eventful was my personal life with the arrival of my princess Mahira on 8th of October into our lives to make it even more memorable.
It is really hard to take out time now to write blogs and get back on track with my passion. This is the first attempt to get back on track. To start with, I have been challenged to restore database from backups many a times. This is a daunting task if there is a long backup chain and there is always someone taking few adhoc backups in between to break your backup chain.
Today we will see a script I have written to save my some time. This will help you check if your backup chain is as expected. There are always few people/accounts which have access more than what is needed. There are few scenarios where the adhoc backup are taken by these accounts. This script can help you in identifying those cases. This will also help you in identifying the right sequence of restore.
/*******************************************
Script: Backup and Restore Sequence
Author: Manohar Punna
Version 1.0
Provies backup sequence with the shortest restore time.
This script only works if the backup history is available till the oldest restorable file needed for the shortest restore.
*******************************************/
DECLARE @backup_history TABLE(server_name SYSNAME,
database_name SYSNAME,
backup_start_date DATETIME,
backup_finish_date DATETIME,
backup_type NVARCHAR(15),
backup_size DECIMAL(10,2),
compressed_size DECIMAL(10,2),
duration_seconds INT,
database_creation_date DATETIME,
recovery_model NVARCHAR(10),
physical_device_name NVARCHAR(500),
user_name SYSNAME)
DECLARE cur CURSOR FOR
SELECT DISTINCT(name) FROM sys.databases WHERE name <> 'tempdb'
DECLARE @DBName NVARCHAR(1000)
OPEN cur
FETCH NEXT FROM cur
INTO @DBName
WHILE(@@FETCH_STATUS = 0)
BEGIN
;with D AS
(
select b.server_name,
b.database_name,
b.backup_start_date,
b.backup_finish_date,
'FULL' AS backup_type,
b.backup_size/1024/1024 AS backup_size,
b.compressed_backup_size/1024/1024 AS compressed_size,
DATEDIFF(SECOND,b.backup_start_date, b.backup_finish_date) AS duration_seconds,
b.database_creation_date,
b.recovery_model,
f.physical_device_name,
b.user_name
from msdb.dbo.backupmediafamily f
inner join msdb.dbo.backupset b
on b.media_set_id = f.media_set_id
WHERE b.database_name = @DBName
AND b.backup_finish_date = (SELECT MAX(backup_finish_date)
FROM msdb.dbo.backupset
WHERE database_name = @DBName AND type = 'D' AND is_copy_only = 0)
AND b.type = 'D')
,I AS
(
select b.server_name,
b.database_name,
b.backup_start_date,
b.backup_finish_date,
'Differential' AS backup_type,
b.backup_size/1024/1024 AS backup_size,
b.compressed_backup_size/1024/1024 AS compressed_size,
DATEDIFF(SECOND,b.backup_start_date, b.backup_finish_date) AS duration_seconds,
b.database_creation_date,
b.recovery_model,
f.physical_device_name,
b.user_name
from msdb.dbo.backupmediafamily f
inner join msdb.dbo.backupset b
on b.media_set_id = f.media_set_id
WHERE b.database_name = @DBName
AND b.type = 'I'
AND b.backup_finish_date = (SELECT MAX(backup_finish_date)
FROM msdb.dbo.backupset
WHERE database_name = @DBName AND type = 'I' AND is_copy_only = 0
AND backup_finish_date > (SELECT backup_finish_date FROM D))),
L AS
(
select b.server_name,
b.database_name,
b.backup_start_date,
b.backup_finish_date,
'Log' AS backup_type,
b.backup_size/1024/1024 AS backup_size,
b.compressed_backup_size/1024/1024 AS compressed_size,
DATEDIFF(SECOND,b.backup_start_date, b.backup_finish_date) AS duration_seconds,
b.database_creation_date,
b.recovery_model,
f.physical_device_name,
b.user_name
from msdb.dbo.backupmediafamily f
inner join msdb.dbo.backupset b
on b.media_set_id = f.media_set_id
WHERE b.database_name = @DBName
AND b.type = 'L'
AND b.backup_finish_date > (CASE WHEN (SELECT COUNT(1) FROM I) <> 0 THEN (SELECT backup_finish_date FROM I) ELSE (SELECT backup_finish_date FROM D) END))
INSERT @backup_history
SELECT * FROM L
UNION ALL
SELECT * FROM I
UNION ALL
SELECT * FROM D
ORDER BY backup_finish_date
FETCH NEXT FROM cur
INTO @DBName
END
CLOSE cur
DEALLOCATE cur
SELECT server_name AS Instance,
database_name AS DBName,
backup_type AS bkptype,
--backup_size,
--compressed_size,
--duration_seconds,
--database_creation_date,
--recovery_model,
--backup_start_date,
physical_device_name AS bkpfile,
backup_finish_date AS bkptime,
user_name AS bkpuser
FROM @backup_history
The result set will give you the aggregate size of all backups, individual type of backups and each database restore sequence. In the below result set, you can observe that I have taken a full backup off schedule which has reset your restore sequence. So instead of the full backup taken at 2015-10-19 14:34, we have to start with the full backup taken manually at 2015-10-19 14:44.
Note 1: This script only works if there is enough history to the oldest backup in the backup chain to be restored.
Note 2: There are additional columns which can be uncommented in the last select statement for additional information.
Hope this script helps you and any feedback is greatly appreciated.
Happy Learning,
Manu
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook
