locked
SQL Server backup Compression RRS feed

  • Question

  • Hi,

    How SQL server backup compression works?

    1.  First sql server will allocate the space for backup and then will it compress ? 

    2. Or it will allocate the space after calculating the compression space ?

    And,

    I have 3 TB database, the size of backup comes around 2.3 TB. I don't have drive space to hold the backup.

    Is there any way to make backup into chunks ?

    If so how to restore if i want in future ?

    Thanks.

    Vinodh Selvaraj


    Monday, January 5, 2015 11:41 AM

Answers

  • You can backup like this:

    BACKUP DATABASE mydb TO DISK = 'd:\backupfile1.bak', DISK = 'e:\backupfile2.bak'

    WITH COMPRESSION, STATS =10, BUFFERCOUNT =500, MAXTRANSFERSIZE = 4194304

    the restore syntax is similar

    RESTORE DATABASE mydb FROM DISK = 'd:\backupfile1.bak', DISK = 'e:\backupfile2.bak' WITH REPLACE, STATS = 10

    http://msdn.microsoft.com/en-us/library/bb964719.aspx

    Allocation of Space for the Backup File

    For compressed backups, the size of the final backup file depends on how compressible the data is, and this is unknown before the backup operation finishes. Therefore, by default, when backing up a database using compression, the Database Engine uses a pre-allocation algorithm for the backup file. This algorithm pre-allocates a predefined percentage of the size of the database for the backup file. If more space is needed during the backup operation, the Database Engine grows the file. If the final size is less than the allocated space, at the end of the backup operation, the Database Engine shrinks the file to the actual final size of the backup.

    To allow the backup file to grow only as needed to reach its final size, use trace flag 3042. Trace flag 3042 causes the backup operation to bypass the default backup compression pre-allocation algorithm. This trace flag is useful if you need to save on space by allocating only the actual size required for the compressed backup. However, using this trace flag might cause a slight performance penalty (a possible increase in the duration of the backup operation).

    Thanks, Andrew
    My blog...



    Monday, January 5, 2015 11:47 AM

All replies

  • You can backup like this:

    BACKUP DATABASE mydb TO DISK = 'd:\backupfile1.bak', DISK = 'e:\backupfile2.bak'

    WITH COMPRESSION, STATS =10, BUFFERCOUNT =500, MAXTRANSFERSIZE = 4194304

    the restore syntax is similar

    RESTORE DATABASE mydb FROM DISK = 'd:\backupfile1.bak', DISK = 'e:\backupfile2.bak' WITH REPLACE, STATS = 10

    http://msdn.microsoft.com/en-us/library/bb964719.aspx

    Allocation of Space for the Backup File

    For compressed backups, the size of the final backup file depends on how compressible the data is, and this is unknown before the backup operation finishes. Therefore, by default, when backing up a database using compression, the Database Engine uses a pre-allocation algorithm for the backup file. This algorithm pre-allocates a predefined percentage of the size of the database for the backup file. If more space is needed during the backup operation, the Database Engine grows the file. If the final size is less than the allocated space, at the end of the backup operation, the Database Engine shrinks the file to the actual final size of the backup.

    To allow the backup file to grow only as needed to reach its final size, use trace flag 3042. Trace flag 3042 causes the backup operation to bypass the default backup compression pre-allocation algorithm. This trace flag is useful if you need to save on space by allocating only the actual size required for the compressed backup. However, using this trace flag might cause a slight performance penalty (a possible increase in the duration of the backup operation).

    Thanks, Andrew
    My blog...



    Monday, January 5, 2015 11:47 AM
  • You can put the large table/s into a separated file group and backup only that file... But regarding the backup compression ,it looks like  you may have LOB data (pictures and etc) which is problematic to compress.

    http://www.brentozar.com/archive/2010/02/how-to-really-compress-your-sql-server-backups/


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by Shanky_621MVP Monday, January 5, 2015 12:02 PM
    Monday, January 5, 2015 11:51 AM
    Answerer
  • Hi Vinodh,

    I´m gonna try to give you some answers...

    How SQL server backup compression works?

    SQL Server uses the memorycleark MEMORYCLERK_SQLUTILITIES that can be queried to see how much memory your backup operation is using at the time when backup is being taken.

    SELECT * FROM SYS.dm_os_memory_clerks WHERE type = 'MEMORYCLERK_SQLUTILITIES'

    1.  First sql server will allocate the space for backup and then will it compress ? 

    Answer given by Andrew...

    For compressed backups, the size of the final backup file depends on how compressible the data is, and this is unknown before the backup operation finishes. Therefore, by default, when backing up a database using compression, the Database Engine uses a pre-allocation algorithm for the backup file. This algorithm pre-allocates a predefined percentage of the size of the database for the backup file. If more space is needed during the backup operation, the Database Engine grows the file. If the final size is less than the allocated space, at the end of the backup operation, the Database Engine shrinks the file to the actual final size of the backup.

    To allow the backup file to grow only as needed to reach its final size, use trace flag 3042. Trace flag 3042 causes the backup operation to bypass the default backup compression pre-allocation algorithm. This trace flag is useful if you need to save on space by allocating only the actual size required for the compressed backup. However, using this trace flag might cause a slight performance penalty (a possible increase in the duration of the backup operation).

    2. Or it will allocate the space after calculating the compression space ?

    And,

    I have 3 TB database, the size of backup comes around 2.3 TB. I don't have drive space to hold the backup.

    Is there any way to make backup into chunks ?

    One option is to saparete your read_only (historical) data into separeted file groups, and backup only read_write ones.. if you chose to do it... its very important you to take and store safely your transaction log backups.

    If so how to restore if i want in future ?

    Restoring your Filegroups and then Transaction Logs,

    Refers to: Backup Files and Filegroups (SQL Server)

    http://msdn.microsoft.com/en-us/library/ms189906.aspx

    Regards,

    Edvaldo Castro


    Edvaldo Castro http://edvaldocastro.com MCITP Database Administrator on SQL Server® 2008 MCITP Database Administrator on SQL Server® 2005 MCTS: SQL Server® 2008 MCTS: SQL Server® 2005 MCT: Microsoft Certified Trainer MTA: Microsoft Technology Associate MTAC – Microsoft Technical Audience Contributor CCSQLA – Cambridge Certified SQL Associate TOEIC – Test of English for International Communication

    Monday, January 12, 2015 1:42 PM