dcsimg
Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
Free Newsletters:



News Via RSS Feed


Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted March 8, 2019

Shrinking SQL Server Backup Files and Speeding Up Backups

Microsoft first introduced backup compression in SQL Server 2008. With backup compression you can speed up our backups, while making your backups smaller. While this sounds good, keep in mind compression doesn't come for free. SQL Server requires more CPU to compress a backup then not to compress a backup. The reason compressed backups run faster is because the backup blocks are compressed therefore more of your database can be writing to a backup file with each I/O operation. Because less I/O is performed your backups run faster, and they are smaller because less blocks are written.

The size of your compressed backup will be smaller than a non-compressed backup. Of course, the compression saving number can vary greatly depending on the data in your database and how compressible that data is. You will need to test compressed backup in your situation to truly understand how much faster they will run and how much disk space you might save with backup compression.

To create compressed database backups, all you need to do is add the COMPRESSION option to the BACKUP command as shown below:

BACKUP DATABASE AdventureWorks2016CTP3
 TO DISK = 'C:\temp\AdventureWorks2016CTP3_compressed.bak'
 WITH FORMAT, COMPRESSION;

# # #

See All Articles by Columnist Gregory A. Larsen



MS SQL Archives




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM