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
