Database Reference
In-Depth Information
Getting ready
For backup compression, the SQL Server instance must be the Standard, Enterprise, or
Datacenter edition for production purposes. In case of development or testing purposes, the
Developer or Evaluation Enterprise edition will suffice.
The following are specific metrics that will help you to classify a database as VLDB:
F
The Database table storage with billions and billions of rows
F
The Database storage with multiple Gigabytes (GB) or higher (a Terabyte (TB))
F
Longer recovery time for database
F
Slower network and storage mechanism
F
Intermittent corruption of data and backup storage systems
The process of the backup compression feature can be implemented using the TSQL
statement options, such as
WITH
COMPRESSION
or
WITH
NO_COMPRESSION
. The default
backup compression feature for a SQL Server instance is
off
.
How to do it...
The following steps can be used to implement backup compression for a VLDB environment.
First, we will see how to enable compression for an SQL Server instance and manage the
efficiency of the backup compression regardless of the server-level option.
1.
To check and change the default backup compression method, execute the following
TSQL statement:
--Check and enable backup compression default setup
USE master
GO
EXEC sp_configure 'backup compression default'
GO
EXEC sp_configure 'backup compression default', '1'
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'backup compression default'
GO
Search WWH ::
Custom Search