In this article, we’ll see the how the backup-and-restore meta-data tables store the information in the MSDB database. Also, discuss several T-SQL statements to derive most useful information with reference to data purge, database growth, backup report, restore history and more.
In this article, we’ll discuss the following topics:
- How do you delete six months old data to reduce the size of the backup and restore history tables?
- How do you get the Backup History for a specific database including the size, location, and LSN?
- How do you create and restore a marked transaction?
- How do you find the RESTORE HISTORY of the database?
- How do you list the last 30 days restore history at the instance level?
- How do you measure the database backup or database restore operation progress?
- How do you measure the database growth using backup size?
- How do you define or estimate the storage required for database backup?
- How do you get most recent database backup time for each database?
- How do you get recent database backup time for each database using PowerShell?
- How do you get recent database backup time for each database across multiple servers using PowerShell?
- How do you find the backup history with duration and compressed backup size columns?
Questions
MSDB database is a log-store and it stores a complete history of all SQL Server backup-and-restore operations.
The following table highlights the high-level detail about the backup-and-restore operation:
| System tables | Description |
| backupfile | The system table provides the most granular details of the backup file. It stores one row for each data file or log file of a database. The columns describe the file type, file group name, page size and file configuration information. |
| backupfilegroup
| The table in-house the filegroup configuration of the database. It stores one row for each filegroup in a database. |
| backupmediafamily
| It stores a row for each media family. |
| backupmediaset
| It stores a row for each backup media set. |
| backupset
| It contains a row for each backup set for successful backup. |
| logmarkhistory
| Contains one row for each marked transaction that has been committed. It is applicable to only those databases where the recovery model is set to full or bulk-logged. |
| restorefile
| It stores one row for each restored file. |
| restorefilegroup
| It stores one row for each restored filegroup. |
| restorehistory
| It stores one row for each restore operation |
| suspect_pages | It stores one row per page that failed with the error 823 or 824 See the link for more information How to perform a page level restore in SQL Server |
1. How do you delete six months old data to reduce the size of the backup and restore history tables?
To reduce the size of the backup and restore history tables, delete the entries of backup sets that are older than the specified date-time. It is recommended to run sp_delete_backuphistory frequently to clean-up the entries from the MSDB database.
|
1 2 3 4 |
USE msdb GO Declare @date Datetime = dateadd(mm,-6,getdate()) EXEC sp_delete_backuphistory @date |
If you want to remove all the entries of backup history for a specific database, run sp_delete_database_backuphistory <database name>
|
1 2 3 |
USE msdb Go sp_delete_database_backuphistory 'SQLShackDemo' |
2. How do you get the Backup History for a specific database including the size, location, and LSN?
The following T-SQL provides you the backup information and LSN details of a given database.
The LSN is viewable using the following system-tables and using the restore command:
- backupset
- backupfile
- sys.database_files;
- sys.master_files
- RESTORE HEADERONLY
In the following example, you can see that the LSN is ordered in a Zig-Zag fashion.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
SELECT bs.server_name, bs.database_name, bs.backup_start_date, bs.backup_finish_date, bs.user_name, bs.first_LSN, bs.last_LSN, CASE WHEN bs.[type] = 'D' THEN 'Full Backup' WHEN bs.[type] = 'I' THEN 'Differential Database' WHEN bs.[type] = 'L' THEN 'Log' WHEN bs.[type] = 'F' THEN 'File/Filegroup' WHEN bs.[type] = 'G' THEN 'Differential File' WHEN bs.[type] = 'P' THEN 'Partial' WHEN bs.[type] = 'Q' THEN 'Differential partial' END ,cast((bs.backup_size/1024)/1024 as numeric(8,0)) 'backup_size MB', bmf.physical_device_name from msdb.dbo.backupset bs inner join msdb.dbo.backupmediafamily bmf on bs.media_set_id=bmf.media_set_id where bs.database_name='SQLShackDemo' order by bs.backup_start_date desc |
3. How do you create and restore a marked transaction?
Marked transactions are very useful to recover the database to a logically consistent point.
To create a marked transaction and restore the marked transaction follow the steps:
-
Create full/differential database backup
1BACKUP DATABASE [SQLShackDemo] TO DISK='F:\PowerSQL\SQLShackDemo_FULL_07172018.bak' WITH COMPRESSION,STATS=10 -
Use “BEGIN TRANSACTION WITH MARK” clause to mark the transaction and perform the DML operations
12345678910111213USE [SQLShackDemo]GOBEGIN TRANSACTION UpdateBackupInfoDemoWITH MARK 'UPDATE Compatibility level';GOUPDATE dbo.BackupInfoSET compatibilitylevel=120WHERE servername='hqdbsp18';GOCOMMIT TRANSACTION UpdateBackupInfo;GO -
Back up the SQLShackDemo transaction-log
1BACKUP LOG [SQLShackDemo] TO DISK='F:\PowerSQL\SQLShackDemo_LOG_07172018.TRN' WITH COMPRESSION,STATS=10 -
To verify the marked transaction, run the following command
1SELECT * FROM MSDB.dbo.logmarkhistory
-
Restore full database backup WITH NORECOVERY option.
123456789USE MASTERGODROP DATABASE IF EXISTS [SQLShackDemo]RESTORE DATABASE [SQLShackDemo]FROM DISK='F:\PowerSQL\SQLShackDemo_FULL_07172018.bak'WITH NORECOVERY;GO -
Restore log WITH STOPATMARK option
1234RESTORE LOG [SQLShackDemo]FROM DISK='F:\PowerSQL\SQLShackDemo_LOG_07172018.TRN'WITH RECOVERY,STOPATMARK = 'UpdateBackupInfo'; -
Verify the data
1SELECT * FROM dbo.BackupInfo WHERE servername='aqdbsp18';
4. How do you find the RESTORE HISTORY of the database?
This following T-SQL provides you with information about a particular database with the restore history and source, destination, start, end time and type of the restore operation.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
USE msdb GO SELECT bs.server_name, bs.database_name Source_database_name, rh.destination_database_name, bs.backup_set_id, bs.backup_start_date, bs.backup_start_date, bs.backup_finish_date, bs.user_name, bs.first_LSN, bs.last_LSN, CASE WHEN bs.[type] = 'D' THEN 'Full Backup' WHEN bs.[type] = 'I' THEN 'Differential Database' WHEN bs.[type] = 'L' THEN 'Log' WHEN bs.[type] = 'F' THEN 'File/Filegroup' WHEN bs.[type] = 'G' THEN 'Differential File' WHEN bs.[type] = 'P' THEN 'Partial' WHEN bs.[type] = 'Q' THEN 'Differential partial' END ,bmf.physical_device_name, rh.restore_date ,rh.stop_at_mark_name from backupset bs inner join backupmediafamily bmf on bs.media_set_id=bmf.media_set_id inner join restorehistory rh on bs.backup_set_id=rh.backup_set_id where bs.database_name='SQLShackDemo' order by rh.restore_date desc |
5. How do you list the last 30 days restore history at the instance level?
The following T-SQL provides you a list of last 30 days data of database restore history.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
SELECT b.database_name source_database_name, rh.destination_database_name, rh.user_name AS [Restored By], CASE WHEN rh.restore_type = 'D' THEN 'Database Restore' WHEN rh.restore_type = 'F' THEN 'File Restore' WHEN rh.restore_type = 'G' THEN 'Filegroup Restore' WHEN rh.restore_type = 'I' THEN 'Differential Restore' WHEN rh.restore_type = 'L' THEN 'Log Restore' ELSE rh.restore_type END AS [Restore Type], rh.restore_date, bm.physical_device_name SourceDevice ,rf.destination_phys_name destDevice FROM msdb.dbo.restorehistory rh INNER JOIN msdb.dbo.backupset b ON rh.backup_set_id = b.backup_set_id INNER JOIN msdb.dbo.restorefile r ON rh.restore_history_id = r.restore_history_id INNER JOIN msdb.dbo.backupmediafamily bm ON bm.media_set_id = b.media_set_id WHERE rh.restore_date >= DATEADD(dd, -30,getdate()) ORDER BY rh.restore_history_id desc |
Output:
6. How do you measure the database backup or database restore operation progress?
To measure the backup operation progress or estimate the time and percentage completed, you can query the DMV—sys.dm_exec_requests.
This script provides the output with backup estimation time and percentage completed.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT command, s.text, er.start_time, er.percent_complete, CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hr(s), ' + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min(s), ' + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' ss' as running_time, CAST((estimated_completion_time/3600000) as varchar) + ' hr(s), ' + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min(s), ' + CAST((estimated_completion_time %60000)/1000 as varchar) + ' ss' as est_time_to_go, dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time FROM sys.dm_exec_requests er CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) s WHERE er.command in ('BACKUP DATABASE', 'BACKUP LOG', 'RESTORE DATABASE', 'RESTORE LOG') |
7. How do you measure the database growth using backup size?
You can construct the T-SQL using backup system table to analyze the growth of the database over a given timeframe. The T-SQL script used in the below-mentioned link is used to calculate the capacity planning of the databases. The metrics are useful for capacity planning and forecasting.
Backup and Restore (or Recovery) strategies for SQL Server database
8. How do you define or estimate the storage required for database backup?
You can refer the T-SQL to get very detailed information about the database backup history. It also talks about capturing the baseline database growth metrics.
9. How do you get most recent database backup time for each database?
The following T-SQL provides the most recent backup completion time of all databases along with the database name
|
1 2 3 4 5 |
SELECT sd.Name AS DatabaseName, COALESCE(CONVERT(VARCHAR(12), MAX(bs.backup_finish_date), 101),'-') AS LastBackUpTime FROM sys.sysdatabases sd LEFT OUTER JOIN msdb.dbo.backupset bs ON bs.database_name = sd.name GROUP BY sd.Name |
10. How do you get recent database backup time for each database using PowerShell?
The following PowerShell script provides the most recent backup completion time of all databases with the database name
- Load the SQLServer Module
- Instantiate the SMO class library
- Invoke the database property
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
TRY { If (Get-Module SQLServer -ListAvailable) { Write-Verbose "Preferred SQLServer module found" } else { Install-Module -Name SqlServer } } CATCH { Write-Host "Check the Module and version" } $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') 'hqdbt01' $databases=$srv.Databases $databases | SELECT Name,LastBackupDate, LastLogBackupDate | Format-Table -autosize |
11. How do you get recent database backup time for each database across multiple servers using PowerShell?
This can be done using looping construct in PowerShell with very few lines of code.
- Load the SQLServer Module
- List the SQL Server instances
- Use looping construct to Instantiate the SMO class library and list the database properties
|
1 2 3 |
Install-Module -Name SqlServer $List = @('aqdbsp18','aqdbs19','adbs201') $list|%{(New-Object -TypeName Microsoft.SQLServer.Management.Smo.Server($ServerName)).Databases| SELECT Name,LastBackupDate, LastLogBackupDate | Format-Table -autosize} |
12. How do you find the backup history with duration and compressed backup size columns?
The following T-SQL get the latest successful backups and it includes columns such as database name, backup start time, backup end time, a derived column named duration (mins), backup file location, type of the backup, backup size, and compressed backup size (if used)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
SELECT bs.database_name DatabaseName, bs.backup_start_date Backup_Start_Date, bs.backup_finish_date Backup_Finished_Date, DATEDIFF(MINUTE, bs.backup_start_date, bs.backup_finish_date) Duration_Mins, bmf.physical_device_name Backup_File_Location, CASE WHEN bs.[type] = 'D' THEN 'Full_Backup' WHEN bs.[type] = 'I' THEN 'Differential_Backup' WHEN bs.[type] = 'L' THEN 'Log_Backup' WHEN bs.[type] = 'F' THEN 'File/Filegroup backup' WHEN bs.[type] = 'G' THEN 'DifferentialFile_Backup' WHEN bs.[type] = 'P' THEN 'Partial_Backup' WHEN bs.[type] = 'Q' THEN 'Differentialpartial_Backup' END 'Backup_Type', ROUND(((bs.backup_size/1024)/1024),2) Backup_SizeMB, ROUND(((bs.compressed_backup_size/1024)/1024),2) CompressedBackup_SizeMB FROM msdb.dbo.backupmediafamily bmf INNER JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id ORDER BY bs.backup_start_date DESC |
Output:
That’s all for now…
Wrapping up
Thus far, we’ve covered most of the concepts of “database backup-and-restore” operations. Please refer the TOC for more information.
Table of contents
References
My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.
View all posts by Prashanth Jayaram
- Stairway to SQL essentials - April 7, 2021
- A quick overview of database audit in SQL - January 28, 2021
- How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021