T-SQL script to retrive full backup and transaction log backup

  • Article

Backup and Restore skills are most essential part of DBA's job profile. As a DBA we should always be prepared for disaster. Here disaster in term of Accidental data-loss, storage failure, file or database corruption. We must have an appropriate backup to bring database online within given RTO (Recover Time Objective). According to many database administrator, we must have good backup plan. I respectfully disagree, DBA's must have good recovery plan. As we know we might have sufficient time to backup database but we have less time to bring database online. When our RTO is nearly zero, at that time each and every minute become very important.

To reduce database recovery  time i have created a small script which will list most recently taken full database backup and all the T-Log backups after full backup. Backup scenario is

  • Full Backup at 12:00 AM everyday
  • No differential backup
  • Transactional log backup every two hours.
01./*
02.Name: Get Transaction  log after  full database  backup
03. 
04.Author: Nisarg Upadhyay
05. 
06.Description: 
07.•   This script will full  database backup and transaction  log backup taken after full  backup.
08.•   This script will also create  a restore statement along with database  name and backup file path.
09.•   This script can be useful when  you need to  restore database  as fast as possible.
10.•   The backup environment is
11.    1.  Full  database at  12:00 AM daily
12.    2.  Transaction  log backup every 2 hours.
13.*/
14. 
15./*Full Database  Backup*/
16.--exec RestoreDB 
17. 
18.Alter Proc RestoreDB
19.@DatabaseName varchar(50)
20.as
21.begin
22. 
23.SELECT
24.    *
25.FROM (SELECT
26.        'Restore database ' + p.[Database Name] + ' from disk =   ''' + p.[Backup Location] + '''  With Norecovery ' ScriptText
27.        ,MAX(p.[Start Date]) 'StartDate'
28. 
29.    FROM (SELECT
30.            a.database_name 'Database Name'
31.            ,c.physical_device_name 'Backup Location'
32.            ,a.type 'Backup Type'
33.            ,a.backup_start_date 'Start Date'
34.            ,a.backup_finish_date 'End Date'
35.        FROM msdb..backupset a
36.        INNER
37.        JOIN msdb..backupfile b
38.            ON a.backup_set_id = b.backup_set_id
39.        INNER JOIN msdb..backupmediafamily c
40.            ON a.media_set_id = c.media_set_id
41.        WHERE CONVERT(DATE, a.backup_start_date) >= CONVERT(DATE, GETDATE()) --BETWEEN '2015-12-20' AND '2015-12-21'
42.        AND a.type = 'D') p
43.    WHERE p.[Database Name] = @databaseName
44.    GROUP BY     p.[Database  Name]
45.                ,p.[Backup Location]
46.                ,p.[Start Date]
47.                ,p.[End Date]
48.                ,p.[Backup Type] 
49.                 
50.Union ALL
51.             
52.    SELECT DISTINCT
53.        'Restore Log ' + p.[Database Name] + ' from disk = ''' + p.[Backup Location] + ''' With Norecovery ' Script
54.        ,p.[Start Date] 'Backup Date'
55.    FROM (SELECT
56.            a.database_name 'Database Name'
57.            ,c.physical_device_name 'Backup Location'
58.            ,a.type 'Backup Type'
59.            ,a.backup_start_date 'Start Date'
60.            ,a.backup_finish_date 'End Date'
61.            ,b.file_type 'File Type'
62.        FROM msdb..backupset a
63.        INNER
64.        JOIN msdb..backupfile b
65.            ON a.backup_set_id = b.backup_set_id
66.        INNER JOIN msdb..backupmediafamily c
67.            ON a.media_set_id = c.media_set_id
68.        WHERE a.backup_start_date >= (SELECT
69.                MAX(backup_start_date)
70.            FROM backupset
71.            WHERE type = 'D'                                                    --Diff Backup
72.            AND CONVERT(DATE, backup_start_date) = CONVERT(DATE, GETDATE()))
73.        AND a.type = 'L') p
74.    WHERE p.[Database Name] = @DatabaseName) Bck
75.ORDER BY  Bck.StartDate DESC
76. 
77.END

 
In case of any correction, feel free to update the code.

Hope this script will help.