You are moving a SQL Server 2005 database named Timesheet to a new database server. The Timesheet

database is used by the TimeEntry application.

You need to minimize the amount of time that the TimeEntry application is unavailable while you move the

database.

What should you do?

A support engineer reports that inserting new sales transactions in a SQL Server 2005 database results in an

error.

You investigate the error. You discover that in one of the databases, a developer has accidentally deleted some

data in a table that is critical for transaction processing. The database uses the full recovery model.

You need to restore the table. You need to achieve this goal without affecting the availability of other data in the

database.

What should you do?

You discover that the msdb database on a SQL Server 2005 computer is corrupt and must be restored.

Databases are backed up daily. The database backup files are written to a network share, but the file names do not

clearly indicate which databases are in each file.

You need to locate the correct backup file as quickly as possible. The first file in the list is named

DB_Backup.bak.

Which Transact-SQL statement should you use?

A power failure occurs on the storage area network (SAN) where your SQL Server 2005 database server is

located.

You need to check the allocation as well as the structural and logical integrity of all databases, including their

system catalogs.

What should you do?

Your company uses SQL Server 2005. Users report that report execution is slow. You investigate and discover

that some queries do not use optimal execution plans. You also notice that some optimizer statistics are missing

and others are out of date.

You need to correct the problem so that reports execute more quickly.

Which two Transact-SQL statements should you use? (Each correct answer presents part of the solution. Choose

two.)

You discover that all indexes of the Sales table on your SQL Server 2005 database server are heavily

fragmented.

You need to decrease the fragmentation of all indexes of the Sales table to a minimum, while keeping the Sales

table available to all users.

What should you do?

You are responsible for maintaining scheduled jobs for the SQL Server 2005 databases in your department.

One of the jobs aggregates data from multiple sources for reports. This job runs daily. The job consists of multiple

steps. Each step aggregates data for a specific report.

A user reports that the data for some of the reports has not been updated recently.

You need to ensure that every step of the job that aggregates data executes even when errors occur.

What should you do?

You are responsible for implementing maintenance jobs on a SQL Server 2005 database server.

Certain jobs run every Sunday and other jobs run at the beginning of every month.

You need to schedule the jobs in the way that uses the least amount of administrative effort.

What should you do?

Your company has two SQL Server 2005 computers named SQL1 and SQL2. Both servers take part in

replication. SQL1 is both the Publisher and its own Distributor of a publication named Pub1. Pub1 is the only

publication on SQL1, and SQL2 is the only Subscriber.

Your supervisor requests a status report about the replication latencies.

Using Replication Monitor on SQL1, you need to find out the current latencies between the Publisher and

Distributor as well as between the Distributor and Subscriber.

What should you do?

The sales department and the finance department are located in different buildings. The two departments are

connected by a 2?megabit network. Both departments use SQL Server 2005.

The sales department records all sales activities in a database named DB1. DB1 contains 380 tables and has a size

of 10?GB.

The finance department has read-only access to four tables in DB1 for reporting purposes. The finance department

runs queries throughout the day and needs the data to be as up to date as possible. Users in the finance department

tell you that report execution is slow. To optimize network capacity, your department decides to provide copies of

these four tables to the finance department.

You need to provide copies of these tables. You want to achieve this goal by using the copy method that uses the

least amount of network bandwidth.

What should you do?