You work as a database administrator at ABC.com. ABC.com is using a SQL Server 2008 Server
that contains a database named ABC_DB. The performance of the server has been decreasing.
After investigation you notice that the ABC_DB database backup files are too large. You have
received instructions from management to cut the content of the entire database backup files of
ABC_DB. Currently the following Transact-SQL command is used:
BACKUP DATABASE ABC_DB TO DISK = ‘f:\ABCbackupfolder\ABC_DB.bak’;
In this scenario how should you change the T-SQL command to decrease the size of the ABC_DB
backup files?
A.
Add the option: WITH NO_COMPRESSION;
B.
Add the option: WITH DIFFERENTIAL;
C.
Add the option: WITH MINIMAL_SIZE;
D.
Add the option: WITH COMPRESSION;
E.
Add the options: WITH COMPRESSION, TRANSACTIONAL;
F.
Add the option: WITH TRANSACTIONAL;
G.
Add the options: WITH COMPRESSION, DIFFERENTIAL;
Explanation:
To create a full database backup
1.
Execute the BACKUP DATABASE statement to create the full database backup, specifying:
* The name of the database to back up.
* The backup device where the full database backup is written.
The basic Transact-SQL syntax for a full database backup is:
BACKUP DATABASE database
TO backup_device [ ,…n ]
[ WITH with_options [ ,…o ] ] ;
Option Description
database
Is the database that is to be backed up.
backup_device [ ,…n ]
Specifies a list of from 1 to 64 backup devices to use for the backup operation. You can specify a
physical backup device, or you can specify a corresponding logical backup device, if already
defined. To specify a physical backup device, use the DISK or TAPE option:{ DISK | TAPE } =physical_backup_device_name
For more information, see Backup Devices.
WITH with_options [ ,…o ]
Optionally, specifies one or more additional options, o. For information about some of the basic
with options, see step 2.
2.
Optionally, specify one or more WITH options. A few basic WITH options are described here. For
information about all the WITH options, see BACKUP (Transact-SQL).
* Basic backup set WITH options:
{ COMPRESSION | NO_COMPRESSION }
In SQL Server 2008 Enterprise and later only, specifies whether backup compression is performed
on this backup, overriding the server-level default.