T-SQL script to retrive full backup and transaction log backup
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.