Efficient and regular database backups are crucial for data security and business continuity. As a DBA, it’s essential to monitor backups regularly and ensure that all databases are being backed up properly. Two SQL scripts that can help with this task are “Database Backups for all Databases for the last 7 days” and “Most Recent Database Backup for Each Database.”
Using these scripts regularly as part of your day-to-day routine can help ensure proper backups and reduce the risk of data loss. These scripts can help you quickly identify at-risk databases that need more frequent backups, and monitor backup status to identify issues that need to be addressed. Regular use of these scripts can also help improve database performance by reducing index fragmentation and improving backup efficiency.
By incorporating these scripts into your routine, you can maintain database health and performance, reduce the risk of data loss, and ensure that all databases are being backed up properly.
Database Backups for all Databases for the last 7 days
---------------------------------------------------------------------------------
--Database Backups for all Databases for the last 7 days
---------------------------------------------------------------------------------
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS ServerName
, 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
/* Note: Howsdata replaces the greater than symbol with '>' for security reasons to prevent malicious attacks.
If you encounter any issues, I am working on a workaround and will update this soon.
In the meantime, please use the correct syntax to ensure that the code runs without errors. */
(CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
--AND msdb.dbo.backupset.database_name in('Database_Name_Here') --Enable this to filter by database
ORDER BY
msdb.dbo.backupset.database_name
, msdb.dbo.backupset.backup_finish_date DESC
/* The script above retrieves information on database backups for all databases for the last 7 days. */
/* To use this script, simply copy and paste it into a new query window in SQL Server Management Studio,
then execute the script to view the backup details. */
/* You can uncomment the line that filters by database name to limit the results to a specific database. */
-------------------------------------------------------------------------------------------
--Most Recent Database Backup for Each Database
-------------------------------------------------------------------------------------------
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS ServerName
, 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..backupset.type = 'D'
GROUP BY
msdb.dbo.backupset.database_name
ORDER BY
msdb.dbo.backupset.database_name
/* The SQL script above retrieves information on the most recent database backup for each database. */
/* To use this script, simply copy and paste it into a new query window in SQL Server Management Studio,
then execute the script to view the backup details. The script can help you quickly determine if all databases
are being backed up regularly and identify any at-risk databases that may need more frequent backups. */
Source: mssqltips
