Tuning the Performance of Backup Compression in SQL Server 2008

ckup compression is a new feature in SQL Server 2008 that can help provide smaller sized backups and reduce backup time. This document provides guidance related to tuning options for backup performance. All of the information and test results presented here were done specifically by using the backup compression feature of SQL Server 2008; however, they apply broadly to any backup scenario whether backup compression is used or not

Automatic Restore

Restore a random backup file on a predefined directory to check backup validity.

Named Pipes

Backups

Tail Log Backups

Issues with running backup log with no_log or truncate_only

On my database server I have my databases set to the full recovery model, but the transaction logs get quite big, so I am issuing a BACKUP LOG with NO_LOG. I am not exactly sure if this causes any issues, but I know that I am able to free up space in my transaction log and shrink the file. Is this the correct way to handle this situation?

An easy way to track the growth of your database

This query gives you an idea of the growth of your database over time.

Comprehensive Backup Script

These scripts were generated to standardize our SQL backup environments, and to remove backups from Maintenance Plans.

Reporting on Activity across Multiple Servers using SQL Backup and SQL Multi Script

Rodney Landrum presents a creative solution for dynamic reporting across all of his SQL Servers, based on use of Red Gate's SQL Backup and SQL Multi Script.

Shrink all databases

Procedure changes all databases' recovery mode to simple and shrinks them all (or at least it tries to).