Daniel Fisher (lennybacon.com)

SOA, DATA & THE WEB

MSSQL Backup Size

I have a test environment for an application. From time to time I take the database to my local development environment.

A smaller backup means less network latency and a faster local reproduction scenario.

Since 2008 Microsoft SQL Server comes with a feature called backup compression.

I gave it a try and so I ran the two following statements in competition:

  BACKUP DATABASE [XXX]
  TO DISK = N'D:\DATA\SQL\BACKUP\Uncompressed.bak'
  WITH
    COPY_ONLY,
    NOFORMAT,
    NOINIT,
    NAME = N'Full Database Backup',
    SKIP,
    NOREWIND,
    NOUNLOAD,
    STATS = 10
  GO

vs.

  BACKUP DATABASE [XXX]
  TO DISK = N'D:\DATA\SQL\BACKUP\Compressed.bak'
  WITH
    COPY_ONLY,
    FORMAT,
    INIT,
    NAME = N'Full Database Backup',
    SKIP,
    NOREWIND,
    NOUNLOAD,
    COMPRESSION, 
    STATS = 10
  GO

Here are the results:

image

Comments

Write a comment