Backup Strategy Document The purpose of creating SQL Server backups is to enable you to recover a damaged database. A well-designed backup and restore strategy maximizes data availability and minimizes data loss, while considering your particular business requirements. Recovery Model: Recovery model is a database property that controls how the transaction log is managed. The best choice of recovery model for the database depends on your business requirements. So far in production environment we are maintaining the Full Recovery model because it supports point in-time recovery, if required. To minimize work-loss exposure, at the cost of administrative overhead, use the full recovery model. Designing the Backup Strategy The optimal backup strategy depends on a variety of factors, of which the following are especially significant:

• How many hours a day do applications have to access the database? If there is a predictable off-peak period, we recommend that you schedule full database backups for that period.
• How frequently are changes and updates likely to occur? If changes are frequent, consider the following:
• Under the full recovery model, you should schedule frequent log backups.
• How much disk space will a full database backup require?

Estimating the Size of a Full Database Backup Before implement a backup and restore strategy, you should estimate how much disk space a full database backup will use. The backup contains only the actual data in the database and not any unused space. Therefore, the backup is usually smaller than the database itself. Scheduling Backups We schedule regular full backups at everyday at 7 PM (off peak hours) and at 15-minute intervals the transaction log backup. If the database size is more than 200GB then will take the full backup weekly at Sunday 7 PM (off peak hours) daily 10 PM differential backup and every 15 minute interval transaction log backup. Retention period: For the disk itself, we maintain three days full backup and storage and take the backup on tape drive for longer retention. Performance Note:

• Schedule backup 0perations when the database activity is low.
• Back up first to disk, whenever possible. Consider using a file server to store the backups.
• When using a file server to store backups, consider using a private LAN trunk to avoid general network congestion.
• Back up to tape or other devices for long run storage.
• Do not use the same physical disk that hold the database files or Log files for backup purposes.

Service Level Agreement: The SLA is an agreement between customers and users on one side and service providers on the other side. The SLA is written like a contract and describes a set of operational goals that the IT Department promises to achieve. The following example is the part of an SLA3 regarding disaster recovery and backup and restores plans. In case of the following incidents that disrupt the database service, the IT department will resolve the problem under the following conditions:

• In case of I/O system failure or human error, the database service will be available within four hours of the first report of the incident, and no more than 15 minutes of data will be lost.

Procedure to make the Backup SLA document As a DBA you have to document the critical backup information so you can prepare the SLA document for the client. The below information is required to make the SLA document so we can estimate the data loss hours. We have to make the SLA document for the 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()) ;