Common Table Expressions (CTE) for retrieving Last Backup information 





--Get List of Last Full Backup or No Backup

;WITH LAST_FULL_BACKUP_LIST
AS
(
SELECT
SYSDBLIST.name AS Name,
MAX(BUSETS.backup_finish_date) AS Last_Backup_Finish_DateTime
FROM 
MASTER.sys.databases AS SYSDBLIST
LEFT OUTER JOIN
msdb.dbo.backupset AS BUSETS
ON
SYSDBLIST.name = BUSETS.database_name
WHERE
SYSDBLIST.name<>'TempDB'
AND
BUSETS.[type] ='D' OR BUSETS.[type] IS NULL
GROUP BY 
SYSDBLIST.name
)
,


--Get List of Last Differential Backup


LAST_DIFFERENTIAL_BACKUP_LIST
AS
(
SELECT
SYSDBLIST.name AS Name,
MAX(BUSETS.backup_finish_date) AS Last_Backup_Finish_DateTime
FROM 
MASTER.sys.databases AS SYSDBLIST
LEFT OUTER JOIN
msdb.dbo.backupset AS BUSETS
ON
SYSDBLIST.name = BUSETS.database_name
WHERE
SYSDBLIST.name<>'TempDB'
AND
BUSETS.[type] ='I' 
GROUP BY 
SYSDBLIST.name
)
,


--Get List of Last Log Backup

LOG_BACKUP_LIST
AS
(
SELECT
SYSDBLIST.name AS Name,
BUSETS.backup_finish_date AS Backup_Finish_DateTime,
ROUND(((BUSETS.backup_size/1024)/1024),2) AS Backup_Size_MB,
ROW_NUMBER() OVER(Partition by SYSDBLIST.name ORDER BY BUSETS.backup_finish_date DESC) AS RevOrderBuDate
FROM 
MASTER.sys.databases AS SYSDBLIST
LEFT OUTER JOIN
msdb.dbo.backupset AS BUSETS
ON
SYSDBLIST.name = BUSETS.database_name
WHERE
SYSDBLIST.name<>'TempDB'
AND
BUSETS.[type] ='L' 
)
,
LAST_LOG_BACKUP_LIST
AS
(SELECT
Name,
Backup_Finish_DateTime AS Last_Backup_Finish_DateTime,
Backup_Size_MB AS Log_Backup_Size_MB
FROM
LOG_BACKUP_LIST
WHERE
RevOrderBuDate=1
)


SELECT SERVERPROPERTY('Servername') AS ServerName,
SYSDBLIST.name AS Database_Name,
SYSDBLIST.Compatibility_level,
SYSDBLIST.Recovery_model,
SYSDBLIST.Recovery_model_desc,
BUSETS.database_creation_date AS Database_Create_Date,
CASE WHEN BUSETS.backup_set_id IS NULL THEN 'NO Backup' ELSE 'Backup Complete' END AS Backup_Completed,
BUSETS.backup_start_date AS Backup_Start_DateTime,
BUSETS.backup_finish_date AS Backup_Finish_DateTime,
DATEDIFF(MINUTE, BUSETS.backup_start_date, BUSETS.backup_finish_date) AS Duration_Min,
(DATEDIFF(DAY,BUSETS.backup_finish_date,GETDATE())) AS Days_Since_Last_Backup,
LASTDIFFBACKUP.Last_Backup_Finish_DateTime AS Last_Differential_Finish_DateTime,
(DATEDIFF(DAY,LASTDIFFBACKUP.Last_Backup_Finish_DateTime ,GETDATE())) AS Days_Since_Last_Differential_Backup,
LASTLOGBACKUP.Last_Backup_Finish_DateTime AS Last_Log_Finish_DateTime,
(DATEDIFF(DAY,LASTLOGBACKUP.Last_Backup_Finish_DateTime,GETDATE()))AS Days_Since_Last_Log_Backup,
LASTLOGBACKUP.Log_Backup_Size_MB AS LOG_Backup_Size_MB,
CASE 
WHEN BUSETS.[type] = 'D' THEN 'Full Backup' 
WHEN BUSETS.[type] = 'I' THEN 'Differential Database' 
WHEN BUSETS.[type] = 'L' THEN 'Log' 
WHEN BUSETS.[type] = 'F' THEN 'File/Filegroup'
WHEN BUSETS.[type] = 'G' THEN 'Differential File'
WHEN BUSETS.[type] = 'P' THEN 'Partial'  
WHEN BUSETS.[type] = 'Q'THEN 'Differential partial' 
END AS Backup_Type,
ROUND(((BUSETS.backup_size/1024)/1024),2) AS Backup_Size_MB,
ROUND(((BUSETS.compressed_backup_size/1024)/1024),2) AS Backup_Size_Compressed_MB,
BUMEDFAM.Device_type,
BUMEDFAM.Physical_device_name,
BUMEDFAM.Logical_device_name
FROM 
MASTER.sys.databases AS SYSDBLIST
LEFT OUTER JOIN
msdb.dbo.backupset AS BUSETS
ON
SYSDBLIST.name = BUSETS.database_name
LEFT OUTER JOIN
msdb.dbo.backupmediafamily  AS BUMEDFAM
ON
BUSETS.media_set_id = BUMEDFAM.media_set_id
INNER JOIN
LAST_FULL_BACKUP_LIST AS LASTBACKUP
ON
SYSDBLIST.Name=LASTBACKUP.Name
AND
ISNULL(BUSETS.backup_finish_date,'01/01/1900') = ISNULL(LASTBACKUP. Last_Backup_Finish_DateTime,'01/01/1900')
LEFT OUTER JOIN
LAST_DIFFERENTIAL_BACKUP_LIST AS LASTDIFFBACKUP
ON
SYSDBLIST.Name=LASTDIFFBACKUP.Name
LEFT OUTER JOIN
LAST_LOG_BACKUP_LIST AS LASTLOGBACKUP
ON
SYSDBLIST.Name=LASTLOGBACKUP.Name
WHERE
SYSDBLIST.name<>'TempDB'

Comments

Post a Comment

Popular posts from this blog

SSRS Report Design: Best Practices

Introduction Microsoft® SQL Server™ Reporting Services is a comprehensive reporting tool. In using Reporting Services, you can design and deliver a wide range of reports, from simple tables to online free-form reports with graphical items and interactive links. Reporting Services can render reports for a variety of client-viewing formats, including HTML, PDF, Excel, and image formats. The wide variety of options and choices can be problematic for report authors who must make numerous decisions when choosing how to lay out a report. This paper provides a few guidelines to make these decisions easier and answers common questions.
Read more

SSAS OLAP Design - Best Practices

Introduction This paper outlines the recommended best practices for designing OLAP databases in Microsoft SQL Server 2005 Analysis Services to better meet the functional and performance needs of users. Designing a database for Online Analytical Processing (OLAP) databases is very different from designing a database for Online Transaction Processing (OLTP) and requires a different approach for achieving optimal results. The best practices recommendations are grouped into the following sections: Data Source Design Best Practices Dimension Design Best Practices Cube Design Best Practices Partition Design Best Practices Aggregation Design Best Practices Within each section, these best practices are ordered roughly by priority, where priority is a combination of how important the best practice is and how often you must worry about it. Major topics not covered include best practices for the design of calculations and for server management and operations.  These two topics are s...
Read more

Enable Usage-Based Optimization in SSAS

Usage-Based Optimization is a fantastic feature in SSAS to improve the most frequently used MDX queries of any cube database. This feature is used to design aggregations based on actual user queries using Business Intelligence Development Studio. But when ever you try to design  Usage-Based Optimization  you might have seen all the options  disabled.  This uses the technique of creating aggregations on FREQUENTLY USED queries in the given time interval.  We can enable the options in Usage-Based Optimization wizard. Connect to SSAS using SSMS (Sql Server Management Studio). Right click on the server and select properties as shown below. set CreateQueryLogTable to  true. Set the value of  QueryLogConnectionString  to any database where you wish to store your queries. Set the value of  QueryLogSampling  to the appropriate value for your environment (the default value of 10 means every 10th query will be logged). The lowe...
Read more