Database Snapshots (SQL Server)

Starting with SQL Server 2016 SP1, all editions now support the database snapshot feature.

A database snapshot provides a static, read-only image of a SQL Server database at a specific point in time. This snapshot remains transactionally consistent with the source database as it existed at the moment the snapshot was taken. Note that the snapshot must reside on the same server instance as its original database.

CREATE DATABASE <database_snapshot_name>
ON (
  NAME = <logical_file_name>, FILENAME = '<os_file_name>'
) [ ,...n ]
AS SNAPSHOT OF <source_database_name>
  

Note: The .ss file extension shown in the examples is user-defined and not mandatory.

CREATE DATABASE AdventureWorks_dbss1800 ON  
( NAME = AdventureWorks, FILENAME =  
'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\AdventureWorks_data_1800.ss' )  
AS SNAPSHOT OF AdventureWorks;  
GO
  

Restoring the Original Database Using a Snapshot

RESTORE DATABASE <database_name> 
FROM DATABASE_SNAPSHOT = <database_snapshot_name>
  

Further Reading:

• Understanding Database Snapshots vs. Backups in SQL Server
• Create a Database Snapshot (Microsoft Docs)

Restore database in SQL Server TSQL Statement

In this post, I will demonstrate how to retrieve backup file details and relevant information using SQL queries. The query below fetches the latest 100 backup records ordered by date.

SELECT TOP 100
    DatabaseName = bs.database_name,
    BackupStartDate = bs.backup_start_date,
    CompressedBackupSize = bs.compressed_backup_size,
    ExpirationDate = bs.expiration_date,
    BackupSetName = bs.name,
    RecoveryModel = bs.recovery_model,
    ServerName = bs.server_name,
    CASE bs.[type]
        WHEN 'D' THEN 'Full'
        WHEN 'I' THEN 'Differential'
        WHEN 'L' THEN 'Transaction Log'
        WHEN 'F' THEN 'File or filegroup'
        WHEN 'G' THEN 'Differential file'
        WHEN 'P' THEN 'Partial'
        WHEN 'Q' THEN 'Differential partial'
        ELSE bs.[type]
    END AS BackupType,
    LogicalDeviceName = bmf.logical_device_name,
    PhysicalDeviceName = bmf.physical_device_name,
    'RESTORE LABELONLY FROM DISK = ''' + bmf.physical_device_name + ''' WITH CHECKSUM ' AS ChkSQL,
    'RESTORE ' + CASE bs.[type]
        WHEN 'D' THEN 'DATABASE'
        WHEN 'I' THEN 'DATABASE'
        WHEN 'L' THEN 'LOG'
    END + ' ' + bs.database_name + ' FROM DISK = ''' + bmf.physical_device_name + ''' WITH NORECOVERY' AS 'RestoreSQL'
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE bs.database_name = '<Database Name>'
ORDER BY bs.backup_start_date DESC;

Make sure to replace `` with the actual database name, or simply omit this part from the query.

The following query extracts backup media information based on the backup file name retrieved from the previous query:

RESTORE LABELONLY FROM DISK = 'I:\\MSSQL\\Backup\\dbname\\dbname_backup_2020_12_04_090003_0380615.trn' WITH CHECKSUM

This query will retrieve file details from the backup file name provided:

RESTORE FILELISTONLY FROM DISK = 'I:\\MSSQL\\Backup\\DBNAME\\DBNAME_backup_2020_12_03_200002_1858707.bak'

The following query provides the full backup header information for all backups stored on a specific device in SQL Server:

RESTORE HEADERONLY FROM DISK = N'I:\\MSSQL\\Backup\\AdventureWorks-FullBackup.bak';

Here’s a query that creates a temporary table and stores file information from the backup file. This can be useful if you want to restore the database to a different file path.

CREATE TABLE #RestoreFileList (
    RowID INT IDENTITY(1, 1),
    LogicalName VARCHAR(250),
    PhysicalName VARCHAR(500),
    [Type] VARCHAR(2),
    FileGroupName VARCHAR(100),
    Size VARCHAR(100),
    MaxSize VARCHAR(100),
    FileId INT,
    CreateLSN VARCHAR(250),
    DropLSN VARCHAR(250),
    UniqueId VARCHAR(250),
    ReadOnlyLSN VARCHAR(250),
    ReadWriteLSN VARCHAR(250),
    BackupSizeInBytes VARCHAR(250),
    SourceBlockSize INT,
    FileGroupId INT,
    FileGroupGUID VARCHAR(250),
    DifferentialBaseLSN VARCHAR(250),
    DifferentialBaseGUID VARCHAR(250),
    IsReadOnly VARCHAR(2),
    IsPresent VARCHAR(2),
    TDEThumbPrint VARCHAR(250)
);

INSERT INTO #RestoreFileList (
    LogicalName,
    PhysicalName,
    [Type],
    FileGroupName,
    Size,
    MaxSize,
    FileId,
    CreateLSN,
    DropLSN,
    UniqueId,
    ReadOnlyLSN,
    ReadWriteLSN,
    BackupSizeInBytes,
    SourceBlockSize,
    FileGroupId,
    FileGroupGUID,
    DifferentialBaseLSN,
    DifferentialBaseGUID,
    IsReadOnly,
    IsPresent,
    TDEThumbPrint
)
EXEC ('RESTORE FILELISTONLY FROM DISK = ''I:\\MSSQL\\Backup\\DBNAME\\DNNAME_backup_2020_12_03_200002_1858707.bak''');

The following query generates a restore script with the specified path. You should replace `@newPath` and the backup filename as per your requirements. Depending on your SQL Server version, you might need to adjust the columns accordingly.

DECLARE @dataFile VARCHAR(1000) = '', @newPath VARCHAR(100) = 'c:\\testpat\\';
SET @dataFile = 'RESTORE DATABASE [signphoto] FROM DISK = N''I:\\MSSQL\\signphoto.bak'' WITH FILE = 1, ';
SELECT @dataFile = @dataFile + 'MOVE' + '''' + LogicalName + '''' + ' TO '
 + '''' + @newPath + '\\' + RIGHT(PhysicalName, CHARINDEX('\\', REVERSE(PhysicalName)) - 1) + ''',' + CHAR(13)
FROM #RestoreFileList
ORDER BY FileId;
PRINT @dataFile + ', NOUNLOAD, STATS = 5';

The generated restore script will look like this:

RESTORE DATABASE [signphoto] FROM DISK = N'I:\\MSSQL\\signphoto.bak' WITH FILE = 1, MOVE 'signphoto' TO 'c:\\testpat\\signphoto.mdf',
MOVE 'signphoto_log' TO 'c:\\testpat\\signphoto_log.ldf', NOUNLOAD, STATS = 5;

Additional References:

Restore HeaderOnly Documentation

Restore FileListOnly Documentation

Related blog posts:

Backup History Using T-SQL

Backup History

How to find a backup task by percentage complete

How to find a backup task by percentage complete

In this post, I will demonstrate how to track the completion of a backup task using a percentage indicator.

SELECT query = a.text, start_time, percent_complete,
    eta = dateadd(second, estimated_completion_time/1000, getdate())
FROM sys.dm_exec_requests r
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command LIKE 'BACKUP%'

For more related information, check out the following resources:

Find Databases Without Backup

Backup History Using T-SQL

Restore Database

SQL Server find database without backup from a given date

SQL Server finds a database without a backup from a given date

Below is a sample SQL query that retrieves the list of databases that have not had a backup since a specific date:

SELECT S.NAME AS database_name,
       'No Backups' AS [Backup Age (Hours)]
FROM master.dbo.sysdatabases S
LEFT JOIN msdb.dbo.backupset B
       ON S.name = B.database_name
       AND B.backup_start_date > '20201101'
WHERE B.database_name IS NULL 
      AND S.name <> 'tempdb'
ORDER BY B.database_name;

Additional Resources:

Backup History

Restore Database

Backup History Using TSQL

Backup History using TSQL

Using SQL to Retrieve Backup History Details

By querying system objects in SQL, you can retrieve detailed information about your backup history. This includes tables like Backupmediafamily and Backupset, which provide insight into backup sets and media families.

Understanding Key Tables:

• Backupmediafamily: Stores details for each media family. If a media family exists in a mirrored media set, there will be a separate row for each mirror. This data is stored in the msdb database.
• Backupset: Contains one row for each backup set. A backup set represents a successful backup operation, and statements like RESTORE, RESTORE FILELISTONLY, and RESTORE HEADERONLY act on a single backup set within the media set.

Find Backup History from the Last Day

If you're looking to retrieve backup history from the past 24 hours, use the following SQL query:

SELECT 
    CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
    bs.server_name, 
    BS.database_name, 
    BS.backup_start_date, 
    BS.backup_finish_date, 
    DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) AS 'Duration (sec)', 
    CASE BS.type  
        WHEN 'D' THEN 'Database'  
        WHEN 'L' THEN 'Log'  
        WHEN 'I' THEN 'Differential database'
        WHEN 'F' THEN 'File/Filegroup'   
        WHEN 'G' THEN 'Differential File'  
        WHEN 'P' THEN 'Partial'  
        WHEN 'Q' THEN 'Differential partial'   
    END AS backup_type, 
    ROUND(((bs.backup_size/1024)/1024),2) AS 'Backup Size (MB)',
    ROUND(((bs.compressed_backup_size/1024)/1024),2) AS 'Compressed Backup Size (MB)',
    BS.backup_size, 
    BMF.physical_device_name, 
    BS.name AS backupset_name, 
    bs.is_copy_only, 
    bs.compressed_backup_size, 
    bs.recovery_model, 
    bs.is_password_protected, 
    bs.last_lsn, 
    bs.compatibility_level, 
    bs.database_version
FROM msdb.dbo.backupmediafamily BMF
INNER JOIN msdb.dbo.backupset BS ON BMF.media_set_id = BS.media_set_id 
WHERE CONVERT(datetime, BS.backup_start_date, 102) >= GETDATE()-1 
ORDER BY BS.backup_finish_date DESC

Alternative Query with Different Columns

Here's a similar query that returns the backup history with alternative column names:

SELECT 
    CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
    bs.server_name, 
    BS.database_name, 
    BS.backup_start_date, 
    BS.backup_finish_date, 
    DATEDIFF(MINUTE, bs.backup_start_date, bs.backup_finish_date) AS 'Duration (min)', 
    CASE BS.type  
        WHEN 'D' THEN 'Database'  
        WHEN 'L' THEN 'Log'  
        WHEN 'I' THEN 'Differential database'
        WHEN 'F' THEN 'File/Filegroup'   
        WHEN 'G' THEN 'Differential File'  
        WHEN 'P' THEN 'Partial'  
        WHEN 'Q' THEN 'Differential partial'   
    END AS backup_type, 
    ROUND(((bs.backup_size/1024)/1024),2) AS 'Backup Size (MB)', 
    ROUND(((bs.compressed_backup_size/1024)/1024),2) AS 'Compressed Backup Size (MB)', 
    BS.backup_size, 
    BMF.physical_device_name, 
    BS.name AS backupset_name, 
    bs.is_copy_only, 
    bs.compressed_backup_size, 
    bs.recovery_model, 
    bs.is_password_protected, 
    bs.last_lsn, 
    bs.compatibility_level, 
    bs.database_version
FROM msdb.dbo.backupmediafamily BMF
INNER JOIN msdb.dbo.backupset BS ON BMF.media_set_id = BS.media_set_id 
WHERE CONVERT(datetime, BS.backup_start_date, 102) >= GETDATE()-1 
ORDER BY BS.backup_finish_date DESC

Backup History for Databases Without Backups

This query helps identify databases that haven't had any backups:

-- Database backup not taken
SELECT 
    S.NAME AS database_name,  
    'Nobackups' AS [Backup Age (Hours)]  
FROM master.dbo.sysdatabases S 
LEFT JOIN msdb.dbo.backupset B
    ON S.name = B.database_name 
WHERE B.database_name IS NULL 
    AND S.name <> 'tempdb'
ORDER BY B.database_name

Find Most Recent Full Backup for Each Database

To get a list of the most recent full backups for all databases, use this query:

-- To get a list of the most recent full backup of all the databases:
SELECT 
    CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
    msdb.dbo.backupset.database_name,  
    MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date 
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.type = 'D' 
GROUP BY msdb.dbo.backupset.database_name

Track Backup Progress Percentage

If you're monitoring a backup, this query shows the percentage completion:

-- Backup percentage complete
SELECT 
    query = a.text, 
    start_time, 
    percent_complete,
    eta = dateadd(second, estimated_completion_time/1000, getdate())
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command LIKE 'BACKUP%'

References:

For further details, refer to the official documentation:

• Backupset Table Documentation
• Backupmediafamily Table Documentation

SQL Server database backup history

 

How to View SQL Server Backup History

Monitoring database backups is essential for maintaining data integrity and recovery readiness. In this post, we’ll look at how to retrieve backup history in SQL Server using T-SQL queries. These scripts help you analyze when your databases were last backed up, the type of backups performed, and the devices used.

---

Viewing Recent Backup History

The following script displays detailed information about all database backups from the past 30 days for a specified database:

-- Retrieve backup history for the past 30 days
SELECT 
    CONVERT(CHAR(100), SERVERPROPERTY('ServerName')) AS ServerName,
    bs.database_name,
    bs.backup_start_date,
    bs.backup_finish_date,
    bs.expiration_date,
    CASE bs.type 
        WHEN 'D' THEN 'Database' 
        WHEN 'L' THEN 'Transaction Log' 
    END AS BackupType,
    bs.backup_size,
    bf.logical_device_name,
    bf.physical_device_name,
    bs.name AS BackupSetName,
    bs.description
FROM 
    msdb.dbo.backupmediafamily AS bf
    INNER JOIN msdb.dbo.backupset AS bs ON bf.media_set_id = bs.media_set_id
WHERE 
    bs.backup_start_date >= DATEADD(DAY, -30, GETDATE()) 
    AND bs.database_name = 'myDatabase'
ORDER BY 
    bs.database_name, 
    bs.backup_finish_date;

This query pulls data from the msdb system database and provides insight into the backup type, size, device used, and more.

---

Finding the Most Recent Backup per Database

To identify the latest full backup for each database on the server, use the query below. This script returns the most recent full backup details for all databases.

-- Get the latest full backup information for each database
SELECT 
    A.Server,
    A.last_db_backup_date,
    B.backup_start_date,
    B.expiration_date,
    B.backup_size,
    B.logical_device_name,
    B.physical_device_name,
    B.backupset_name,
    B.description
FROM (
    SELECT 
        CONVERT(CHAR(100), SERVERPROPERTY('ServerName')) AS Server,
        bs.database_name,
        MAX(bs.backup_finish_date) AS last_db_backup_date
    FROM 
        msdb.dbo.backupmediafamily AS bf
        INNER JOIN msdb.dbo.backupset AS bs ON bf.media_set_id = bs.media_set_id
    WHERE 
        bs.type = 'D'
    GROUP BY 
        bs.database_name
) AS A
LEFT JOIN (
    SELECT 
        CONVERT(CHAR(100), SERVERPROPERTY('ServerName')) AS Server,
        bs.database_name,
        bs.backup_start_date,
        bs.backup_finish_date,
        bs.expiration_date,
        bs.backup_size,
        bf.logical_device_name,
        bf.physical_device_name,
        bs.name AS backupset_name,
        bs.description
    FROM 
        msdb.dbo.backupmediafamily AS bf
        INNER JOIN msdb.dbo.backupset AS bs ON bf.media_set_id = bs.media_set_id
    WHERE 
        bs.type = 'D'
) AS B 
ON 
    A.Server = B.Server 
    AND A.database_name = B.database_name 
    AND A.last_db_backup_date = B.backup_finish_date
ORDER BY 
    A.database_name;

---

These queries are helpful for DBAs who want to quickly validate the backup status of their databases or audit recent backup operations.

For more details on the backup-related dynamic management views, refer to the Microsoft Documentation on backupset.