How to configure and Use Database Backup Compression in SQL Server 2008


How to Perform a Compressed Database Backup using TSQL
The following TSQL code can be used to take the fully compressed backup of the SampleDB database. The time taken to complete the compressed full backup was 6.910 seconds.

BACKUP DATABASE SAMPLEDB
TO DISK = N’D:DatabaseBackupsSampleDB_Compressed.Bak’
WITH INIT, STATS = 20





In the current scenario we could achieve 90% compression in the database backup size when compared to the native SQL Server Database backups.

You can run the following TSQL Code and look for a value in the Compressed column. If the value is 1 then that means that the database backup is compressed and if it is 0 then it means the database backup is not compressed or it is a native backup.

RESTORE HEADERONLY
FROM DISK = N’D:DatabaseBackupsSampleDB_Compressed.Bak’

Note: Database Backup Compression significantly increases the CPU usage and as a result it can have slight negative impact on other database operations while the database backups are perfromed. You need to carefully plan the time during which the database backup jobs can run to take the backups.

Restoring a Compressed Database Backup
The following TSQL code will restore the SampleDB database from the compressed backup and it is completed in 8.565 seconds

RESTORE FILELISTONLY
FROM DISK = N’D:DatabaseBackupsSampleDB_Compressed.Bak’
GO
RESTORE DATABASE SampleDB
FROM DISK = N’D:DatabaseBackupsSampleDB_Compressed.Bak’
WITH MOVE N’SampleDB_Data’
TO N’D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataSampleDB.mdf’,
MOVE N’SampleDB_Log’
TO N’D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataSampleDB_1.ldf’,
RECOVERY,
STATS = 20
GO


Continues…

Pages: 1 2 3




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |