SQL Server Backup Check

Hi,

To summarize the backup we can say, Copy of Stored Data to Disk or Tape In case of unexpected errors or disasters, this copy can be defined as Restoring Data and preventing data loss.

In large-scale companies or in important projects, the loss of data causes material and reputation-causing events, so companies must always keep more than one copy of the Databases or Files for one or more.

The causes of data loss that may occur in companies and systems are as follows.

• Media Failure
• User-Based Errors. Drop table
• Disk Errors. Disk corruption
• Natural disasters. Earthquake floods.

Backup should be checked everyday by SQL Server DBA with following script.

SELECT DB.name AS Database_Name
,MAX(DB.recovery_model_desc) AS Recovery_Model
,MAX(BS.backup_start_date) AS Last_Backup
,MAX(CASE WHEN BS.type = 'D'
THEN BS.backup_start_date END)
AS Last_Full_backup
,SUM(CASE WHEN BS.type = 'D'
THEN 1 END)
AS Count_Full_backup
,MAX(CASE WHEN BS.type = 'L'
THEN BS.backup_start_date END)
AS Last_Log_backup
,SUM(CASE WHEN BS.type = 'L'
THEN 1 END)
AS Count_Log_backup
,MAX(CASE WHEN BS.type = 'I'
THEN BS.backup_start_date END)
AS Last_Differential_backup
,SUM(CASE WHEN BS.type = 'I'
THEN 1 END)
AS Count_Differential_backup
,MAX(CASE WHEN BS.type = 'F'
THEN BS.backup_start_date END)
AS LastFile
,SUM(CASE WHEN BS.type = 'F'
THEN 1 END)
AS CountFile
,MAX(CASE WHEN BS.type = 'G'
THEN BS.backup_start_date END)
AS LastFileDiff
,SUM(CASE WHEN BS.type = 'G'
THEN 1 END)
AS CountFileDiff
,MAX(CASE WHEN BS.type = 'P'
THEN BS.backup_start_date END)
AS LastPart
,SUM(CASE WHEN BS.type = 'P'
THEN 1 END)
AS CountPart
,MAX(CASE WHEN BS.type = 'Q'
THEN BS.backup_start_date END)
AS LastPartDiff
,SUM(CASE WHEN BS.type = 'Q'
THEN 1 END)
AS CountPartDiff
FROM sys.databases AS DB
LEFT JOIN
msdb.dbo.backupset AS BS
ON BS.database_name = DB.name
WHERE ISNULL(BS.is_damaged, 0) = 0-- exclude damaged backups 
GROUP BY DB.name
ORDER BY Last_Backup desc;

Do you want to learn Microsoft SQL Server DBA Tutorials for Beginners, then read the following articles.

https://ittutorial.org/sql-server-tutorials-microsoft-database-for-beginners/