How do I find backup history in SQL Server? | Last backup of SQL database in sql server

This article describes about,

How do I find backup history in SQL Server? or Last backup of SQL database in sql server

What are Backups in SQL Server?

SQL Server contains three type of backup which help you to recover data in case of disaster. SQL Server has FULL, Differential and T log Backup.

Full Backup : This is main database backup type in SQL Server. Without full backup you can restore other database backup. Full backup contain all data in database. For any database restore you should have good full backup file.

Differential Backup : This backup contain all data from last full backup. This backup help you manage backup strategy for large size databases.

Transaction log Backup : This backup is incremental backup which is use to recover data in point of time recovery. T-log backup only taken in FULL recovery model. To restore transaction log backup we need T-log backup chain.

Backup history SQL server:

Backup history help to find out all backup taken in past for SQL server. All history stored in MSDB system database in SQL Server. Below tables used to store historical data about all past backups.

• dbo.backupset: provides information concerning the most-granular details of the backup process
• dbo.backupmediafamily: provides metadata for the physical backup files as they relate to backup sets
• dbo.backupfile: this system view provides the most-granular information for the physical backup files

How to get backup history from SQL Server:

Below script help you to get all details of past backups taken on SQL Server database.

Connect to SQL Server instance and executed below query to get required backup history details from SQL Server. You can filter history based on database name or Backup date or Backup type.

Query to check backup history in SQL server or Last backup of SQL database in sql server

SELECT CONVERT(CHAR(100),SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,msdb.dbo.backupset.expiration_date,CASE
msdb..backupset.type WHEN 'D' THEN'Database' WHEN 'L' THEN 'Log' END AS backup_type,
msdb.dbo.backupset.backup_size,msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description FROM msdb.dbo.backupmediafamily INNER JOIN
msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id =msdb.dbo.backupset.media_set_id
--WHERE msdb.dbo.backupset.is_copy_only = 1
ORDER BY msdb.dbo.backupset.database_name,msdb.dbo.backupset.backup_finish_date

How To Configure Database Mail on azure SQL managed instance |SQL Server Database Mail