Microsoft TS:MS SQL Server 2008,Implementation and Maintenance Sample Questions:

1. You maintain an instance of Microsoft SQL Server 2008. The instance contains a database named Finance.
The recovery model of the Finance database is set to Full. A full database backup of all the user databases is performed at 02:00 hours daily. The transaction log backup occurs every 15 minutes. A differential backup is performed every 4 hours.
You plan to perform a full backup of the Finance database at 11:00 hours.
You need to ensure that the backup is performed without affecting the overall backup and restore procedures for the Finance database. You also need to ensure that the backup files are restored in proper sequence.
Which Transact-SQL statement should you use?

A) BACKUP DATABASE FINANCE TO DISK = 't:\backups\finance.bak` WITH COPY_ONLY:
B) BACKUP DATABASE FINANCE TO DISK = 't:\backups\finance.bak' WITH NOUNLOAD;
C) BACKUP DATABASE FINANCE TO DISK = 't:\backups\finance.bak` WITH
DIFFERENTIAL;
D) BACKUP LOG FINANCE TO DISK = 't:\backups\finance.trn';

2. You are managing your company database. You manage a SQL Server 2005 database called ContractTEST, which is down.
You operate a restore by using the following Transact-SQL script. RESTORE DATABASE ContractTESTFROM contracts_bu_deviceWITH CONTINUE_AFTER_ERROR, CHECKSUM, RECOVERY After the restore, the SQL Server logs show errors in some page restores.
What you should do is to repair the database pages, which are down, and facilitates repair by using the fastest method.
Which is the correct answer?

A) You should add code: ALTER DATABASE Contracts SET TORN_PAGE_DETECTION ON
B) You should add code: ALTER DATABASE Contracts SET TRUSTWORTHY ON
C) You should add code: DBCC CHECKDB (Contracts, REPAIR_ALLOW_DATA_LOSS) WITH TABLOCK
D) You should add code: DBCC CHECKDB (Contracts, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS

3. You administer a SQL Server 2008 instance that contains a database named Sales. The Sales database has a table named Products that stores information about all types of products. Users frequently query the Products table based on the TelevisionSize column. The TelevisionSize column has the NULL value for all products other than Televisions. There is currently no index on the TelevisionSize column. You need to improve the query performance by ensuring that the effect on the disk space is minimized. What should you do?

A) Create a clustered index on the TelevisionSize column.
B) Create a unique clustered index on the TelevisionSize column.
C) Create a filtered index on the TelevisionSize column.
D) Create a view on the Products table by filtering on the TelevisionSize column.

4. You migrate an application named App1 from Microsoft SQL Server 2000 to SQL Server 2008.
You plan to monitor the SQL Server instance.
You need to capture the use of features that will be discontinued.
What should you do?

A) Run the SQL Server Profiler and capture the Exception event class.
B) Create a SQL server-side trace that captures the Deprecation Announcement and Deprecation Final Support event classes.
C) Create a Data Collector Set that captures the Microsoft-Windows-COMRuntime event trace provider.
D) Run the SQL Server 2008 Upgrade Advisor.

5. DRAG DROP
You administer a Microsoft SQL Server 2008 database for an order-processing application. The following Transact-SQL statements have been run against the database:

You need to ensure that members of the reporting group can use only up to 35 percent of the CPU and up to 45 percent of the available RAM.
Which four Transact-SQL statements should you use? (To answer, move the appropriate SQL statements from the list of statements to the answer area and arrange them in the correct order.)

Solutions: