This is a post on how to get the last backup dates and times for all databases on a SQL Server Instance.
Database backups are as important as the availability of your server. If your database gets corrupted, or there’s an accidental data deletion and you need to recover data, then we must rely on backups to get the data back. If you have no backups, you have no way to recover your lost data.
This demo post includes the following SQL scripts to help gain database backup history information for review:
# Get Most Recent Database Backup Info
# Get All Database Backup Info
If you discover a database without an associated backup you should consider reviewing the situation.
Get Most Recent Database Backup Info
The following script returns the most recent Full, Differential & TLog Backup times for all databases on a SQL Server Instance.
We can see from the above screenshot all databases on this SQL Server Instance have been backed up in the last 30 days. Only the demoBlog has had recent Differential and TLog Backups.
The type column within the backupset table gives us a way of checking whether the backup was a Full, Differential or Transaction Log.
Get All Database Backup Info
This script is more useful if you are reviewing the history of database backups, ensuring they are being backed up on a regular schedule/interval.
The following script will show all backups in the log, in the past 2 months.
You can simply remove the WHERE Clause to bring back the full history if necessary.
The above screenshot includes examples of Full, Differential & Transaction Log backups. This data can be very valuable for verifying backup chains are not broken, as well as backup sizes (including backup compression savings).
Leave a Reply