You are a database administrator for your company. You are required to perform a full backup of the Sales database.
When performing the backup, you must fulfill the following requirements:
? The backup should be written to the disk backup device named ‘ C:sales1a.bak ‘.
? The size of each block for the backup should be 64 KB.
? The backup should expire on the 11th day.
? The new backup sets should be appended to existing backup sets on the backup media.
? The newly created backup should be named FULL BACKUP OF SALES .
Which statement will fulfill all the requirements in this scenario?
A.
BACKUP DATABASE sales
TO DISK = ‘C:sales1a.bak’ WITH
BLOCKSIZE = 65536,
RETAINDAYS = 10,
NAME = ‘FULL BACKUP OF SALES’;
B.
BACKUP DATABASE sales
TO DISK = ‘C:sales1a.bak’ WITH
BLOCKSIZE = 65536,
RETAINDAYS = 10,
FORMAT,
NAME = ‘FULL BACKUP OF SALES’;
C.
BACKUP DATABASE sales
TO DISK = ‘C:sales1a.bak’ WITH
BLOCKSIZE = 64,
NOFORMAT,
NAME = ‘FULL BACKUP OF SALES’;
D.
BACKUP DATABASE sales
NAME = ‘FULL BACKUP OF SALES’,
TO DISK = ‘C:sales1a.bak’ WITH
BLOCKSIZE = 65536,
RETAINDAYS = 10;
Explanation:
You should issue the following statement because it fulfills all the requirements in this scenario:
BACKUP DATABASE sales TO DISK = ‘C:sales1a.bak’ WITH
BLOCKSIZE = 65536,
RETAINDAYS = 10,
NAME = ‘FULL BACKUP OF SALES’;
The following clauses are used in this statement:
? TO DISK : Specifies the location of the disk backup device on which the backup of the database will bestored.
? BLOCKSIZE : Specifies the size of the blocks in which the data will be stored on the backup device. Thesize is specified in bytes. If not specified, QL Server automatically selects the block size that is appropriateeither for the disk or for the tape device.
? RETAINDAYS : Specifies the retention time of the backup in number of days. After the specified days haveelapsed, the backup can be overwritten.
? NAME : Specifies the name of the backup set.The BACKUP DATABASE statement specifies the location, the block size, the number of days to retain thebackup, and the name of the backup set required in this scenario. The BLOCKSIZE value specified is 65536bytes or 64 KB. The RETAINDAYS clause is set to 10, specifying that the backups should expire on the 11th day. The following option is incorrect:
BACKUP DATABASE sales TO DISK = ‘C:sales1a.bak’ WITH
BLOCKSIZE = 65536,
RETAINDAYS = 10,
FORMAT,
NAME = ‘FULL BACKUP OF SALES’;
You should not use the FORMAT clause in the statement because it specifies that a new backup media should becreated. By using the FORMAT clause, you can write a new media header for all the volumes in the backup set,and invalidate all the data present on the media. In this scenario, the new backup sets should be appended toexisting backup sets on the backup media. If the FORMAT clause is not specified, it defaults to NOFORMAT .This indicates that the new backup sets should be appended to existing backup sets on the backup media. The following option is incorrect:
BACKUP DATABASE sales TO DISK = ‘C:sales1a.bak’ WITH
BLOCKSIZE = 64,
NOFORMAT,
NAME = ‘FULL BACKUP OF SALES’;
This statement specifies an incorrect value for the BLOCKSIZE clause. To specify a block size of 64 KB, youshould set the BLOCKSIZE to 65536 because the value is specified in bytes. The following option is incorrect because this statement is syntactically incorrect:
BACKUP DATABASE sales NAME = ‘FULL BACKUP OF SALES’, TO DISK = ‘C:sales1a.bak’ WITH
BLOCKSIZE = 65536,
RETAINDAYS = 10;
The NAME clause that specifies the name of the backup set is specified in the WITH clause. To rectify the error inthis statement, use the following modified statement:
BACKUP DATABASE sales TO DISK = ‘C:sales1a.bak’ WITH
BLOCKSIZE = 65536,
RETAINDAYS = 10,
NAME = ‘FULL BACKUP OF SALES’;Objective:
Maintaining SQL Server InstancesSub-Objective:
Back up a SQL Server environment.References:
TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > Administration >Administration: How-to Topics > Backing Up and Restoring How-to Topics > How to: Create a Full DatabaseBackup (Transact-SQL) TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQLReference > BACKUP (Transact-SQL)