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

1. Yon maintain a SQL Server 2008 instance that contains a database named Finance.
The backup strategy for the Finance database specifies the following requirements:
- Full database backup to a file named finance.bak is performed at 08:00 hours daily. - A transaction log backup to a file named finance_HHMM.trn is performed every 15 minutes starting at 08:15 horns.
The Finance database is being used in single-user mode.
At 08:40 hours, a user reports that some important data was accidentally deleted by a
query that was executed at 08:23 horns.
You need to restore the database to its original state.
Which Transact-SQL statement(s) should you use?

A) Option D
B) Option A
C) Option C
D) Option B

2. You administer a SQL Server 2008 instance that contains a database named Ad venture Works.
You are log shipping the Adventureworks database to a remote SQL Server 2008 instance.
During the weekend, the primary SQL Server instance required a restart. After the weekend, you discover that log shipping has stopped working.
You need to troubleshoot log shipping of the Adventureworks database.
What should you do?

A) Verify whether the AdventureWorks database uses the Simple recovery model.
B) Verify whether the SQL Server Volume Shadow Copy Service (VSS) Writer is started on the primary server.
C) Execute a DBCC CHECKDB statement on the AdventureWorks database by using the EXTENDED_LOGICAL_CHECKS option.
D) Verify whether the SQL Server Agent is started on the primary server.

3. You administer a SQL Server 2008 instance that contains a database named DB1.
A table named Sales.Table1 exists in the Sales schema.
You need to move the Sales.Table1 table to a new schema named Billing.
Which Transact-SQL statement should you execute?

A) ALTER USER Sales WITH DEFAULT_SCHEMA = Billing;
B) ALTER TABLE Sales.Table1 SWITCH TO Billing.Table1;
C) ALTER SCHEMA Billing TRANSFER Sales.Table1;
D) ALTER AUTHORIZATION ON Sales.Table1 TO Billing:

4. You maintain multiple SQL Server 2008 instances.
You are designing a consolidated repository of performance data.
You need to ensure that the following requirements are met:
----
The data collector is used to gather performance information.
A single database stores performance information for all instances.
Performance information that is older than 14 days is deleted.
Administrative effort to manage performance data is minimized.
What should you do?

A) Create a SQL Agent job process on each instance to store and delete performance data in a single database for all instances.
B) Configure an automated server-side trace process on each instance to store and delete performance data in a single database for all instances.
C) Create and schedule a single Microsoft SQL Service Integration Services (SSIS) package process to store and delete performance data in a single database for all instances.
D) Configure a management data warehouse process on each instance to store and delete performance data in a single database for all instances.

5. You maintain a SQL Server 2008 instance that contains a database named Finance. The data file and the transaction log file are located on the E: drive. The E: drive has only 5 percent available space.
You need to move both files to the V: drive.
Which procedure should you use?

A) Run the following Transact-SQL statement. ALTER DATABASE Finance SET RESTRICTED_USER WITH ROLLBACK_IMMEDIATE; Move the data file and transaction log file to the new location. Run the following Transact-SQL statements. ALTER DATABASE Finance MODIFY FILE ( NAME = Finance_Data, FILENAME = 'v:\SQLServer\Finance_Data.mdf'); ALTER DATABASE Finance SET MULTI_USER;
B) Stop the SQL Server Service. Move the data file and transaction log file to the new location. Start the SQL Server service. Run the following Transact-SQL statement. EXEC sp_attach_db @dbname = N'Finance', @filename1 = N'v:\SQLServer\Finance_Data.mdf', @filename2 = N'v:\SQLServer\Finance_Log.ldf';
C) Run the following Transact-SQL statement. ALTER DATABASE Finance SET OFFLINE WITH ROLLBACK_IMMEDIATE; Move the data file and transaction log file to the new location. Run the following Transact-SQL statements. ALTER DATABASE Finance MODIFY FILE (NAME = Finance_Data, FILENAME = 'v:\SQLServer\Finance_Data.mdf'); ALTER DATABASE Finance MODIFY FILE (NAME = Finance_Log, FILENAME = 'v:\SQLServer\Finance_Log.ldf'); ALTER DATABASE Finance SET ONLINE;
D) Stop the SQL Server service. Move the data file to the new location. Start the SQL Server service. Run the following Transact-SQL statement. EXEC sp_attach_single_file_db @dbname = N'Finance', @physname = N'v:\SQLServer\Finance_Data.mdf';

Solutions: