0

T-SQL Query to get Backup Information

Emeka OkekeDec 21, 2019

In this post, I will show you how to get backup information from SQL Server 2008 and up. I will also include the TSQL query used to retrieve the records from database.

We will write the T-SQL query to get the backup information on SQL Server 2008 and up instances. The backup will retrieve information about FULL, DIFF & LOG backups. The TSQL script will show the backup chain that will guarantee the shortest restore time if we want to restore the database(s).

The backup information query resultset will also display accumulative data showing the size of all backup files, size of FULL alone, size of DIFF alone and size of LOGs alone. The script will ensure a query resultset that will look like the sample shown below:

Source_Server
database_name
backup_start_date
backup_finish_date
backup_type
Backup_size_MB
Compressed_Backup_size_MB
Duration_Seconds
database_creation_date
recovery_model
physical_device_name
user_name
SQL
ALL_Diff
NULL
NULL
ALL
19.11
19.11
2
NULL
NULL
NULL
NULL
SQL
ALL_FULL
NULLNULL
ALL
2009.33
2009.33
21
NULL
NULL
NULL
NULL
SQL
ALL_Log
NULLNULL
ALL
1.43
1.43
3
NULL
NULL
NULL
NULL
SQL
FULL+DIFF+LOG
NULLNULL
ALL
23.33
23.33
9
NULL
NULL
NULL
NULL
SQL
DB2
3/20/2019 1:35:31
3/20/2019 2:35:31Differential
1000.21
1000.21
18
1/20/2016 2:23:31SIMPLE
C:\backup\db2_Diff_2019-03-20-13531.dif
domain\sqlaccount
SQL
DB23/17/2019 4:44:32
3/17/2019 5:44:32Full
1.3
1.3
1
2/20/2014 4:15:00SIMPLE
C:\backup\db2_Full_2019-03-17-44432.bak
domain\sqlaccount
SQL
DB23/20/2019 6:00:10
3/20/2019 7:00:10LOG
1.04
1.04
1
1/20/2016 5:32:31FULL
C:\backup\db2_DLog_2019-03-20-32.trn
domain\sqlaccount
SQL
DB13/20/2019 5:00:30
3/20/2019 6:00:30LOG1.43
1.43
4
6/20/2016 9:35:31SIMPLE
C:\backup\db1_DLog_2019-03-20-10.trn
domain\sqlaccount
SQL
DB13/20/2019 2:00:13
3/20/2019 3:00:13LOG
2.44
2.44
5
3/20/2015 1:32:30FULL
C:\backup\db1_DLog_2019-03-20-30.trn
domain\sqlaccount
SQL
DB23/20/2019 4:00:00
3/20/2019 5:00:00LOG2.00
2.00
3
8/12/2017 9:25:33FULL
C:\backup\db2_DLog_2019-03-20-13.trn
domain\sqlaccount
SQL
DB13/20/2019 3:00:13
3/20/2019 4:00:13LOG5.32
5.32
6
4/17/2016 5:17:44FULL
C:\backup\db1_DLog_2019-03-20-00.trn
domain\sqlaccount

The TSQL Code to get Backup Information

Here is the TSQL query:



SELECT 
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Source_Server, 
B.database_name, 
B.backup_start_date, 
B.backup_finish_date, 
CASE B.type 
WHEN 'D' THEN 'Full' 
WHEN 'L' THEN 'Log' 
WHEN 'I' THEN 'Differential' 
WHEN 'F' THEN 'File or filegroup'
WHEN 'G' THEN 'Differential File'
WHEN 'Q' THEN 'Differential Partial'
WHEN 'P' THEN 'Partial'
END AS backup_type, 
CAST(B.backup_size/1024.0/1024/1024 AS DECIMAL(10, 2)) AS Backup_Size_MB, 
CAST(B.Compressed_Backup_Size/1024.0/1024/1024 AS DECIMAL(10, 2)) AS Compressed_Backup_Size_MB, 
DATEPART(SECOND,B.backup_finish_date - B.backup_start_date) AS Duration_Seconds,
B.database_creation_date, 
B.recovery_model, 
A.physical_device_name, 
B.user_name
FROM msdb.dbo.backupmediafamily A
INNER JOIN msdb.dbo.backupset B ON A.media_set_id = B.media_set_id 
ORDER BY 
B.database_name, 
B.backup_finish_date 

Query Explanation

SQL Server stores backup information in the MSDB system database. The function SERVERPROPERTY('Servername') is used to get the backup source server name. The type column from the table dbo.backupset is used with CASE statement to get the backup type.

The column backup_size is stored in bytes, to get the megabyte equivalent, we divided as follows backup_size/1024.0/1024/1024. We did the same thing for the Compressed_Backup_Size column. To get the backup duration in seconds, we take the difference between the backup start and end dates and then get the seconds part of the returned date as follows DATEPART(SECOND,B.backup_finish_date - B.backup_start_date) AS Duration_Seconds.

Emeka Okeke@sqldibia+ Follow
0
Emeka Okeke+ Follow
locationPennsburg, PennsylvaniajoinedDec 11, 2019

More from @sqldibia