Home > Backup and Restore > Procedure to make the Backup SLA document

Procedure to make the Backup SLA document


 

Procedure to make the Backup SLA document

 

As a DBA I have to document the Critical backup information so we can prepare the SLA document for our client. The below information is required to make the SLA document so we can estimate the data loss hours. I have to make the SLA document for my client.

 

Backup Information

•           Types of Backups Performed (Full, Differential, Log):

•           Last Full Database Backup:

•           Last Differential Database Backup:

•           Last Transaction Log Backup:

•           How Often are Transaction Logs Backed Up:

•           Average Database Full Backup Time:

•           Database Full Backup Size:

•           Average Transaction Log Backup Size:

•           Number of Full Database Backup Copies Retained:

•           Backups Encrypted:

•           Backups Compressed:

•           Backup To Location:

•           Offsite Backup Location:

•           Backup Software/Agent Used:

 

 

Script reference: Book SQL Server Backup and Restore By Shawn McGehee

 

SELECT  d.name ,

MAX(d.recovery_model) ,

is_Password_Protected , –Backups Encrypted:

–Last Full Database Backup:

MAX(CASE WHEN type = ‘D’ THEN backup_start_date

ELSE NULL

END) AS [Last Full Database Backup] ,

–Last Transaction Log Backup:

MAX(CASE WHEN type = ‘L’ THEN backup_start_date

ELSE NULL

END) AS [Last Transaction Log Backup] ,

–Last Differential Log Backup:

MAX(CASE WHEN type = ‘I’ THEN backup_start_date

ELSE NULL

END) AS [Last Differential Backup] ,

–How Often are Transaction Logs Backed Up:

DATEDIFF(Day, MIN(CASE WHEN type = ‘L’ THEN backup_start_date

ELSE 0

END),

MAX(CASE WHEN type = ‘L’ THEN backup_start_date

ELSE 0

END)) / NULLIF(SUM(CASE WHEN type = ‘I’ THEN 1

ELSE 0

END), 0) [Logs BackUp count] ,

–Average backup times:

SUM(CASE WHEN type = ‘D’

THEN DATEDIFF(second, backup_start_date,

Backup_finish_date)

ELSE 0

END) / NULLIF(SUM(CASE WHEN type = ‘D’ THEN 1

ELSE 0

END), 0) AS [Average Database

Full Backup Time] ,

SUM(CASE WHEN type = ‘I’

THEN DATEDIFF(second, backup_start_date,

Backup_finish_date)

ELSE 0

END) / NULLIF(SUM(CASE WHEN type = ‘I’ THEN 1

 

ELSE 0

END), 0) AS [Average Differential

Backup Time] ,

SUM(CASE WHEN type = ‘L’

THEN DATEDIFF(second, backup_start_date,

Backup_finish_date)

ELSE 0

END) / NULLIF(SUM(CASE WHEN type = ‘L’ THEN 1

ELSE 0

END), 0) AS [Average Log Backup Time] ,

SUM(CASE WHEN type = ‘F’

THEN DATEDIFF(second, backup_start_date,

Backup_finish_date)

ELSE 0

END) / NULLIF(SUM(CASE WHEN type = ‘F’ THEN 1

ELSE 0

END), 0) AS [Average file/Filegroup

Backup Time] ,

SUM(CASE WHEN type = ‘G’

THEN DATEDIFF(second, backup_start_date,

Backup_finish_date)

ELSE 0

END) / NULLIF(SUM(CASE WHEN type = ‘G’ THEN 1

ELSE 0

END), 0) AS [Average Differential

file Backup Time] ,

SUM(CASE WHEN type = ‘P’

THEN DATEDIFF(second, backup_start_date,

Backup_finish_date)

ELSE 0

END) / NULLIF(SUM(CASE WHEN type = ‘P’ THEN 1

ELSE 0

END), 0) AS [Average partial Backup Time] ,

SUM(CASE WHEN type = ‘Q’

THEN DATEDIFF(second, backup_start_date,

Backup_finish_date)

ELSE 0

END) / NULLIF(SUM(CASE WHEN type = ‘Q’ THEN 1

ELSE 0

END), 0) AS [Average Differential

partial Backup Time] ,

MAX(CASE WHEN type = ‘D’ THEN backup_size

ELSE 0

END) AS [Database Full Backup Size] ,

 

SUM(CASE WHEN type = ‘L’ THEN backup_size

ELSE 0

END) / NULLIF(SUM(CASE WHEN type = ‘L’ THEN 1

ELSE 0

END), 0) AS [Average Transaction Log Backup Size] ,

–Backup compression?:

CASE WHEN SUM(backup_size – compressed_backup_size) <> 0 THEN ‘yes’

ELSE ‘no’

END AS [Backups Compressed]

FROM    master.sys.databases d

LEFT OUTER JOIN msdb.dbo.backupset b ON d.name = b.database_name

WHERE   d.database_id NOT IN ( 2, 3 )

GROUP BY d.name ,

is_Password_Protected

–HAVING  MAX(b.backup_finish_date) <= DATEADD(dd, -7, GETDATE()) ;

 

 

Advertisements
Categories: Backup and Restore
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: