Backing up related scripts

Original: Backing up related scripts

--View backup information within one week:

SELECT     CONVERT (CHAR (+), serverproperty (' Servername ')) as Server,    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 ' and ' the 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 joins Msdb.dbo.backupset on msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  WHERE  (CONVERT (datetime, Msdb.dbo.backupset.backup_start_date, 102) >= GETDATE ()-7)  ORDER     by Msdb.dbo.backupset.database_name,    msdb.dbo.backupset.backup_finish_date

--recent backup of each library:

---------------------------------------------------------------------------------------------most recent Database Backup for each database--------------------------------------------------------------------------------- ----------SELECT     CONVERT (CHAR (+), 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: Backupset.type = ' D ' GROUP by    msdb.dbo.backupset.database_name  ORDER by     Msdb.dbo.backupset.database_ Name

--Details of each library's most recent backup:

---------------------------------------------------------------------------------------------most recent Database Backup for each database-detailed------------------------------------------------------------------------ -------------------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 (+), 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 J OIN 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) as A left JOIN (SELECT CONVERT (CHAR (+), serverproperty (' Servername '))As Server, Msdb.dbo.backupset.database_name, Msdb.dbo.backupset.backup_start_date, Msdb.dbo.backupset.backup_fi Nish_date, Msdb.dbo.backupset.expiration_date, Msdb.dbo.backupset.backup_size, Msdb.dbo.backupmediafamily.logica L_device_name, Msdb.dbo.backupmediafamily.physical_device_name, msdb.dbo.backupset.name as Backupset_name, MSD B.dbo.backupset.description from msdb.dbo.backupmediafamily INNER joins Msdb.dbo.backupset on Msdb.dbo.backupmediafam ily.media_set_id = msdb.dbo.backupset.media_set_id WHERE msdb: Backupset.type = ' D ') as B on a.[server] = B.[server] and a.[database_name] = B.[database_name] and A.[last_db_back Up_date] = b.[backup_finish_date] ORDER by A.database_name

--a database that has not been fully backed up in the last 24 hours:

---------------------------------------------------------------------------------------------Databases Missing A Data (aka Full) back-up within past Hours------------------------------------------------------------------------- --------------------Databases with data backup over hours old SELECT CONVERT (CHAR (+), serverproperty (' Servername '    )) as Server, Msdb.dbo.backupset.database_name, MAX (msdb.dbo.backupset.backup_finish_date) as Last_db_backup_date, DATEDIFF (hh, MAX (msdb.dbo.backupset.backup_finish_date), GETDATE ()) As [Backup Age (Hours)] from Msdb.dbo.backupset W Here Msdb.dbo.backupset.type = ' D ' GROUP by Msdb.dbo.backupset.database_name have (MAX (msdb.dbo.backupset.back Up_finish_date) < DATEADD (hh,-GETDATE ())) UNION--databases without any backup history SELECT CONVERT (C HAR (+), serverproperty (' Servername ')) as Server, Master.dbo.sysdatabases.NAME as database_name, NULL as [last Da Ta backup Date], 9999 as [Backup Age (Hours)] from master.dbo.sysdatabases left JOIN msdb.dbo.backupset on master.dbo.sysdatabases.name = msdb . Dbo.backupset.database_name WHERE Msdb.dbo.backupset.database_name is NULL and Master.dbo.sysdatabases.name <> ' tempdb ' ORDER by msdb.dbo.backupset.database_name

Backing up related scripts