SQL Server DBA Tools: Database Backups

Query to List Last Backup Date for Every Database

This query returns the last full, differential, and log backup date for every database on a SQL Server instance, along with data and log file sizes.

SELECT
    [DatabaseID]      = d.database_id
    , [Database]      = d.[Name]
    , [Status]        = d.state_desc
    , [Recovery]      = d.Recovery_Model_Desc
    , [LastFullBackup] = (
        SELECT CONVERT(char(10), MAX(backup_finish_date), 101)
               + RIGHT(CONVERT(char(19), MAX(backup_finish_date), 100), 8)
        FROM msdb.dbo.backupset
        WHERE database_name = d.name AND type = 'D'
      )
    , [LastDiffBackup] = (
        SELECT CONVERT(char(10), MAX(backup_finish_date), 101)
               + RIGHT(CONVERT(char(19), MAX(backup_finish_date), 100), 8)
        FROM msdb.dbo.backupset
        WHERE database_name = d.name AND type = 'I'
      )
    , [LastLogBackup] = (
        SELECT CONVERT(char(10), MAX(backup_finish_date), 101)
               + RIGHT(CONVERT(char(19), MAX(backup_finish_date), 100), 8)
        FROM msdb.dbo.backupset
        WHERE database_name = d.name AND type = 'L'
      )
FROM sys.databases d
ORDER BY d.[Name]

Query to List Backup History for a Database

SELECT
    s.database_name
    , m.physical_device_name
    , CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize
    , CAST(DATEDIFF(second, s.backup_start_date, s.backup_finish_date) AS VARCHAR(4)) + ' Seconds' AS TimeTaken
    , s.backup_start_date
    , CASE s.[type]
        WHEN 'D' THEN 'Full'
        WHEN 'I' THEN 'Differential'
        WHEN 'L' THEN 'Transaction Log'
      END AS BackupType
    , s.server_name
    , s.recovery_model
FROM msdb.dbo.backupset s
    JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = DB_NAME() -- Remove this line for all databases
  AND s.[type] <> 'L'
ORDER BY backup_start_date DESC

Executing Backups

Full Backup to Local Drive

BACKUP DATABASE [master] TO DISK = 'D:\Backup\master_FULL.bak'
WITH STATS, CHECKSUM, COMPRESSION

Full Backup to Azure Blob Storage (SAS)

BACKUP DATABASE [master] TO URL = 'https://[storageaccount].blob.core.windows.net/[container]/master_FULL.bak'
WITH CREDENTIAL = 'SQLAzureStorageSAS', STATS, CHECKSUM, COMPRESSION

Differential Backup

BACKUP DATABASE [master] TO DISK = 'D:\Backup\master_DIFF.bak'
WITH DIFFERENTIAL, STATS, CHECKSUM, COMPRESSION

Transaction Log Backup

BACKUP LOG [YourDatabase] TO DISK = 'D:\Backup\YourDatabase_LOG.bak'
WITH STATS, CHECKSUM, COMPRESSION

Datazip provides 24/7 managed SQL Server backup monitoring and Azure cloud backup setup. Call 1-833-DATAZIP or visit www.SQLBrainBox.com for our monitoring tool.