As a SQL Server database administrator, one of the most critical tasks is to ensure that regular backups are taken to protect the data in case of any unforeseen events. However, it is equally important to understand the time taken between backups and the potential data loss that may occur.

Inspired by a real-life scenario, where a corrupt backup led to data loss, I have created a script that can help you identify the time taken between backups and estimate the potential data loss. Let’s dive into the details.

The Script

Here is a rudimentary script that you can use to address this requirement:

CREATE TABLE #backupset (
    backup_set_id INT,
    database_name NVARCHAR(128),
    backup_finish_date DATETIME,
    TYPE CHAR(1),
    next_backup_finish_date DATETIME
);

INSERT INTO #backupset (
    backup_set_id,
    database_name,
    backup_finish_date,
    TYPE
)
SELECT
    backup_set_id,
    database_name,
    backup_finish_date,
    TYPE
FROM
    msdb.dbo.backupset WITH (NOLOCK)
WHERE
    backup_finish_date >= DATEADD(dd, -14, GETDATE())
    AND database_name NOT IN ('master', 'model', 'msdb', 'ReportServer', 'ReportServerTempDB');

CREATE CLUSTERED INDEX CL_database_name_backup_finish_date ON #backupset (database_name, backup_finish_date);

UPDATE #backupset
SET next_backup_finish_date = (
    SELECT TOP 1 backup_finish_date
    FROM #backupset bsNext
    WHERE bs.database_name = bsNext.database_name
    AND bs.backup_finish_date < bsNext.backup_finish_date
    ORDER BY bsNext.backup_finish_date
)
FROM #backupset bs;

SELECT
    bs1.database_name,
    MAX(DATEDIFF(mi, bs1.backup_finish_date, bs1.next_backup_finish_date)) AS max_minutes_of_data_loss,
    'SELECT bs.database_name, bs.type, bs.backup_start_date, bs.backup_finish_date, DATEDIFF(mi, COALESCE((SELECT TOP 1 bsPrior.backup_finish_date FROM msdb.dbo.backupset bsPrior WHERE bs.database_name = bsPrior.database_name AND bs.backup_finish_date > bsPrior.backup_finish_date ORDER BY bsPrior.backup_finish_date DESC), ''1900/1/1''), bs.backup_finish_date) AS minutes_since_last_backup, DATEDIFF(mi, bs.backup_start_date, bs.backup_finish_date) AS backup_duration_minutes, CASE DATEDIFF(ss, bs.backup_start_date, bs.backup_finish_date) WHEN 0 THEN 0 ELSE CAST((bs.backup_size / (DATEDIFF(ss, bs.backup_start_date, bs.backup_finish_date)) / 1048576) AS INT) END AS throughput_mb_sec FROM msdb.dbo.backupset bs WHERE database_name = ''' + database_name + ''' AND bs.backup_start_date > DATEADD(dd, -14, GETDATE()) ORDER BY bs.backup_start_date' AS more_info_query
FROM #backupset bs1
GROUP BY bs1.database_name
ORDER BY bs1.database_name;

DROP TABLE #backupset;

This script creates a temporary table to store the backup information. It then populates the table with the backup details from the last two weeks, excluding system databases. The script also calculates the time taken between backups and estimates the potential data loss.

Using the Script

To use this script, simply execute it in your SQL Server Management Studio. It will provide you with a list of databases and the maximum amount of time between backups over the last two weeks. This information can help you determine if your backup strategy is sufficient to minimize data loss.

Feel free to modify the script according to your specific requirements. If you make any changes, please share them in the comments section so that others can benefit from your modifications.

Additional Resources

If you found this script helpful, you may also be interested in the following related queries:

• Get Database Backup History for a Single Database
• Finding Last Backup Time for All Databases – Last Full, Differential, and Log Backup – Optimized

These queries provide additional insights into database backup history and can further enhance your backup monitoring and management.

Remember, regular backups are crucial for data protection, and understanding the time taken between backups is essential to minimize potential data loss. Use this script as a starting point to evaluate your backup strategy and make any necessary adjustments.

Happy backup monitoring!