SQL Server 2008 MCITP 70-432 Exam Review

Question 1 of 186

1

You are the administrator of a SQL Server 2008 instance.
All the instances run on their independent server. The three instances are respectively named InstanceA, InstanceB, and InstanceC. Between InstanceA and InstanceB, you configure a mirroring mission-critcal database. InstanceA currently acts as the Principal and InstanceC acts as the witness.
You intend to apply a patch to both servers as soon as possible, the patch needs to restart the server.
You must make sure that the database is online on the partner that currently does not apply the patch and during this time the database does not failover to the other partner.
Which action should you perform to achieve this goal?

Select one of the following:

First, you should stop the mirroring session. Second you apply the patch to the server that runs InstanceB. Third, you should resume the mirroring session. Fourth, you should manually failover the mirroring session. At last, you should apply the patch to the server that runs Instance1.
First, you should apply the patch to the server that runs InstanceB. Second you apply the patch to the server that runs InstanceA.
First, you should apply the patch to the server that runs InstanceB. Second you failover the mirroring session manually. At last apply the patch to the server that runs InstanceA.
First, you should remove the mirroring session. Second you apply the patch to the server that runs InstanceB. Third, you should apply the patch to the server that runs Instance1. At last re-establish the mirroring session.

Explanation

Question 2 of 186

1

You are the administrator of a SQL Server 2008 instance with a database named DB1.
In the Services schema there is a table named Service.Table.
The Services.Table has to be moved from the Sales schema to a new schema named Costs.
Which Transact-SQL statement should you execute?

Select one of the following:

ALTER TABLE Service.Table SWITCH TO Costs.Table1;
ALTER SCHEMA Costs TRANSFER Service.Table;
ALTER USER Service WITH DEFAULT_SCHEMA = Costs;
ALTER AUTHORIZATION ON Service.Table1 TO Costs;

Explanation

Question 3 of 186

1

You are the administrator of a SQL Server 2008 instance with a database named DB1.
which contains the following stored procedure. (Line numbers are useded for reference only.)
01 CREATE PROCEDURE Sales.Procedure1
02 AS
03 IF OBJECT_ID('Service.Table') IS NOT NULL
04 DROP TABLE Service.Table;
05
06 CREATE TABLE Service.Table (
07 Id int PRIMARY KEY CLUSTERED,
08 Name varchar(100);
09 );
10
11 ...
12 GO
The following exception is raised when a user tries to invoke Procedure1,
"Msg 262, Level 14, State 1, Procedure Procedure1, Line 5
CREATE TABLE permission denied in database 'DB1'."
You should grant the user access to execute Procedure1, you must assign only the required permissions.
Which action should you perform?

Select one of the following:

Between lines 01 and 02, you should insert the WITH EXECUTE AS 'dbo' clause.
Between lines 01 and 02, you should insert the EXECUTE AS USER = 'dbo' statement.
You should give the user the ALTER permission on the Service schema.
You should give the CREATE TABLE permission and permit the user to drop the Service.Table table.

Explanation

Question 4 of 186

1

You are the administrator of a SQL Server 2008 instance with a database named Dworks.
The table below shows the two tables of the Dworks database:
There are two partitions in the BillHistory table.
Partition1 is empty while partition2 contains all data.
You intend to move data from partition2 to the appropriate partition of the BillHistory table.
Partions2 contains data for the oldest month of the Bills table.
You are going to develop a process to achieve this.
You have to make sure that at the end of every month the process can be repeated.

Select one of the following:

Alter the partition function of the BillHistory table by using the split option.
Switch the appropriate partition of the Bills table to the appropriate partition of the BillHistory table. Alter the BillHistory table by using the split option.
Alter the partition function of the Bills table by using the split option. Alter the Billstable by using the merge option.
Alter the partition function of the BillHistory table by using the split option. Switch the appropriate partition of the Bills table to the appropriate partition of the BillHistory table. Alter the BillHistory table by using the merge option. Alter the Bills table by using the merge option.
Alter the partition function of the Bills table by using the split option. Alter the BillHistory table by using the split option. Alter the partition function of the BillHistory table by using the merge option. Alter the Bill stable by using the merge option.

Explanation

Question 5 of 186

1

You are the administrator of a SQL Server 2008 instance.
You notice that the application has a poor performance.
On the remote SQL Server instance, you capture a workload of the remote instance to a trace table by using SQL Profiler.
You use the Database Engine Tuning Advisor to analyze the workload of the remote SQL Server instance on a local SQL Server instance.
Which action should you perform to achieve this goal?

Select one of the following:

You should recapture the workload by using the data collector.
You should enable the XP_MSVER stored procedure on the local server.
You should enable the XP_MSVER stored procedure on the remote server.
You should recapture the workload to a trace file by using SQL Profiler.

Explanation

Question 6 of 186

1

You are the administrator of a SQL Server 2008 instance.
According to the company requirement the names of all user-defined stored procedures must contain the prefix usp_ on all instances.
You have to make sure that stored procedures that do not contain this prefix cannot be created
by you.
What should you do?

Select one of the following:

A policy should be created. The policy targets the name of the stored procedure that is evaluated on demand.
A policy should be created. The policy targets the name of the stored procedure that is evaluated on change.
A condition should be created. The condition targets the name of the stored procedure that is evaluated on change.
A condition should be created. The condition targets the name of stored procedure that is evaluated on demand.

Explanation

Question 7 of 186

1

You are the administrator of a SQL Server 2008 instance.
There is a n On-Line Analytical Processing (OLAP) database named in the instance.
The database contains a dimension table named Clients.
Ever hour backup of data of the Clients table is performed.
But the Clients table contains redundant data.
You must keep the disk space used to store the Clients table.
Which compression technology should you use?

Select one of the following:

You should use row compression
You should use page compression
You should use backup compression
You should use windows NTFS file system compression

Explanation

Question 8 of 186

1

You are the administrator of a SQL Server 2008 instance.
There is a text file which is named SQL01.log on the E: drive.
A SQL Server Agent job executes every 2 minutes and logs information to the text file.
You get a report from users saying that the sever doesn't make response.
As the technical support, you check and find that the SQL Server Agent service does not run and the SQL Server Agent job no longer functions.
You have to identify the reason why the SQL Server Agent service is unresponsive.
Which log should you check?

Select one of the following:

You should check SQL1.log
You should check SQLAGENT.OUT
You should check log_xx.trc
You should check ERRORLOG

Explanation

Question 9 of 186

1

You are the administrator of a SQL Server 2008 instance.
A SQL Server Agent job is failing.
You find that the job history information is incomplete and seems to be truncated after you review it.
All information produced by a job must be available for viewing.
Which action should you perform to achieve this goal?

Select one of the following:

You should enable notifications to the Windows application event log when the job completes.
You should enable write OEM file.
You should enable all job steps, making them send the output to a file.
You should include execution trace messages in the SQL Agent Error log.

Explanation

Question 10 of 186

1

You are the administrator of a SQL Server 2008 instance.
There is a database named DB1 in the instance.
On DB1, quite few logged operations are performed.
Now according to the requirement of the company CIO, you must validate that the database can be restored to a specific point in time.
Which action should you perform to achieve this goal?

Select one of the following:

You should verify that the simple recovery model is used by the database.
You should verify that the full recovery model is used by the database.
You should verify that the checksum page verify option is used by the database,
You should verify that the bulk-logged recovery model is used by the database.

Explanation

Question 11 of 186

1

You are the administrator of a SQL Server 2008 instance.
According to the company requirement, the names of all user-defined stored procedures
must contain the prefix usp_ on all instances. Besides this, you must make sure that stored procedures that do not contain this prefix cannot be created by you.
What should you do?

Select one of the following:

A policy should be created. The policy targets the name of the stored procedure that is evaluated on demand.
A policy should be created. The policy targets the name of the stored procedure that is evaluated on change.
A condition should be created. The condition targets the name of the stored procedure that is evaluated on change.
A condition should be created. The condition targets the name of stored procedure that is evaluated on demand.

Explanation

Question 12 of 186

1

You are the administrator of a SQL Server 2008 instance.
The company CIO wants you to configure FILESTREAM data and the two requirements below must be met: you must enable FILESTREAM for file I/O streaming access; remote client computers must be able to have streaming access to FILESTREAM data.
You have to make sure that FILESTREAM data is enabled.
Which service should you configure?

Select one of the following:

You should configure SQL Server VSS Writer.
You should configure Distributed File System.
You should configure SQL Server.
You should configure SQL Server Full Text

Explanation

Question 13 of 186

1

You are the administrator of a SQL 2008 instance.
There is an Internet Information Services application.
This application will use anonymous access to access the SSAS instance.
You must make sure that the application can access the SSAS instance.
What should you do?

Select one of the following:

The ure the SQL Server service torun un server configuration should be set to False.
The Security\RequireClientAuthentication server configuration should be set to True.
The NTLM Security Support Provider Interface (SSPI) provider should be added to the Security\SecurityPackageList server configuration.
The Kerberos Security Support Provider Interface (SSPI) provider should be added to the Security\SecurityPackageList server configuration.

Explanation

Question 14 of 186

1

You are the administrator of a SQL Server 2008 with a named Instance01.
Instance01 contains a database which is named Dworks.
The bulk-logged recovery mode is used by the Dworks database.
You logship the Dworks database through a WAN link.
A scheduled job rebuilds the indexes of the Dworks database.
You find that the size of the log backups for log shipping has been enlarged by the job.
You have to reduce the size of the log backup.
Which action should you perform to achieve this goal?

Select one of the following:

You should recreate the indexes.
You should compress the log file backups.
You should make the Dworks database use the Full recovery mode.
You should make the Dworks database use the Simple recovery mode.

Explanation

Question 15 of 186

1

You are the administrator of a SQL Server 2008 cluster.
According to the company requirement, the failover response of the cluster has to be tested.
The company assigns this task to you.
On the cluster, you have to implement a manual failover.
Which actions should you take? (choose more than one)

Select one or more of the following:

The contents of a shared folder on the active node should be encrypted by using Encrypting File System (EFS)
You should restore a backup to the active node
You should get rid of the shared array from the active node
From the active node, you have to uplug the network cable(s)

Explanation

Question 16 of 186

1

You are a database developer for your company.
You are creating a parameterized stored procedure that will query the data and return rows that include any verb tense of the verbs supplied as input to the stored procedure.
The query should also return rows that contain both plural and singular forms of nouns.
The data is included in several varchar(max) and varbinary(max)columns.
You need to create the stored procedure to fulfill these requirements.
Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)

Select one or more of the following:

Use the LIKE operator.
Use the CONTAINS predicate.
Create a full-text index on the appropriate column or columns.
Create a nonclustered index on the appropriate column or columns.

Explanation

Question 17 of 186

1

You are the administrator of a SQL Server 2008 instance with a database named DB01.
The company CIO wants all the application developers can capture traces to troubleshoot the application that uses the database DB1, and they can perform this by using SQL Server Profiler.
But they must have given little permission as possible.
The company CIO assigns this task to you.
Which action should you perform to achieve this goal?

Select one of the following:

All the SQL Server logins of the application developers have to be added to a fixed server role.
All the database users of the application developers have to be added to a fixed database role.
All SQL Server logins of the application developers should be given the appropriate server-level permissions.
All database users of the application developers should be given the appropriate database-level permissions.

Explanation

Question 18 of 186

1

You are the administrator of a SQL Server 2008 instance.
You have to perform the backup of the default trace according to the requirement of your failure recovery plan.
The default trace is contained in the subdirectory.
You have to backup the subdirectory.
Which SQL Server database engine subdirectory should be backed up?

Select one of the following:

DATA
INSTALL
LOG
BINN

Explanation

Question 19 of 186

1

You are the administrator of a SQL Server 2008 instance.
There is a database named DB1 in the instance.
The backup strategy for DB1 has the following requirements:
every day at 9:00 full database backup to a file named cash.bak is performed;
At 9:15 a transaction log backup to a file named cash_HHMM.trn is performed every 15 minutes.
DB1 is being used in single-user mode.
Today a user reports that a query deleted some data by accident, the query was executed at 9: 25.
Since the deleted data is quite important, the data has to be restored to its original state.
You must achieve this goal.
Which action should you perform to achieve this goal?

Select one of the following:

RESTORE DATABASE Cash FROM DISK = 't:\backups\cash.bak' WITH NORECOVERY;
RESTORE LOG Cash FROM 't:\backups\cash_0930.trn' WITH RECOVERY,
STOPAT = 'Mar 17, 2008 9:25AM';
RESTORE DATABASE Cash FROM DISK = 't:\backups\cash.bak';
RESTORE LOG Cash FROM 't:\backups\cash_0915.trn' WITH NORECOVERY;
RESTORE LOG Cash FROM 't:\backups\cash_0930.trn' WITH RECOVERY,
STOPAT = 'Mar 17, 2008 9:25AM';
RESTORE DATABASE Cash FROM DISK = 't:\backups\cash.bak';
RESTORE LOG Cash FROM 't:\backups\cash_0915.trn' WITH NORECOVERY;
RESTORE LOG Cash FROM 't:\backups\cash_0930.trn' WITH RECOVERY;
RESTORE DATABASE Cash FROM DISK = 't:\backups\cash.bak' WITH NORECOVERY;
RESTORE LOG Cash FROM 't:\backups\cash_0915.trn' WITH NORECOVERY;
RESTORE LOG Cash FROM 't:\backups\cash_0930.trn' WITH RECOVERY,
STOPAT = 'Mar 17, 2008 9:25AM';

Explanation

Question 20 of 186

1

You are the administrator of a SQL Server 2008 instance with a database named DB1 in the instance.
At present about 90 GB of data is stored in DB1.
You made some rough calculations that every day about 1 GB of data is modified or inserted.
You set the recovery model of DB1 to Simple.
According to the company requirements, during business hours, data loss of more than 120 minutes worth of transactions is unacceptable.
You have to choose a backup strategy which must comply with the following two requirements:
- Using as little disk space as possible
- Complying with the company requirements.
Which action should you perform to achieve this goal?

Select one of the following:

A full database backup should be performed once daily. During business hour, a transaction log backup should be performed every two hours.
A full database backup should be performed once daily.
A full database backup should be performed once daily. During business hours, a differential backup should be performed every two hours.
Perform a full database backup should be performed once every week. A differential backup should be performed once daily. During business hours a transaction log backup should be performed every two hours.

Explanation

Question 21 of 186

1

You are the administrator of a SQL Server 2008 instance with a database named DB1.
The latest differential backup is performed at 15:30, the full back up was performed at 13:30,
and database snapshots were created at 16:30 and at 17:30.
The backups and the database snapshots are stored on a different disk from the database files.
At 17:05, the hard disk containing the database files fails at 17:02 hours.
You must restore DB1 while reduce data loss to the least.
Which action should you perform to achieve this goal?

Select one of the following:

You should restore the full backup
You should restore the database snapshot from 16:30 hours.
You should restore the database snapshot from 17:30 hours
You should restore both the full backup and the differential backup.

Explanation

Question 22 of 186

1

You are the administrator of a SQL Server 2008 instance.
There is a database named DB1 in the instance.
There are data file and the transaction log file on the E: drive.
You find that there is only 6% spare space.
Both files have to be moved to the V: drive.
Which procedure should you use?

Select one of the following:

You should run the following Transact-SQL statement:
ALTER DATABASE DB1 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 DB1 MODIFY FILE(NAME = DB1_Data, FILENAME = 'v:\SQLServer\DB1_Data.mdf'); ALTER DATABASE DB1 SET MULTI_USER;
You should terminate the SQL Server Service.
Then 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'DB1',@filename1 = N'v:\SQLServer\DB1_Data.mdf',@filename2 = N'v:\SQLServer\DB1_Log.ldf';
You should run the following Transact-SQL statement:
ALTER DATABASE DB1 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 DB1 MODIFY FILE(NAME = DB1_Data, FILENAME = 'v:\SQLServer\DB1_Data.mdf'); ALTER DATABASE DB1 MODIFY FILE(NAME = DB1_Log, FILENAME = 'v:\SQLServer\DB1_Log.ldf'); ALTER DATABASE DB1 SET ONLINE;
You should terminate the SQL Server service.
Then 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'DB1',@physname = N'v:\SQLServer\DB1_Data.mdf';

Explanation

Question 23 of 186

1

You are the administrator of a SQL Server 2008 instance with a database named DB1.
A backup of DB1 is performed every day.
You have to minimize the size of the full database backup files of DB1.
Which Transact-SQL statement should you use?

Select one of the following:

BACKUP DATABASE DB1 TO DISK = 't:\backups\db1.bak';
BACKUP DATABASE DB1 TO DISK = 't:\backups\db1.bak' WITH COMPRESSION;
BACKUP DATABASE DB1 TO DISK = 't:\backups\db1.bak' WITH DIFFERENTIAL;
BACKUP DATABASE DB1 TO DISK = 't:\backups\db1.bak' WITH COMPRESSION, DIFFERENTIAL;

Explanation

Question 24 of 186

1

You are the administrator of a SQL Server 2008 instance with a database named DB1.
When you are absent, a user will use a login named Mary to log in and maintain the database snapshots. The user has to delete the database snapshots for DB1, so you have to give the appropriate permissions to the user.
Which database permission should you give the user?

Select one of the following:

DELETE
CONTROL
DROP DATABASE
ALTER ANY DATASPACE

Explanation

Question 25 of 186

1

You are the administrator of a SQL Server 2008 instance with a database named DB1 in the instance.
The DB1 database includes spatial data types. On DB1 database, you have to perform a database consistency check to include the spatial indexes. Besides this, you must make sure that you can reduce the effect on the database concurrency to the least.
Which Transact-SQL statement should you run?

Select one of the following:

DBCC CHECKCATALOG (DB1);
DBCC CHECKALLOC (DB1) WITH TABLOCK;
DBCC CHECKDB (DB1) WITH TABLOCK, PHYSICAL_ONLY;
DBCC CHECKDB (DB1) WITH EXTENDED_LOGICAL_CHECKS;

Explanation

Question 26 of 186

1

You are the administrator of a SQL Server 2008 instance.
A SQL Server Agent job is failing.
You find that the job history information is incomplete and seems to be truncated after you review it.
All information produced by a job must be available for viewing.
Which action should you perform to achieve this goal?

Select one of the following:

You should enable notifications to the Windows application event log when the job completes.
You should enable write OEM file
You should enable all job steps, making them send the output to a file
You should include execution trace messages in the SQL Agent Error log

Explanation

Question 27 of 186

1

You are the administrator of a SQL Server 2008 instance containing a database named DB1.
An application which is continuously connected uses DB1.
The application extensively uses the INSERT command and triggers the population of multiple tables. Now you notice that the application has a poor performance.
You doubt that this problem has something to do with blocking.
You have to monitor the state of the instance at regular intervals while not affecting the application performance further.
Which tool should you use?

Select one of the following:

You should use Dynamic Management Views
You should use SQL Server Resource Governor
You should use SQL Server Profiler
You should use Windows System Monitor

Explanation

Question 28 of 186

1

You are the administrator of a SQL Server 2008 instance.
You upgraded this instance to SQL Server 2008.
You configure the Agent service to use the LocalSystem account.
There is a file named Orderdata on a remote network share.
A job accesses this Orderdata file by using a CMDExec step.
The job step fails to complete execution after the upgrade.
You have to make sure that the job can complete execution, so you have to configure the job step.
Which action should you perform?

Select one of the following:

You should configure a certificate
You should configure the SQL Server Agent service to use the NetworkService account.
You should configure the job step to use a proxy account
You should configure the SQL Server Agent service to use a local Windows account

Explanation

Question 29 of 186

1

You are the administrator of a SQL Server 2008 instance.
A user reports that when he runs a query, it takes a quite long time and it is still unfinished.
As the IT support, you have to verify whether the query is blocked.
Which tool should you use?

Select one of the following:

You should use the Database Engine Tuning Advisor tool
You should use the Windows System Monitor tool
You should use the Job Activity Monitor tool in Microsoft SQL Server Management Studio
You should use the Activity Monitor tool in Microsoft SQL Server Management Studio

Explanation

Question 30 of 186

1

You are the administrator of a SQL Server 2008 instance with a database named DB1.
You have set the recovery model of DB1 to Full.
At 2:00 There is a full database backup of all the user databases every day.
Every quarter the transaction log backup is performed.
Every 4 hours a differential backup is occurred.
You are going to perform a full backup of DB1 at 11:00.
When you perform this back up, you must make sure that the overall backup will not be affected. What's more, you have to restore procedures for DB1.
You must make sure that the backup files are restored in proper sequence.
Which Transact-SQL statement should you use?

Select one of the following:

BACKUP LOG DB1 TO DISK = 't:\backups\db1.trn';
BACKUP DATABASE DB1 TO DISK = 't:\backups\db1.bak' WITH DIFFERENTIAL;
BACKUP DATABASE DB1 TO DISK = 't:\backups\db1.bak' WITH NOUNLOAD;
BACKUP DATABASE DB1 TO DISK = 't:\backups\db1.bak' WITH COPY_ONLY;

Explanation

Question 31 of 186

1

You are the administrator of a SQL Server 2008 instance.
You get an order from your company CIO to identify whether a database integrity check (DBCC CHECKDB) was run for a particular database.
Which log file should you examine?

Select one of the following:

log.trc
default.trc
ERRORLOG
SQLAGENT

Explanation

Question 32 of 186

1

You are the administrator of a SQL Server 2008 instance.
Users report that they are unable to connect to the named instances.
You check and verify that they can only connect to the default instance.
You also ensure that all SQL Server instances run normally.
You have to start the service which is required to connect to the named instances.
Which service should you start?

Select one of the following:

Server
SQL Server Agent
SQL Server Browser
SQL Active Directory Helper

Explanation

Question 33 of 186

1

You are the administrator of a SQL Server 2008 instance.
There are user-defined stored procedures.
You have to ensure two things.
First the names of all user-defined stored procedures must contain the prefix usp_ on all instances; secondly stored procedures wich do not contain this prefix cannot be created by you.
What should you do to ensure these two?

Select one of the following:

You should create a policy that targets the name of the stored procedure that is evaluated on change.
You should create a policy that targets the name of the stored procedure that is evaluated on demand.
You should create a condition that targets the name of stored procedure that is evaluated on demand.
You should create a condition that targets the name of the stored procedure that is evaluated on change.

Explanation

Question 34 of 186

1

You are the administrator of a SQL Server 2008 instance.
A new SQL Agent job has been created by you. The job includes a Windows PowerShell job step which transfer data between servers by using the SQLCmd utility. If the job fails, you have to make sure that an operator named ReactGroup is informed by receiving an e-mail.
What action should you perform to achieve this goal?

Select one of the following:

First you should enable Notifications. Direct the Notification to the ReactGroup operator on job failure after you create the ReactGroup operator.
First you should enable Notifications. Direct the Notification to the ReactGroup operator on failure of the Powershell job step after you create the ReactGroup operator.
First you should assign the ReactGroup operator as the failsafe operator. Then enable the job after you create the ReactGroup operator.
First you should configure the job step proxy account to use the ReactGroup operator account after you create the ReactGroup operator. At last choose the proxy account for the e-mail profile.

Explanation

Question 35 of 186

1

You are the administrator of a SQL Server 2008 instance.
There is a stored procedure. The stored procedure implements a database maintenance process.
A SQL Server Agent job should be created to that runs the stored procedure. Besides this,
You have to make sure that after it is completed successfully, the job is removed.
What should you do?

Select one of the following:

You should create a job which is assigned to the Database Maintenance category
You should create an Alert which will run another to delete the maintenance job.
You should create a job. You schedule the job to run only once.
You should create a job. The job uses the Automatically delete job option

Explanation

Question 36 of 186

1

You are the administrator of a SQL Server 2008 instance which contains multiple Agent jobs.
The jobs run each job on each day of the week by using seven shared schedules.
You have to make sure that a job named Job4 doesn't run Wednesday.
Which action should you perform to achieve this goal?

Select one of the following:

You should remove the schedule for Wednesday from Job4.
You should add a new Job4 schedule for Wednesday and disable it.
You should delete the schedule for Wednesday.
You should disable the schedule for Wednesday.

Explanation

Question 37 of 186

1

You are a database developer for your company.
You use the following Transact-SQL code.
SELECT e.Resume,j.EmployeeID FROM
JobCandidate j, Employee e
WHERE j.EmployeeID =* e.EmployeeID
You need to rewrite this code to be ANSI compliant.
How should you rewrite the code?

Select one of the following:

SELECT e.Resume, j.EmployeeID
FROM JobCandidate AS j
LEFT OUTER JOIN Employee AS e
ON EmployeeID = e.EmployeeID
SELECT e.Resume, j.EmployeeID
FROM JobCandidate AS j
FULL OUTER JOIN Employee AS e
ON EmployeeID = e.EmployeeID
SELECT e.Resume, j.EmployeeID
FROM JobCandidate AS j
RIGHT OUTER JOIN Employee AS e
ON EmployeeID = e.EmployeeID
SELECT e.Resume, j.EmployeeID
FROM JobCandidate AS j
CROSS JOIN Employee AS e

Explanation

Question 38 of 186

1

You are the administrator of a SQL Server 2008 instance.
There is a management data warehouse that uses the data collector to collect performance data. You intend to maintain the management data.
You want to implement a data collection process.
The process can gather and upload data in the management data warehouse on different schedules.
Which process should you implement?

Select one of the following:

You should create a cached data collection
You should create an on-demand non-cached data collection
You should create a scheduled non-cached data collection
You should create two different SQL Agent jobs that are scheduled stimultaneously

Explanation

Question 39 of 186

1

You are the administrator of a remote SQL Server 2008 instance.
You notice that the application has a poor performance.
On the remote SQL Server instance, you capture a workload of the remote instance to a trace table by using SQL Profiler.
You have to use the Database Engine Tuning Advisor to analyze the workload of the remote SQL Server instance on a local SQL Server instance.
Which action should you perform to achieve this goal?

Select one of the following:

You should recapture the workload by using the data collector.
You should enable the XP_MSVER stored procedure on the local server.
You should enable the XP_MSVER stored procedure on the remote server
You should recapture the workload to a trace file by using SQL Profiler.

Explanation

Question 40 of 186

1

You are the administrator of a SQL Server 2008 instance.
According to the company requirement, you have to move the application from Microsoft SQL Server 2000 to Microsoft SQL Server 2008.
You have to monitor the SQL Server instance to record the use of features.
These features will be discontinued. What should you do?

Select one of the following:

The SQL Server 2008 Upgrade Advisor should be used.
The SQL Server Profiler which captures the SQL:BatchCompleted and Exception event classes should be used.
A SQL server-side trace that captures the Deprecation Announcement and Deprecation Final Support event classes should be used
A SQL server-side trace that captures the SQL:BatchCompleted and Exception event classes should be used.

Explanation

Question 41 of 186

1

You are the administrator of a SQL Server 2008 instance with a database named DB1.
DB1 has two filegroups: one is a filegroup named History that contains historical data stored in the G: volume; the other is the PRIMARY filegroup that contains the On-Line Transaction Processing (OLTP) data stored in the F: volume.
The backup strategy of DB1 has three requirements:
- first, the History filegroup should be backed up at the end of each working day;
- second, the PRIMARY filegroup should be backed up five times a day;
- third, the transaction log should be backed up every quarter.
You get a report at 13:00 saying that the volume which contains the History filegroup fails.
As the technical support, you have to restore the database, and you must reduce the data loss to the least as soon as possible.
What should you do?

Select one of the following:

You should restore the most recent backup of the History filegroup.
You should restore the most recent backup of the History filegroup. Restore all the transaction log backups after the most recent backup of the History filegroup
You should backup the transaction log. Then restore the most recent backup of the PRIMARY filegroup and restore the most recent backup of the History filegroup. After the most recent backup of the History filegroup, restore all the transaction log backups
You should backup the transaction log. Then restore the most recent backup of the History filegroup and restore all the transaction log backups after the most recent backup of the History filegroup.

Explanation

Question 42 of 186

1

You are the administrator of a SQL Server 2008 instance.
Your company CIO assigns you a task.
You have to check the performance of the SQL Server 2008 instance.
You have to find out which common language runtime (CLR) queries takes the longest-running time.
Which dynamic management view should you choose to use?

Select one of the following:

sys.dm_os_wait_stats
sys.dm_exec_requests
sys.dm_exec_sessions
sys.dm_exec_query_stats

Explanation

Question 43 of 186

1

You are a database developer for your company.
You are developing a sales report for a given time period.
The report must list all sales representatives and their position based on their total sales for the period. If two sales representatives had the same total sales, they should be assigned the same position. A sample output is shown in the following data:
SALESREPID SALES POSITION
456 123,456 1
765 123,456 1
983 103,762 3
106 101,101 4
You need to create the report to fulfill the requirements.
Which operator should you use?

Select one of the following:

RANK
DENSE_RANK
NTILE
ROW_NUMBER

Explanation

Question 44 of 186

1

You are the administrator of a SQL Server 2008 instance which hosts several applications in your company.
A job named DeliveryList is created by you.
This job requires a file to be written to a file server.
Because the job cannot access the file server, it fails to run.
You intend to configure the SQL Server Agent service so only the SQL Server Agent service has read and write access to the file server.
Which account type should you use?

Select one of the following:

You should use local Service account
You should use network Service account
You should use domain account
You should use local System account

Explanation

Question 45 of 186

1

You are the administrator of a SQL Server 2008 instance with a database named DB1.
On DB1, quite a few logged operations are performed.
Now according to the requirement of the company CIO, you must validate that the database can be restored to a specific point in time.
Which action should you perform to achieve this goal?

Select one of the following:

You should verify that the simple recovery model is used by the database
You should verify that the full recovery model is used by the database.
You should verify that the checksum page verify option is used by the database
You should verify that the bulk-logged recovery model is used by the database

Explanation

Question 46 of 186

1

You are the administrator of a SQL Server 2008 instance.
There is a SQL Server 7.0 database.
You upgrade the database to the SQL Server instance.
According to the company requirement, you have to make sure that suspect pages can be detected in the database.
Which action should you perform to achieve this goal?

Select one of the following:

For the database, you should turn on the TRUSTWORTHY database option
For the database, the PAGE_VERIFY database option should be set to CHECKSUM
For the database, the database compatibility level option should be set to 10.
For the model database, the PAGE_VERIFY database option should be set to TORN_PAGE_DETECTION.

Explanation

Question 47 of 186

1

You are a database developer for your company.
You are developing a stored procedure that must accept a parameter of the xml data type and store the data in a relational table structure.
You need to write the stored procedure to fulfill the requirements.
Which functionality should you use?

Select one of the following:

FOR XML AUTO
the nodes method of the xml data type
the query method of the xml data type
the modify method of the xml data type

Explanation

Question 48 of 186

1

You are a database developer for your company.
You are creating a stored procedure that will use a Transact-SQL cursor to share the result set with other statements in the stored procedure.
This cursor must have a fixed membership where the order of the rows does not change.
The data in the rows that are retrieved through the cursor should be updatable.
These updates should be viewable after they are made.
While implementing this cursor, you need to minimize memory usage regardless of the resulting effect on the speed of the cursor.
You need to create the stored procedure to fulfill these requirements.
Which option should you use in the DECLARE CURSOR statement?

Select one of the following:

DYNAMIC
FAST_FORWARD
KEYSET
STATIC

Explanation

Question 49 of 186

1

You are a database developer for your company.
You are creating a parameterized stored procedure that will query the data and return rows that include any verb tense of the verbs supplied as input to the stored procedure.
The query should also return rows that contain both plural and singular forms of nouns.
The data is included in several varchar(max) and varbinary(max)columns.
You need to create the stored procedure to fulfill these requirements.
Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)

Select one or more of the following:

Use the LIKE operator.
Use the CONTAINS predicate.
Create a full-text index on the appropriate column or columns.
Create a nonclustered index on the appropriate column or columns.

Explanation

Question 50 of 186

1

You are the administrator of a SQL Server 2008 instance.
You configure the SQL Server instance to use the -T1222 and -T1204 trace flags during startup.
You have to make sure that your failure recovery plan performs backup of the use of the trace flags.
Which action should you perform to achieve this goal?

Select one of the following:

You should backup the master database
You should backup the default.trc file
You should backup the SQL Server registry hive.
You should backup the resource database

Explanation

Question 51 of 186

1

You are the administrator in charge of two instances both of which run on the same computer.
One is a SQL Server 2008 instance; another is a SQL Server 2005 instance.
There is a database named DB1 in the SQL Server 2008 instance.
DB1 uses the Fulltext indexes.
Several records that include the word "root" are added to DB1.
An empty resultset is returned when the Fulltext index is queried for the word "root."
You have to make sure the query can return records that contain the word "root".
What should you do?

Select one of the following:

You should terminate and restart the MSFTESQL service
You should rebuild the full-text index
The word "root" should be added to the stop list
The word "root" should be added to the thesaurus file

Explanation

Question 52 of 186

1

You are the administrator of a SQL Server 2008 instance.
You make sure that all SQL Server instances are consistently configured for naming conventions, security settings, force index creation and avoidance of data fragmentation.
Which action should you perform to achieve this goal?

Select one of the following:

You should use the Database Engine Tuning Advisor.
In Microsoft SQL Server Management Studio, you should create a maintenance plan
You should use the SQL Server Configuration Manager
In Microsoft SQL Server Management Studio, you should create a policy

Explanation

Question 53 of 186

1

You are the administrator of a SQL Server 2008 instance with a database named Dworks.
The Dworks database has a table named Orderthings. The Orderthings table is partitioned on the OrderId column. The first partition contains integer values greater than 100,000, while the second partition contains integer values between 1 and 100,000.
You have to add a new partition.
The new partition should contain integer values greater than 200,000.
What should you do?

Select one of the following:

A new partition function should be created
You should change the existing partition scheme
You should use a Merge clause to change the existing partition function
You should use a Split clause to change the existing partition function

Explanation

Question 54 of 186

1

You are the administrator of a SQL Server 2008 instance with a database named Dworks.
You get a report from users saying that DB1 meets deadlock problems.
As the technical support, you have to capture the deadlock information to the SQL Server error log.
What should you do?

Select one of the following:

For the AdventureWorks database, enable Server Auditing
First you should set the appropriate trace flags as a startup parameter, and then restart the SQL Server instance
You should configure the data collector and make it capture the deadlock graphs
You should configure a SQL Profiler trace, and make it capture the deadlock graphs

Explanation

Question 55 of 186

1

You work in a company which is named Wiikigo Corp.
The company uses SQL Server 2008.
You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
There is a database named Dworks in the instance.
The Dworks database has a table named Orderthings. According to the company requirement, you have to export all data from the Orderthings table to a file.
During the export, you must make sure that the data export process is saved for reuse and a Microsoft Office Open XML document format is used.
What should you do?

Select one of the following:

You should run the bulk copy program utility along with an output file and no format file
You should run the SQLCmd utility and save the output to a file
You should run the SQL Import and Export Data Wizard and save the output to a file.
You should run the bulk copy program utility along with a format file and an output file.

Explanation

Question 56 of 186

1

You work in a company which is named Wiikigo Corp.
The company uses SQL Server 2008.
You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
There is a database which is named Sellings in the instance.
The Sellings database contains a table named Productions.
The table is used to stores information about all types of products.
The Productions table is often queried by users on the basis of the RadioSize column.
The RadioSize column contains the NULL value for all products other than Radios.
Currently no index exists on the RadionSize column.
According to the requirement of the company CIO, you have to optimize the query performance and reduce the effect on the disk space to the least.
Which action should you perform to achieve this goal?

Select one of the following:

On the Products table, you should create a view.
On the RadioSize column, you should create a clustered index
On the RadioSize column, you should create a filtered index
On the RadioSize column, you should create a unique clustered index

Explanation

Question 57 of 186

1

You work in a Organization which is named Wiikigo Corp.
The Organization uses SQL Server 2008.
You are the administrator of the Organization database.
Now you are in charge of a SQL Server 2008 instance.
Look at the following query:
SELECT s., i.
FROM SensitiveTbl AS s
INNER JOIN InsensitiveTbl AS i
ON i.OrganizationName = s.OrganizationName
You use the above query to join two tables on a column named OrganizationName.
The following error is returned when you execute the query.
"Msg 468, Level 16, State 9, Line 17 Cannot resolve the collation conflict between 'SQL_Latin1_General_CP1_CS_AS' and 'SQL_Latin1_General_CP1_CI_AS' in the equal to operation."
The ON clause of the query has to be modified so that it can perform a case-sensitive joinsuccessfully.
What should you do?

Select one of the following:

ON LOWER(i.OrganizationName) = LOWER(s.OrganizationName)
ON UPPER(i.OrganizationName) = UPPER(s.OrganizationName)
ON i.OrganizationName = s.OrganizationName COLLATE SQL_Latin1_General_CP1_CS_AS
ON i.OrganizationName = s.OrganizationName COLLATE SQL_Latin1_General_CP1_CI_AS

Explanation

Question 58 of 186

1

You work in a company which is named Wiikigo Corp.
The company uses SQL Server 2008.
You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
There is a database named OrderIn in the instance.
The OrderIn database contains a table which is named OrdeSend.
A column named SendSite which is of the Geography data type has been added to the OrdeSend. The OrderSend table contains no indexes.
On the SentSite point, you have to create a spatial index.
What should you do first?

Select one of the following:

You must make sure that the SendSite column does not allow NULL values.
You should define a primary key for the OrderSend table
For the OrderSend table, you should create a clustered index
First, you should copy the OrderSend data to a temporary table, then you should truncate the existing OrderSend table.

Explanation

Question 59 of 186

1

You work in a company which is named Wiikigo Corp.
The company uses SQL Server 2008.
You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
The server has a very large database named DB1.
An application which is constantly available uses DB1.
Now you get a report from users, in the report users complain that server has a poor performance. As the technical support, you have to improve the performance of the application by using the Database Engine Tuning Advisor.
Besides this, you must make sure that the action of analyzing the workload will not affect the performance of the production server.
What should you do?

Select one of the following:

On the local server, enable the XP_MSVER stored procedure
On the remote server, enable the XP_MSVER stored procedure.
On the production server, use the dta.exe utility along with an XML input file.
You should configure a test server that has a similar hardware configuration. Use the dta.exe utility on the test server along with an XML input file

Explanation

Question 60 of 186

1

You work in a company which is named Wiikigo Corp.
The company uses SQL Server 2008.
You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
Today you notice that applications that run on the server have poor performances.
You doubt that this has something to do with table scans.
You have to capture the appropriate information by using an appropriate Windows System Monitor object.
Which performance object should you use?

Select one of the following:

You should use SQLServer:Buffer Manager
You should use SQLServer:Memory Manager
You should use SQLServer:Databases
You should use SQLServer:Access Methods

Explanation

Question 61 of 186

1

You work in a company which is named Wiikigo Corp.
The company uses SQL Server 2008.
You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
The server hosts databases for several mission-critical applications.
Microsoft SQL Server Management Studio executes queries and it has some effect.
Now you intend to limit the effect by using the Resource Governor.
You have to make sure that queries initiated through SQL Server Management Studio is less than
20 percent of CPU utilization, besides this, you must make sure that queries initiated by the missioncritical
applications can consume 100 percent of CPU utilization when required.
Which action should you perform to achieve this goal?

Select one of the following:

You should alter the default resource pool and set the MAX_CPU_PERCENT option to 80.
Then assign this resource pool to the workload group used by SQL Server Management Studio.
First, you should create a new resource pool and set the MAX_CPU_PERCENT option to 20.
Then assign this resource pool to the workload group used by SQL Server Management Studio.
You should alter the default resource pool and set the MAX_CPU_PERCENT option to 20.
Then assign this resource pool to the workload group used by the mission-critical applications.
First, you should create a new resource pool and set the MAX_CPU_PERCENT option to 80.
Then assign this resource pool to the workload group used by the mission-critical applications.

Explanation

Question 62 of 186

1

You work in a company which is named Wiikigo Corp.
The company uses SQL Server 2008.
You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
You are going to use the data collector to gather performance data periodically on all instances.
You must store all collected data in the same database.
This database is hosted on a single instance. Every five hours, you have to collect and load performance data in the management data warehouse.
Which data collection process should you implement?

Select one of the following:

You should create a cached data collection
You should create an on-demand non-cached data collection
You should create a scheduled non-cached data collection.
You should create two different SQL Agent jobs. The two jobs are scheduled at the same time. One job uploads the data collection and the other job creates a data collection.

Explanation

Question 63 of 186

1

You are the administrator of a SQL Server 2008.
According to the company requirement, you are designing a consolidated repository of performance data.
You must make sure that the four requirements below are met:
1. the data collector is used to gather performance information
2. a single database stores performance information for all instances
3. performance information that is older than 15 days is deleted
4. reduce the administrative effort to manage performance to the least.
Which action should you perform to achieve this goal?

Select one of the following:

You should create a SQL Agent job process on each instance to store and delete performance data in a single database for all instances.
You should configure a management data warehouse process on each instance, then use this process to store and delete performance data in a single database for all instances.
You should configure an automated server-side trace process on each instance, then use this process to store and delete performance data in a single database for all instances.
You should create and schedule a single Microsoft SQL Service Integration Services (SSIS) package process, then use this process to store and delete performance data in a single database for all instances.

Explanation

Question 64 of 186

1

You are the administrator of a SQL Server 2008, with a report often executed during business time.
There is a stored procedure that is used as the data source for the report.
You get a report from users saying that they receive the data returned by the report but the data is inconsistent.
As the technical support, you check and find that phantom reads cause this problem.
You have to make sure that consistent data is returned by the report while not affecting other users.
Which action should you perform to achieve this goal?

Select one of the following:

You should configure the database for Read Committed Snapshot isolation.
You should modify the stored procedure to use the Snapshot isolation level
You should configure the database for Auto Update Statistics asynchronously
You should modify the stored procedure to use the Repeatable Read isolation level

Explanation

Question 65 of 186

1

You are the administrator of a SQL 2008 two cluster-ready nodes.
You intend to perform an installation of a SQL Server 2008 mission-critical cluster.
Constant availability is needed by the mission-critical cluster.
You have to configure the SQL Server cluster to failover and reduce the service disruption to the least.
Which failover option should you use?

Select one of the following:

You should allow failback only during non-business hours
You should immediately allow failback
You should prevent automatic failback
You should allow failback only during business hours

Explanation

Question 66 of 186

1

You are the administrator of a SQL Server 2008 instance with a database named Dworks.
You are log shipping the Adventureworks database to a remote SQL Server 2008 instance.
The primary SQL Server instance required a restart during the weekend.
But you notice that log shipping has stopped working after the weekend.
You have to check log shipping of the Adventureworks database and find out the problem.
What should you do?

Select one of the following:

You should use the EXTENDED_LOGICAL_CHECKS option to execute a DBCC CHECKDB statement on the AdventureWorks database by.
You should identify whether the SQL Server Agent is started on the primary server.
You should identify whether the AdventureWorks database uses the Simple recovery model.
You should identify whether the SQL Server Volume Shadow Copy Service (VSS) Writer is started on the primary server.

Explanation

Question 67 of 186

1

You are the administrator of a SQL Server 2008 with two instances.
The two instances are respectively named InstanceA and InstanceB.
There is a database named Dworks on InstanceA.
You move the Dworks database to InstanceB.
A user use a SQL Server login named Mary to access the database on InstanceA with the password "Tk@87#34".
On InstanceB, you create the same SQL Server login.
The user tries to use the SQL Server login Mark to access the Dworks database on InstanceB.
But the user gets an error message, according to the indication of the message, the access to the Dworks database has been denied.
You have to make sure that the user can access the Dworks database on InstanceB successfully.
On InstanceB, which Transact-SQL statements should you execute?

Select one of the following:

USE AdventureWorks;ALTER USER Mary WITH LOGIN = Mary;
USE AdventureWorks;ALTER LOGIN Mary ENABLE;
USE AdventureWorks;ALTER LOGIN Mary WITH PASSWORD = "Tk@87#34" UNLOCK;
USE AdventureWorks;ALTER LOGIN Mary WITH DEFAULT_DATABASE = AdventureWorks;

Explanation

Question 68 of 186

1

You are the administrator of a SQL Server 2008 cluster in a high security environment,
You intend to configure and use encrypted connections for the clustered virtual SQL Server.
You have to install a certificate.
The cetification will be used for encryption.
What should you do?

Select one of the following:

In the cluster quorum drive, install the encryption certificate
In the SQL Server shared disk, install the encryption certificate
in the cluster group, install the encryption certificate.
On each individual node, install the encryption certificate

Explanation

Question 69 of 186

1

You are the administrator SQL 2008 instance with a database named DB1.
The company stores clients data in DB1.
Clients can access their profile data by using a Web application.
You must ensure the security of the customer data, you must ensure that even if the backup media is lost, data files, log files and subsequent backups and so on are quite safe.
Which action should you perform to achieve this goal?

Select one of the following:

For both the CustomerDB database and the master database, enable Transparent Database Encryption.
You should use the built-in encryption functions to encrypt the sensitive data at the cell level.
You should make the CustomerDB database accessible only through stored procedures and functions.
For the CustomerDB database, enable Transparent Database Encryption and back up the transaction log.

Explanation

Question 70 of 186

1

You are the administrator of a SQL Server 2008 instance.
There is an internal application which uses Analysis Services and Reporting Services.
According to the requirement of company security policy, the surface area for all the deployed components of SQL Server 2008 should be configured.
You have to implement the security policy and devise a method to evaluate the security policy against other database servers.
What should you do?

Select one of the following:

You should create policies based on the appropriate facets. Apply the policies against a server group. The server group includes the database servers that need to be configured
You should create a Transact-SQL script based on the sp_configure stored procedure. Use a configuration server to run the script against a server group that includes the database servers that need to be configured
You should edit the RSReportServer.config configuration file. Distribute the file to all database servers that need to be configured
You should analyze your database servers by using the SQL Server Best Practices Analyzer (BPA). Implement the recommendations of the BPA

Explanation

Question 71 of 186

1

You are the administrator of a SQL Server 2008 instance with a database named Service with
a database user named ServiceGreatUser.
A SQL Server login named ServiceGreatLogin maps to ServiceGreatUser in the Service database.
According to the requirement of the company CIO, the database user can only perform the two tasks below:
- the database user executes all stored procedures that currently exist in the Sales database
- executes all stored procedures that will be created in the Sales database.
What should you do to ensure this?

Select one of the following:

ServiceGreatUser should be added to the appropriate fixed database roles.
You should assign the appropriate object-level permissions to ServiceGreatUser
You should assign the appropriate server-level permissions to ServiceGreatLogin
You should assign the appropriate database-level permissions to ServiceGreatUser

Explanation

Question 72 of 186

1

You are the administrator of a SQL Server 2008 instance.
There is a Windows group named Wiikigo\Service. According to the security policy, members of this group are allowed to set up new connections to the SQL Server instance.
But this is can only be done during office time from 8:00-20:00.
Other users may connect to the SQL Server instance any time.
You write the following Transact-SQL statements:

CREATE TABLE Security.RestrictedLogonHours (
Id in NOT NULL IDENTITY(1,1),
GroupName sysname NOT NULL,
RestrictedTimeStart time NOT NULL,
RestrictedTimeStop time NOT NULL,
CONSTRAINT RestrictedLogonHours_pk
PRIMARY KEY CLUSTERED(Id)
);

INSERT INTO Security.RestrictedLogonHours (
GroupName,
RestrictedTimeStart,
RestrictedTimeStop
)
VALUES (
'CONTOSO\Sales',
CAST('07:00' AS time),
CAST('19:00' AS time)
);

You need to implement the company security policy which is stored in the RestrictedLogonHours table.
What should you do?

Select one of the following:

You should create a SQL Server Agent job. The job causes the SQL Server Windows service to pause during non-business hours.
You should create a logon trigger. The trigger disallows the connection to the WIIKIGO\Service group during non-business hours
You should create a SQL Server Agent job. The job periodically looks for and kills connections made by the WIIKIGO\Service group during non-business hours.
You should create a policy. The policy uses a condition based on the Server Audit facet and disallows the connection to the WIIKIGO\Service group during non-business hours.

Explanation

Question 73 of 186

1

You are the administrator of a SQL Server 2008 instance.
There is a database developer who is named UserJack.
UserJack views the definitions of all database objects in a database to read data from all user-defined tables, views, and table-valued functions.
For UserJack, you have to assign the required permissions.
Besides this, you must make sure that other developers can also be given the same permissions, but this should be achieved by executing as little Transact-SQL statements as possible.
Which Transact-SQL statements should you execute?

Select one of the following:

GRANT VIEW ANY DEFINITION TO UserJack;
EXEC sp_addrolemember 'db_datareader', 'UserJack';
CREATE ROLE Developers;
GRANT CONTROL TO Developers;
EXEC sp_addrolemember 'Developers', 'UserJack';
CREATE ROLE Developers;
GRANT VIEW DEFINITION TO Developers;
GRANT SELECT TO Developers;EXEC sp_addrolemember 'Developers', 'UserJack';
CREATE ROLE Developers;
EXEC sp_addrolemember 'sp_dbdatareader', 'Developers';
EXEC sp_addrolemember 'sp_dbddladmin', 'Developers';
EXEC sp_addrolemember 'Developers', 'UserJack';

Explanation

Question 74 of 186

1

You are the administrator of a SQL Server 2008 instance with a database named DB1.
The DB1 database contains the following stored procedure. (Line numbers are useded for reference only.)
01 CREATE PROCEDURE Sales.Procedure1
02 AS
03 IF OBJECT_ID('Service.Table') IS NOT NULL
04 DROP TABLE Service.Table;
06 CREATE TABLE Service.Table (
07 Id int PRIMARY KEY CLUSTERED,
08 Name varchar(100);
09 );
11 ...
12 GO
The following exception is raised when a user tries to invoke Procedure1, "Msg 262, Level 14, State 1, Procedure Procedure1, Line 5 CREATE TABLE permission denied in database 'DB1'."
You should grant the user access to execute Procedure1, you must assign only the required permissions.
Which action should you perform?

Select one of the following:

Between lines 01 and 02, you should insert the WITH EXECUTE AS 'dbo' clause.
Between lines 01 and 02, you should insert the EXECUTE AS USER = 'dbo' statement.
You should give the user the ALTER permission on the Service schema
You should give the CREATE TABLE permission and permit the user to drop the Service.Table table.

Explanation

Question 75 of 186

1

You are the administrator of a SQL Server 2008 instance with a database named DB1.
DB1 has a table which is named Table01 and a stored procedure named Procedure01.
Procedure01 choose data from Table01 by using a sp_executesql Transact-SQL statement.
You company security rules forbid users to access tables directly in any database.
Look at the exception below:
"Msg 229, Level 14, State 5, Line 1 The SELECT permission was denied on the object 'Table01', database 'DB1', schema 'dbo'."
The exception is raised when Procedure01 is executed by users.
You have to make sure that a user can successfully execute Procedure1 complying with the company rules.
Which action should you perform to achieve this goal?

Select one of the following:

You should execute the GRANT SELECT ON dbo.Table01 TO User1 Transact-SQL statement.
You should execute the GRANT EXECUTE ON dbo.Procedure1 TO User1 Transact-SQL statement.
You should alter Procedure01 and add the WITH EXECUTE AS OWNER option to its header
You should alter Procedure01 and add the EXECUTE AS USER = 'dbo' option immediately before the call to the sp_executesql stored procedure.

Explanation

Question 76 of 186

1

You are the administrator of a SQL Server 2008 instance with a database named DB1.
DB1 is available to a Windows group which is named WIIKIGO\Supervisors.
A Windows user named User1 is a member of the WIIKIGO\Supervisors group. WIIKIGO\Supervisors is a member of the db_owner role in the DB1 database.
You have to make sure that the SQL Server instance is (not?) available to User1.
In the DB1 database, which Transact-SQL statement(s) should you execute?

Select one of the following:

DROP LOGIN "WIIKIGO\User1";
EXEC dbo.sp_droprolemember 'db_owner', 'WIIKIGO\User1';
EXEC dbo.sp_revokedbaccess 'WIIKIGO\User1';
EXEC dbo.sp_revokelogin 'WIIKIGO\User1';
CREATE LOGIN "WIIKIGO\User1" FROM Windows;
DENY CONNECT SQL TO "WIIKIGO\User1";

Explanation

Question 77 of 186

1

You are the administrator of a SQL Server 2008.
There are queries that have an estimated execution cost less than 3.
You intend to configure the instance, make it use a single thread for these queries.
Which sp_configure configuration option should you set?

Select one of the following:

You should set query governor cost limit
You should set cost threshold for parallelism
You should set priority boost
You should set precompute rank

Explanation

Question 78 of 186

1

You are the administrator of a SQL Server 2008.
According to the company requirement, you intend to perform an installation of an instance of SQL Server Reporting Services (SSRS) to the same machine.
Now your company CIO wants version histories of all deployed reports can be kept.
As a technical support, what should you do to ensure this?

Select one of the following:

You should use the http.sys listener to install the SSRS instance.
You should configure the SSRS database, make it use Native mode
You should configure the SSRS database, make it use SharePoint integrated mode
You should use the Internet Information Services default Web site to install the SSRS instance

Explanation

Question 79 of 186

1

You are the administrator of a default SQL Server 2005 instance.
For a new application, you have to install a SQL Server 2008 instance on the server.
Your company CIO assigns this task to you.
You have to make sure that the respective certified third-party applications have access to both database instances.
You have to achieve this goal without changing the existing application environments.
What should you do to achieve this goal by using as little database administrative effort as possible?

Select one of the following:

You should install SQL Server 2008 as a named instance, and make the new application to use the new instance.
You should install SQL Server 2008 as the default instance, and make the new application to use the default instance.
You should upgrade the SQL Server 2005 application to use SQL Server 2008.
You should upgrade the SQL Server 2005 instance to a SQL Server 2008 instance.

Explanation

Question 80 of 186

1

You are the administrator of SQL Server 2008 instance.
There is a single Database Mail profile. Reports are sent to all the company employees by using the Mail profile. During office hours, a large volume of reports are sent by the sp_send_dbmail stored procedure.
Now you notice that it takes a long time for the reports to be sent to the company manager.
As the technical support, you have to speed up the delivery.
Which action should you perform to achieve this goal?

Select one of the following:

Another SMTP account should be added to the existing Database Mail profile
When you send the reports to the manager, you should use the @importance = high parameter.
You should change the Account Retry Attempts option of the Database Mail system properties
You should configure a new Database Mail profile. The new one will be used for sending the reports to the manager.

Explanation

Question 81 of 186

1

You are the administrator of a SQL Server 2008 instance with a database named DB1.
You put forward a database maintenance plan.
The maintenance plan rebuilds indexes, checks database integrity, writes a report to a text file.
The company requires that when the maintenance plan executes abnormally, the operators can be notified by an e-mail message.
You've been assigned this task.
So you have to make modifications on the SQL Server instance.
What should you do?

Select one of the following:

For the SQL Server Agent, you should enable a fail-safe operator
You should modify the database maintenance plan, making it send an e-mail to the appropriate operators
You should modify the database maintenance plan, making it include a Notify Operator task that e-mails the appropriate operators.
You should modify the SQL Server Agent job. The job executes the database maintenance plan to notify the appropriate operators.

Explanation

Question 82 of 186

1

You are the administrator of a SQL Server 2008 instance with a database named Finance.
The Finance database currently contains 100 GB of data. On an average, 1 GB of data is modified or inserted daily.The recovery model of the Finance database is set to Simple.
Business requirements specify that data loss of more than two hours worth of transactions during business hours is unacceptable.
You need to select a backup strategy that uses the least amount of disk space by adhering to the business requirements.
What should you do?

Select one of the following:

Perform a full database backup once daily.
Perform a full database backup once daily.
Perform a differential backup every two hours during business hours.
Perform a full database backup once daily.
Perform a transaction log backup every two hours during business hours.
Perform a full database backup once every week.
Perform a differential backup once daily.
Perform a transaction log backup every two hours during business hours.

Explanation

Question 83 of 186

1

You are the administrator of a SQL Server 2008 instance named Instance01.
Instance01 contains a database which is named SellingHelp.
There isthe Products table in the SellingHelp database. Now according to the company requirement, the Products table has to be replicated to the SQL Server instances installed in laptops computers.
You have to create a Replication topology to perform this.
The Products table is updated from Instance01 when the laptops reconnect to the corporate network.
The Products table on Instance01 is frequently updated between reconnections.
You have to make sure that you can implement the Replication topology along with the Subscription type successfully and meanwhile reduce the bandwidth usage to the least.
Which action should you perform to achieve this goal?

Select one of the following:

You should implement the Snapshot Replication topology along with a Push Subscription.
You should implement the Merge Replication topology along with a Pull Subscription.
You should implement the Snapshot Replication topology along with a Pull Subscription.
You should implement the Transactional Replication topology along with a Pull Subscription

Explanation

Question 84 of 186

1

You are the administrator of a SQL Server 2008 instance.
According to the requirement of the company CIO, users on the SQL Server instance must be able to use the OPENROWSET() function to query remote data source.
Which sp_configure configuration option should you configure?

Select one of the following:

Agent XPs
remote access
remote proc trans
Ad Hoc Distributed Queries

Explanation

Question 85 of 186

1

You are the administrator of a SQL Server 2008 instance with a database named DB1.
At the database a small percentage of data is modified everyday.
Ever day in the morning, you have to create a read-only copy of the database for reporting purposes and make sure that you use as little disk space as possible.
In the options below, which strategy should you use?

Select one of the following:

You should use database snapshots
You should use database backup and restore
You should use database backup and restore along with compression
You should use schema-bound views in a read-only database. The database resides on the same instance.

Explanation

Question 86 of 186

1

You are a database developer for your company.
You are writing a query that will search a resume text column for specified skills.
The results must include both synonyms and exact matches.
You need to write the query to fulfill the requirements.
Which Transact-SQL function should you use?

Select one of the following:

SOUNDEX
CHARINDEX
CONTAINS
PATINDEX

Explanation

Question 87 of 186

1

You are a database developer for your company.
You are developing a report that will return the customer name and phone number.
Customers might have a business number, a mobile number, and a fax number.
The report must return the first nonnull phone number for each customer.
You need to write the query that returns the required customer information for the report.
What should you use?

Select one of the following:

NULLIF
IF
REPLACE
COALESCE

Explanation

Question 88 of 186

1

You are a database developer for your company.
You are creating a stored procedure that will use a Transact-SQL cursor to share the result set with other statements in the stored procedure.
This cursor must have a fixed membership where the order of the rows does not change.
The data in the rows that are retrieved through the cursor should be updatable.
These updates should be viewable after they are made. While implementing this cursor, you need to minimize memory usage regardless of the resulting effect on the speed of the cursor.
You need to create the stored procedure to fulfill these requirements.
Which option should you use in the DECLARE CURSOR statement?

Select one of the following:

DYNAMIC
FAST_FORWARD
KEYSET
STATIC

Explanation

Question 89 of 186

1

You are the administrator of a SQL Server 2008 instance with a database named AdventureWorks.
The AdventureWorks database contains the Products table.
You create a Merge Replication topology and a Publication to replicate the Products table to the SQL Server instances at remote locations.
The Publication has a 21-day retention period.
When a user returns from a one-month vacation, she discovers that her database does not contain the most recent data.
The Windows Event log states the following error message:
"Replication: expired subscription dropped."
You need to obtain the most recent data in the database of the user.
You also need to ensure that future data changes are appropriately replicated.
What should you do?

Select one of the following:

Recreate the publication.
Upload unsynchronized changes.
Upload unsynchronized changes, and then reinitialize the publication.
Reinitialize the publication and immediately generate a new snapshot.

Explanation

Question 90 of 186

1

You are the administrator of three SQL Server 2008 instances named Instance1, Instance2, and Instance3.
Each of the three instances runs on a separate server.
A mission-critical database is mirrored between Instance1 and Instance2.
Instance3 acts as the witness. Instance1 currently acts as the Principal.
You plan to apply a patch to both servers.
The patch requires a restart of the server.
You need to find out the sequence of steps necessary to ensure that the following requirements are met:
1. The patching process is completed in the shortest possible time.
2. The database is online on the partner that currently does not apply the patch.
3. The database does not failover to the other partner during this time.
What should you do?

Select one of the following:

Apply the patch to the server that runs Instance2.
Apply the patch to the server that runs Instance1.
Apply the patch to the server that runs Instance2.
Manually failover the mirroring session.
Apply the patch to the server that runs Instance1.
Remove the mirroring session.
Apply the patch to the server that runs Instance2.
Apply the patch to the server that runs Instance1.
Re-establish the mirroring session.
Suspend the mirroring session.
Apply the patch to the server that runs Instance2.
Resume the mirroring session.
Manually failover the mirroring session.
Apply the patch to the server that runs Instance1.

Explanation

Question 91 of 186

1

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

Select one of the following:

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.
Create a SQL Agent job process on each instance to store and delete performance data in a single database for all instances.
Configure a management data warehouse process on each instance to store and delete performance data in a single database for all instances.
Configure an automated server-side trace process on each instance to store and delete performance data in a single database for all instances.

Explanation

Question 92 of 186

1

You are the administrator of a SQL Server 2008 instance. .
You plan to maintain a management data warehouse that collects performance data by using the data collector.
You need to implement a process that routinely gathers and uploads data in the management data warehouse on different schedules.
What data collection process should you implement?

Select one of the following:

Create a cached data collection.
Create a scheduled non-cached data collection.
Create an on-demand non-cached data collection.
Create two different SQL Agent jobs that are scheduled at the same time.
One job creates a data collection and the other job uploads the data collection.

Explanation

Question 93 of 186

1

You are the administrator of a SQL Server 2008 instance.
Users report that the performance of the application is poor.
You use SQL Profiler to capture a workload of the remote instance to a trace table on the remote SQL Server instance.
You need to analyze the workload of the remote SQL Server instance on a local SQL Server instance by using the Database Engine Tuning Advisor.
What should you do?

Select one of the following:

Use the data collector to recapture the workload.
Use SQL Profiler to recapture the workload to a trace file.
Enable the XP_MSVER stored procedure on the local server.
Enable the XP_MSVER stored procedure on the remote server.

Explanation

Question 94 of 186

1

You are the administrator of a SQL Server 2008 instance.
A user named Mary reports that she is waiting for a query to complete.
You need to ascertain whether the query is blocked.
Which tool should you use?

Select one of the following:

The Windows System Monitor tool
The Database Engine Tuning Advisor tool
The Activity Monitor tool in Microsoft SQL Server Management Studio
The Job Activity Monitor tool in Microsoft SQL Server Management Studio

Explanation

Question 95 of 186

1

You are the administrator of a SQL Server 2008 instance that runs on a computer that hosts several applications.
You configure the SQL Server Agent service to run by using the SERVER1\AGENT account.
You create a job named MailingList that requires a file to be written to a file server.
The job fails to run because it does not have appropriate access to the file server.
You plan to configure the SQL Server Agent service.
You need to perform the configuration such that only the SQL Server Agent service has read and write access to the file server.
Which account type should you use?

Select one of the following:

Domain account
Local System account
Local Service account
Network Service account

Explanation

Question 96 of 186

1

You are the administrator of a SQL Server 2008 instance with 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?

Select one of the following:

Create a filtered index on the TelevisionSize column.
Create a clustered index on the TelevisionSize column.
Create a unique clustered index on the TelevisionSize column.
Create a view on the Products table by filtering on the TelevisionSize column.

Explanation

Question 97 of 186

1

You are the administrator of a SQL Server 2008 instance.
The instance contains an On-Line Analytical Processing (OLAP) database along with a dimension table named Customers.
The data of the Customers table is updated every hour.
The Customers table contains redundant data.
You need to conserve the disk space used to store the Customers table.
Which compression technology should you use?

Select one of the following:

Row compression
Page compression
Backup compression
Windows NTFS file system compression

Explanation

Question 98 of 186

1

You are the administrator of a SQL Server 2008 instance with a database named AdventureWorks.
The AdventureWorks database contains two tables as shown in the following table:
1. The Orders table contains a table partition for each month.
2. The OrderHistory table contains two table partitions; one partition contains all data and the other partition is empty.
3. You plan to develop a process to move data from the partition that contains data for the oldest month of the Orders table to the appropriate partition of the OrderHistory table.
4. You need to ensure that the process can be repeated at the end of every month.
What should you do?

Select one of the following:

Alter the partition function of the Orders table by using the split option.
Alter the Orders table by using the merge option.
Alter the partition function of the OrderHistory table by using the split option.
Switch the appropriate partition of the Orders table to the appropriate partition of the OrdersHistory table.
Alter the OrderHistory table by using the split option.
Alter the partition function of the Orders table by using the split option.
Alter the OrderHistory table by using the split option.
Alter the partition function of the OrderHistory table by using the merge option
Alter the Orders table by using the merge option.
Alter the partition function of the OrderHistory table by using the split option. Switch the appropriate partition of the Orders table to the appropriate partition of the OrdersHistory table. Alter the OrderHistory table by using the merge option. Alter the Orders table by using the merge option. e model and master databases for the data

Explanation

Question 99 of 186

1

You are the administrator of a SQL Server 2008 instance with a database named Finance.
Minimally logged operations are performed on the Finance database.
You need to verify that the database can be restored to a specific point in time.
What should you do?

Select one of the following:

Verify that the database uses the full recovery model
Verify that the database uses the simple recovery model.
Verify that the database uses the bulk-logged recovery model.
Verify that the database uses the checksum page verify option.

Explanation

Question 100 of 186

1

You are the administrator of a SQL Server 2008 instance with a database named DB1.
You have set the recovery model of DB1 to Full.
The table below shows the backup strategy for DB1.
Your company stores the system databases, user database data, and log files on separate disks.
A user report that the hard disks which store the user database data files have failed at 15:00 on Wednesday. According to the requirement of the company CIO, you have to restore DB1 from the database backups, and you must reduce the data loss as soon as possible.
Which action should you perform to achieve this goal?

Day Sunday Weekdays Weekdays
Type of Backup Full backup Differential backup Transaction Log backup
Time 01:00 22:00 8:00 12:00 16:00

Select one of the following:

First you should restore the full backup taken on Sunday. Then you restore the differential backup taken on Wednesday.
First you should back up the transaction log. Then restore the full backup taken on Sunday. At last restore all transaction log backups
First you should back up the transaction log. Second, restore the full backup taken on Sunday. Third, restore the differential backup taken on Tuesday and restore all transaction log backups taken during Wednesday.
First you should restore the full backup taken on Sunday. Then restore the differential backup taken on Monday and Tuesday and restore the transaction log backups taken at 08:00 hours and at 12:00 hours on Wednesday.

Explanation

Question 101 of 186

1

You are the administrator of a SQL Server 2008 instance.
The instance contains a database named Finance.
The recovery model of the Finance database is set to Full.
You deploy a new process that modifies 10,000 records from the Accounts table at 19:00 hours daily.
You need to ensure that any modification to the data can be reverted without the database going offline.
Which strategy should you implement?

Select one of the following:

Database snapshots
Differential backup
Transaction log backup
Primary filegroup backup

Explanation

Question 102 of 186

1

You are the administrator of a SQL Server 2008 instance.
The instance that runs on a Windows Server 2003 computer. with mixed authentication mode.
You need to ensure that the SQL Server 2008 authenticated logins follow the same password complexity rules that are enforced by Windows Server 2003.
You also need to ensure that the password complexity rules continue to be enforced.
Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)

Select one or more of the following:

Use the ALTER LOGIN ... HASHED statement to modify all logins.
Use the ALTER LOGIN ... CHECK_POLICY = ON statement to modify all logins.
Use the ALTER LOGIN ... CHECK_EXPIRATION = ON statement to modify all logins.
Use Policy-Based Management to create a policy that prevents any violation of these rules.
Create an SQL Server Agent job that runs periodically to raise an alert if a rule violation is detected.

Explanation

Question 103 of 186

1

You maintain a SQL Server 2008 instance.
You create a new SQL Agent job that includes a Windows PowerShell job step.
The PowerShell job step uses the SQLCmd utility to transfer data between servers.
You need to ensure that an operator named ResponseTeam is notified by an e-mail message if the job fails.
What should you do?

Select one of the following:

Enable Notifications.
Create the ResponseTeam operator.
Direct the Notification to the ResponseTeam operator on job failure.
Enable Notifications.
Create the ResponseTeam operator.
Direct the Notification to the ResponseTeam operator on failure of the Powershell job step.
Create the ResponseTeam operator.
Assign the ResponseTeam operator as the fail safe operator.
Enable the job.
Create the ResponseTeam operator.
Configure the job step proxy account to use the ResponseTeam operator account.
Select the proxy account for the e-mail profile.

Explanation

Question 104 of 186

1

You are the administrator of a SQL Server 2008 instance.
The instance has databases for a finance application and a manufacturing application.
You want to put some limitation on the maximum CPU query time allowed by the applications, so you configure the Resource Governor to achieve this.
But you notice that certain reports do not execute successfully any longer on the manufacturing application.
You have to allow the manufacturing application to consume more CPU time, so you have to change the Resource Governor configuration.
Which Resource Governor component should you configure?

Select one of the following:

The workload group which is used by default
The workload group which is used by the finance application
The classifier function which identifies the finance application
The workload group which is used by the manufacturing application

Explanation

Question 105 of 186

1

You are the administrator of a SQL Server 2008 instance.
For some reason, the company wants to view the number of users who access the SQL Server instance. The company CIO assigns this task to you.
You have to use Windows System Monitor to find out the concrete number.
Which performance object should you capture?

Select one of the following:

SQLServer:Buffer Manager
SQLServer:Access Methods
SQLServer:General Statistics
SQLServer:Exec Statistics

Explanation

Question 106 of 186

1

You are the administrator of a SQL Server 2008 instance with a database named Dworks.
The Dworks database has a table named Bills which contains several indexes and a great amount of rows.An enterprise Web environment is supported by the database. On the Bills table 28% of the indexes have been fragmented.
You have to defragment the indexes, making sure that you reduce the effect on database availability to the least.
In the ALTER INDEX statement, which option should be included?

Select one of the following:

Sort in tempdb
Online
Fill factor
Pad index

Explanation

Question 107 of 186

1

You are the administrator of a SQL Server 2008 instance with a database named named Dworks.
The table below shows the two tables of the Dworks database:
There are two partitions in the BillHistory table.
Partition1 is empty while partition2 contains all data.
You intend to move data from partition2 to the appropriate partition of the BillHistory table.
Partions2 contains data for the oldest month of the Bills table.
You are going to develop a process toachieve this.
You have to make sure that at the end of every month the process can be repeated.
Which actions should you take?

Table Name Function
Bills Store bill information of the current year
BillHistory Store bill information of the last years

Select one of the following:

1. Alter the partition function of the BillHistory table by using the split option.
2. Switch the appropriate partition of the Bills table to the appropriate partition of the BillHistory table.
3. Alter the BillHistory table by using the split option.
1. Alter the partition function of the Bills table by using the split option.
2. Alter the Billstable by using the merge option.
1. Alter the partition function of the BillHistory table by using the split option.
2. Switch the appropriate partition of the Bills table to the appropriate partition of the BillHistory table.
3. Alter the BillHistory table by using the merge option.
4. Alter the Bills table by using the merge option.
1. Alter the partition function of the Bills table by using the split option.
2. Alter the BillHistory table by using the split option.
3. Alter the partition function of the BillHistory table by using the merge option.
4. Alter the Bill stable by using the merge option.

Explanation

Question 108 of 186

1

You are the administrator of a SQL Server 2008 instance.
The company wants to export data from SQL Server to a Microsoft Excel file.
The company assigns this task to you.
You perform this by using the SQL Server Import and Export Wizard.
After this, you save the package definition to a file.
Now you have to reexecute the package from the command line.
Which utility should you choose to use?

Select one of the following:

bcp.exe
dta.exe
dtexec.exe
sqlmaint.exe

Explanation

Question 109 of 186

1

You are the administrator of a SQL Server 2008 instance with a database named named Dworks.
The Dworks database contains a table named BillDetails which has a clustered primary key named BillId on the BillId column.
The BillDetails table has a single XML column named GoodsDetails.
The XML column has an XML index and XML data are stored in this XML column.
A new column named GoodsID is added to the BillDetails table.
GoodsID must be included in the primary key.
Which action should you perform to achieve this goal?

Select one of the following:

You should drop the XML index on the table. Modify the primary key. Recreate the XML index
You should alter the XML index and set the ALLOW_ROW_LOCKS = OFF option.
Alter the primary key and set the ALLOW_ROW_LOCKS = ON option.
You should move the XML data to a temporary table.
Clear the XML data from the original table by setting the GoodsDetails column to NULL.
Modify the primary key. Repopulate the ProductSpecs column.
You should disable the XML index on the GoodsDetails column.
Modify the primary key.
Enable the XML index on the ProductSpecs column.

Explanation

Question 110 of 186

1

You are the administrator of a SQL Server 2008 instance with a database named DB1.
According to the requirements of the security audit policy only successful and failed logon attempts are recorded in log files.
if records cannot be written to the log files, the SQL Server instance is shut down.
You have to make the SQL Server instance comply with the security audit policy by performing
the configuration.
Which Transact-SQL statements should you run?

Select one of the following:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'default trace enabled', 1;
GO
RECONFIGURE
GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'common criteria compliance enabled', 1;
GO
RECONFIGURE
GO
CREATE SERVER AUDIT Srv_Audit TO FILE ( FILEPATH ='\\MAIN_SERVER\Audit\' ) WITH (ON_FAILURE = SHUTDOWN);
GO
CREATE DATABASE AUDIT SPECIFICATION Audit_Specification FOR SERVER AUDIT Srv_Audit
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (FAILED_LOGIN_GROUP) WITH (STATE=ON);
GO
ALTER SERVER AUDIT Srv_Audit WITH (STATE=ON);
GO
CREATE SERVER AUDIT Srv_Audit TO FILE ( FILEPATH ='\\MAIN_SERVER\Audit\' ) WITH (ON_FAILURE = SHUTDOWN);
GO
CREATE SERVER AUDIT SPECIFICATION Audit_Specification FOR SERVER AUDIT Srv_Audit
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (FAILED_LOGIN_GROUP) WITH (STATE=ON);
GO
ALTER SERVER AUDIT Srv_Audit WITH (STATE=ON);
GO

Explanation

Question 111 of 186

1

You are the administrator of a SQL Server 2008 instance which uses mixed authentication mode.
The instance runs on a computer which has Windows Server 2003 installed.
You have to make sure that the SQL Server 2008 authenticated logins follow the same password complexity rules that are enforced by Windows Server 2003.
Besides this, you must make sure that the password complexity rules continue to be enforced.
Which action should you perform to achieve this goal? (choose more than one)

Select one or more of the following:

You should create an SQL Server Agent job. If a rule violation is detected, the job runs periodically to raise an alert
You should modify all logins by using the ALTER LOGIN ... HASHED statement.
You should modify all logins by using the ALTER LOGIN ... CHECK_POLICY = ON statement to.
You should modify all logins by using the ALTER LOGIN ... CHECK_EXPIRATION = ON statement.
You should create a policy by using Policy-Based Management. The policy prevents any violation of these rules

Explanation

Question 112 of 186

1

Your workstation has installed Microsoft SQL Server Management Studio (SSMS), and not installed Microsoft Business Intelligence Development Studio (BIDS).
Which is correct?

Select one of the following:

You should create the package by using DTS Designer.
You should create the package by using the Package Migration Wizard.
You should create the package by using the Microsoft SQL Server Import and Export Wizard.
On the Microsoft SQL Server 2005 Integration Services (SSIS) menu, you should click the Create Package submenu.

Explanation

Question 113 of 186

1

You are mastering the company database called TesterDB;
The database includes a database that uses SQL Server log shipping.
Log shipping will happen half a hour.
You should make the full backup.
In order to cut the net bandwidth utilization when maintenance every night.
Which is the correct answer?

Select one of the following:

You should set the database to utilize the bulk-logged recovery model.
You should reproduce only those indexes that ask it every night.
You should add the time during transaction log backups.
You should disable log shipping after the nightly maintenance.

Explanation

Question 114 of 186

1

You are mastering the company database, Users complaints that the SQL Server 2008 application is running slowly.
You should check the information of the CPU utilization, disk utilization, and memory utilization. The network should be considered.
The detailed information should be checked for the proper example of SQL Server2008.
Which is the correct answer?

Select one of the following:

You should check the proper dynamic management views (DMVs).
In the databases, you should check the distribution of the statistics which are hosted
You should check a statistics update commands the output.
You should check the recovery model of the master database.

Explanation

Question 115 of 186

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?

Select one of the following:

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

Explanation

Question 116 of 186

1

You are mastering the company database, and managing 20 SQL Server 2005 computers which are operated by other administrators that are asked to meet company service level agreements (SLAs) on querying response time.
Some of the method on controlling query response times on the servers should be told for these administrators.
Which is the correct answer?

Select one of the following:

You should create a query that searches the sys.dm_db_partition_stats dynamic management view (DMV). Then distribute this search to the administrators.
You should develop an XML schema which includes the event and column names of the query response times, which should be captured in SQL Server Profiler.
You should develop SQL Server Profiler templates, which contain query start times and end times. Then distribute these templates to the database.
You should teach the administrators to use System Monitor Control Tool.

Explanation

Question 117 of 186

1

You are managing a SQL Server 2008 instance which includes some applications for Home.com.
You set the SQL Server Agent service to implement through utilizing the SerTest\Test account.
You create a job named MailingTester that allows a file to be stored to a log server.
The job could not run normally because it could not own proper access to the log server.
You decide to set the service of SQL Server Agent.
You should make sure that you could run the settings such that the SQL Server Agent service owns all access to the log server.
Which is the correct answer?

Select one of the following:

You should utilize the type of Domain account
You should utilize the type of remote System account
You should utilize the type of remote Service account
You should utilize the type of Local Service account

Explanation

Question 118 of 186

1

You are the administrator of a SQL Server 2008 instance.
You determine that you need more information about SPID 56.
Which of these do you do?

Select one of the following:

Execute sp_who 55 in SSMS
Execute sp_who 56 in SSMS
Use the Activity Monitor in Microsoft SQL Management Studio and locate process 55.
Select from the sys.syslockinfo compatibility view and locate spid 55.

Explanation

Question 119 of 186

1

You administer a SQL Server 2008 instance.
The instance contains a database named DB1.
You plan to allow all the application developers to use SQL Server Profiler to capture traces to troubleshoot the application that uses the database DB1.
You need to grant the minimum necessary permission to the application developers.
What should you do?

Select one of the following:

Grant the appropriate database-level permissions to all database users of the application developers.
Grant the appropriate server-level permissions to all SQL Server logins of the application developers.
Add all the database users of the application developers to a fixed database role.
Add all the SQL Server logins of the application developers to a fixed server role.

Explanation

Question 120 of 186

1

You are a database administrator for your company.
The company uses a SQL Server 2008 database that includes a table named Inventory.
The table contains a column named Price. A company policy states that the value in the Price column cannot be decreased by more than 10 percent in any single database operation.
Updates to the Price column are made by various means, including by using ad hoc queries.
You need to ensure that this company policy is enforced.
What should you do?

Select one of the following:

You should develop a trigger which rolls back changes to the Price column which breaks company policy.
On the Price column, you should develop a primary key constraint to a table which includes valid prices.
You should develop a view which rolls back changes to the Price column which breaks company policy.
You should develop a stored procedure which allows changes to the Price column which breaks company policy.

Explanation

Question 121 of 186

1

You maintain a SQL Server 2008 instance that contains a database named DB1.
DB1 stores customer data for the company.
The customers use a Web application to access their profile data.
You need to protect the customer data such that data files, log files, and subsequent backups are as secure as possible even if the backup media is lost.
Your solution must not affect the Web application or impact performance.
What should you do?

Select one of the following:

Encrypt the customer data at the cell level and then back up DB1.
Configure access to DB1 to only use stored procedures and functions.
Enable Transparent Database Encryption for DB1 and then back up the transaction logs.
Encrypt the customer data at the folder level by using Encrypted File System (EFS) and then back up the transaction logs.

Explanation

Question 122 of 186

1

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?

Select one of the following:

ALTER SCHEMA Billing TRANSFER Sales.Table1;
ALTER USER Sales WITH DEFAULT_SCHEMA = Billing;
ALTER AUTHORIZATION ON Sales.Table1 TO Billing;
ALTER TABLE Sales.Table1 SWITCH TO Billing.Table1;

Explanation

Question 123 of 186

1

You are a database administrator for your company.
There are four automated testing areas for finished items in the company's manufacturing floor. SQL Server 2008 is used to reserve testing results for every testing area.
You should create a replication method to make sure that test results flow from the testing areas to the SQL Server quickly.
The results of testing will be used on reporting.
Which is the correct answer?

Select one of the following:

At each of the testing areas for the test results, you should develop a separate snapshot publication. Develop a pull subscription on each distributor to the testing areas.
At each of the testing areas for the test results, you should develop a separate snapshot publication. Develop a merge subscription on each distributor to the testing areas.
You should develop a separate transactional publication at each of the testing areas for the test results. Develop push subscriptions on each of the testing areas.
You should develop a separate transactional publication at each of the testing areas for the test results. Develop a merge subscription on each distributor to the testing areas.

Explanation

Question 124 of 186

1

You administer a SQL Server 2008 instance that contains a database named DB1.
The DB1 database contains the following stored procedure. (Line numbers are included for reference only.)
When a user named User1 attempts to invoke Procedure1, the following exception is raised:
"Msg 262, Level 14, State 1, Procedure Procedure1, Line 5 CREATE TABLE permission denied in database 'DB1'."
You need to provide User1 access to execute Procedure1 by allocating only the required permissions.
What should you do?

01 CREATE PROCEDURE Sales.Procedure1
02 AS
03 IF OBJECT_ID('Sales.Tables1') IS NOT NULL
04 DROP TABLE Sales.Table1;
05
06 CREATE TABLE Sales.Table1 (
07 Id int PRIMARY KEY CLUSTERED,
08 Name varchar(100);
09 );
10
11 ...
12 GO

Select one of the following:

Grant the ALTER permission on the Sales schema to User1.
Grant the CREATE TABLE permission and allow User1 to drop the Sales.Table1 table.
Insert the WITH EXECUTE AS 'dbo' clause between lines 01 and 02.
Insert the EXECUTE AS USER = 'dbo' statement between lines 02 and 03.

Explanation

Question 125 of 186

1

You are managing a SQL Server 2008 instance for Home.com.
You use a Microsoft SQL Server 2008 Analysis Services (SSAS) instance.
A data mining structure should be deployed which contains the Microsoft Clustering mining model. From Scalable K-Means to Non-scalable K-Means.
You should alter the attribute of the mining model.
Which is the correct answer?

Select one of the following:

You should change MODELLING_CARDINALITY
You should change CLUSTERING_METHOD
You should change INDEXDEFRAG
You should change SHRINKFILE

Explanation

Question 126 of 186

1

You are mastering the company database, including the SQL Server 2005 database which include a table called Production.
Product. The table has more than 50 columns
The most common queries are listed below Transact-SQL statements.
--QUERY 1SELECT [Name], [ProductLine], [ListPrice], [Class], [Style] FROM [Production]. [Product]WHERE [ProductLine] = 'T' --QUERY 2SELECT * FROM [Production]. [Product] ORDER BY [Name]
When you are minimizing the space required for the indexes.
You should select the best indexes to maintain the most common searched to the Production. Product table Which is the correct answer?

Select one of the following:

You should develop a clustered index on the Name column.
You should develop a nonclustered index on the Name column.
You should develop a clustered index on the ProductLine, ListPrice, Class and Style columns.
You should develop a nonclustered index on the ProductLine, ListPrice columns.

Explanation

Question 127 of 186

1

You are mastering the company database, with SQL Server 2008 computer named SQLTEST1
The master database on SQLTEST1 is down.
Some SQL Server Agent jobs should be included in the SQLTEST1.
The whole database should be backed up every day.
You rebuild and restore the master database.
You should make sure that SQLTEST1 functions as it did before the database lost.
Which is the correct answer?

Select one of the following:

You should reserve the model and master databases for the database.
You should reserve the Log Transaction for the database.
You should rebuild the whole table schema for the database.
You should rebuild the whole SQL Server Web Service for the database.

Explanation

Question 128 of 186

1

You are mastering the company database; a new SQL Server 2008 computer named TesterDB should be set by yourself.
Reporting Services will work in TesterDB.
It should be set to run database backups and other maintenance job automatically.
In the network environment, the company has no other SQL Server computers.
The whole approach SQL2 will be made by using SQL1s DNS name.
You should enable any necessary devices on SQL2.
Which is the correct answer?(select more than one)

Select one or more of the following:

You should utilize SQL Server Web Service technology
You should utilize SQL Server Browser technology
You should utilize Internet Information Services technology
You should utilize Microsoft Distributed Transaction Coordinator technology

Explanation

Question 129 of 186

1

You are mastering the company database called Testing;
you should back up the Testing database fully.
You do transaction log once every two hours from 08:00 to 17:00.
You should run database snapshots for the Testing database.
You should develop database snapshot every day at 07:00.
At 8:30 the user removes the whole information on the Testing database which was entered intothe CurrentSpeaker table yesterday. Deletion is the first step for the database.
It is now 11:45 and many other changes have happened in the database.
You should find solution to solve this problem .
Which is the correct answer?

Select one of the following:

You should utilize the SELECT subquery in the INSERT statement to move the deleted rows from this mornings database snapshot to the CurrentSpeaker table.
You should utilize the Delete subquery in the INSERT statement to transfer the deleted rows from yesterday mornings database snapshot to the CurrentSpeaker
You should reserve last nights Full Backup and all transaction log backups until the 10:00 backup. Use the STOP AT statement on the first restores to prevent data loss
You should reserve last nights Full Backup and all transaction log backups until the 10:00 backup. Utilize the STOP AT statement on the last restore to prevent data loss

Explanation

Question 130 of 186

1

You are mastering the company database,
you should check the log of the SQL Server 2008 Enterprise Edition everyday, and you get the information on the torn page errors of the database.
In order to fine the solution of the torn page, which is the correct answer?

Select one of the following:

You backup to restore only the torn page. Then restore any transaction logs which have been made since the full backup.
You should reserve the newest database backup and then reserve any transaction logs which have been made since the past backup.
You should operate DBCC CHECKDB.
You should reserve the database from the newest database snapshot.

Explanation

Question 131 of 186

1

You administer a SQL Server 2008 instance.
The instance contains various SQL Server Agent jobs that use seven shared schedules to run each job on each day of the week.
You need to ensure that a job named Job5 no longer runs on Thursdays.
What should you do?

Select one of the following:

Delete the schedule for Thursday.
Disable the schedule for Thursday.
Remove the schedule for Thursday from Job5.
Add a new Job5 schedule for Thursday and disable it.

Explanation

Question 132 of 186

1

You administer a SQL Server 2008 instance.
You have a stored procedure that implements a database maintenance process.
You need to create a SQL Server Agent job that runs the stored procedure.
You also need to ensure that the job is removed after successful completion.
What should you do?

Select one of the following:

Create a job that is scheduled to run once.
Create a job that uses the automatically delete job option.
Create a job that is assigned to the Database Maintenance category.
Create an Alert that will be raised when the job completes. This Alert will run another job to delete the maintenance job.

Explanation

Question 133 of 186

1

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?

Select one of the following:

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;
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;
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';
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';

Explanation

Question 134 of 186

1

You are mastering the company database, with SQL Server 2008 computer named SQLDB.
The function of the SQLDB is to perform transaction log backups and so on.
SQL1 should be reset by another administrator using the tool of SQL Server Surface Area Configuration.
You should see that SQL1 no longer runs the maintenance tasks automatically.
On SQLDB, You should make sure the tasks of the maintenance are finished without men
Which is the correct answer?

Select one of the following:

You should reset the Web service to log on by using the role of administrator.
You should reset the server so that the SQL Server Agent service starts automatically.
You should reset operate a full backup of the master database by hand.
You should reset SQLDB to use Windows Integrated authentication.

Explanation

Question 135 of 186

1

You are mastering the company database,
On a SQL Server 2008, you find out that one of the data files computer is broken.
You should reserve the database which is from the most recent configurations of backups.
In order to cut the lost, you should find the method as quickly as possible.
Which is the correct answer?

Select one of the following:

You should run a transaction log backup for the database.
You should reserve the old database backup for the database.
You should reserve the most recent store produce log backup for the database.
You should run the whole database backup.

Explanation

Question 136 of 186

1

Your workstation has installed Microsoft SQL Server Management Studio (SSMS), and not installed Microsoft Business Intelligence Development Studio (BIDS).
You should design a package, which has the following features:
- The package should be transactional.
- The package should be optimized for 10 tables.
- The package should be stored safely in the msdb database of a server which is remote.
Which is correct?

Select one of the following:

You should create the package by using DTS Designer.
You should create the package by using the Package Migration Wizard.
You should create the package by using the Microsoft SQL Server Import and Export Wizard.
On the Microsoft SQL Server 2005 Integration Services (SSIS) menu, You should click the Create Package submenu.

Explanation

Question 137 of 186

1

You maintain a SQL Server 2008 instance.
You find a SQL Server Agent job is failing.
When you review the job history information, you notice that the job history information is incomplete and appears to be truncated.
You need to ensure that all information produced by a job is available for viewing.
What should you do?

Select one of the following:

Enable write OEM file.
Enable all job steps to send the output to a file.
Include execution trace messages in the SQL Agent Error log.
Enable notifications to the Windows application event log when the job completes.

Explanation

Question 138 of 186

1

You administer a SQL Server 2008 instance that contains a database named SpatialDB.
The SpatialDB database includes spatial data types.
You need to perform a database consistency check on SpatialDB to include the spatial indexes. You also need to ensure that the effect on the database concurrency is minimized.
Which Transact-SQL statement should you execute?

Select one of the following:

DBCC CHECKCATALOG (SpatialDB);
DBCC CHECKALLOC (SpatialDB) WITH TABLOCK;
DBCC CHECKDB (SpatialDB) WITH TABLOCK. PHYSICAL_ONLY:
DBCC CHECKDB (SpatialDB) WITH EXTENDED_LOGICAL_CHECKS:

Explanation

Question 139 of 186

1

You are mastering the company database; there is the transaction information which is for company's Web-based order system in the database of the company.
The database is set to utilize the full recovery model.
Between the hours of 20:00 and 04:00, you get the fewest orders; one hour should be accepted for the risk when the system is running Database backups should be run as quickly as possible.
You should create the solution to avoid this kind of risk.
What should you do?

Select one of the following:

You should set a Back up Database job to run the whole backup of the database every hour. Set a Check Database Integrity task to run every day in order to avoid corruption.
You should set a Back up Database job to do the whole backup of the database every day at 20:00. Set a second Back up Database task to run differential backups every hour.
Set a third Back up Database job to run transaction log backups every hour.
You should set a Back up Database task to do the whole backup of the database every day at 20:00. Configure a second Back up Database job to run differential backups at 12:00.
Configure a Check Database Integrity task to run every hour.
You should set a Back up Database job to run the whole backup at 09:00 and 14:00. Set a second Back up Database task to do transaction log backups every 30 minutes.

Explanation

Question 140 of 186

1

You are managing two SQL Server 2008 computers called SQLTEST1 and SQTEST2, which include a copy of a database named Sales.
The database is replicated between SQL1 and SQL2 by using transactional replication.
A full backup of each database is performed every night.
Transaction log backups are performed every hour.
Replication latency is typically less than two minutes.
One afternoon, the Sales database on SQLTEST1 becomes corrupted.
You are unable to repair the database.
The Sales database on SQLTEST2 is unaffected.
You need to return the Sales database on SQLTEST1 to normal operation as quickly as possible.
You must ensure a minimum loss of data and minimal impact to users of either server.
What should you do?

Select one of the following:

You should reserve the most recent full database backup and the whole transaction logs made since the past backup could be made.
You should reserve only the newest transaction log backup.
You should detach the Sales database on SQL2. Copy the database file to SQL1, and link the database on both servers.
You should reserve a full database backup on SQL2. Reserve the backup to SQL1.

Explanation

Question 141 of 186

1

You maintain a SQL Server 2008 Enterprise Edition instance that contains a database named Finance.
You need to reduce the size of the full database backup files of the Finance database.
Which Transact-SQL statement should you use?

Select one of the following:

BACKUP DATABASE FINANCE TO DISK = 't:\backups\finance.bak*;
BACKUP DATABASE FINANCE TO DISK = ,t:\backups\finance.bak' WITH COMPRESSION:
BACKUP DATABASE FINANCE TO DISK = ,t:\backups\finance.bak' WITH DIFFERENTIAL:
BACKUP DATABASE FINANCE TO DISK = 't:\backups\finance.bak' WITH COMPRESSION- DIFFERENTIAL;

Explanation

Question 142 of 186

1

You are managing a SQL Server 2008 instance which includes a database called TesterDB for Home.com.
The data file and the transaction log file which are located on the D: drive that owns only 10 percent useful space.
You should make sure that you move both files to the G: drive.
Which is the correct answer?

Select one of the following:

You should implement the statement below:
ALTER DATABASE Finance SET RESTRICTED_USER WITH ROLLBACK_IMMEDIATE;
Move the data file and run the following Transact-SQL statements.
ALTER DATABASE Finance MODIFY FILE(NAME = Finance_Data, FILENAME = G:\Data\Finance_Data.mdf');
ALTER DATABASE Finance SET MULTI_USER;
You should utilize the following Transact-SQL statement.
ALTER DATABASE TesterDB SET OFFLINE WITH ROLLBACK_IMMEDIATE;
ALTER DATABASE TesterDB
SET FILEEXTENSIONS('mdf', 'ldf');
ALTER DATABASE TesterDB SET ONLINE;

Explanation

Question 143 of 186

1

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?

Select one of the following:

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

Explanation

Question 144 of 186

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?

Select one of the following:

RESTORE DATABASE Finance
FROM DISK = 't:\backups\finance.bak'
WITH NORECOVERY;
RESTORE LOG Finance
FROM 't:\backups\finance_0830.trn'
WITH RECOVERY, STOPAT = 'Mar 17, 2008 08:23 AM';
RESTORE DATABASE Finance
FROM DISK = 't:\backups\finance.bak';
RESTORE LOG Finance
FROM 't:\backups\finance_0815.trn'
WITH NORECOVERY;
RESTORE LOG Finance
FROM 't:\backups\finance_0830.trn'
WITH RECOVERY, STOPAT = 'Mar 17, 2008 08:23 AM';
RESTORE DATABASE Finance
FROM DISK = 't:\backups\finance.bak'
WITH NORECOVERY;
RESTORE LOG Finance
FROM 't:\backups\finance_0815.trn'
WITH NORECOVERY;
RESTORE LOG Finance
FROM 't:\backups\finance_0830.trn'
WITH RECOVERY, STOPAT = 'Mar 17, 2008 08:23 AM';
RESTORE DATABASE Finance
FROM DISK = 't:\backups\finance.bak';
RESTORE LOG Finance
FROM DISK = 't:\backups\finance_0815.trn'
WITH NORECOVERY;
RESTORE LOG Finance
FROM 't:\backups\finance_0830.trn'
WITH RECOVERY;

Explanation

Question 145 of 186

1

You are managing a SQL Server 2008 computer called SQLTEST1.
You should configure the SQL Server service and the SQL Server Agent service to start automatically, and set SQLTEST1 to audit all user names and application names that plan to get information from SQLTEST1.
SQLTEST1 must operate auditing always, and you should keep the results for 10 years.
Which is the correct answer?

Select one of the following:

You should disable the C2 Audit Tracing setting on the server properties.
You should create a SQL Server Agent job to run the SQL Trace stored procedure. Configure the job to start when the SQL Server Agent service starts.
You should set the Login auditing setting to both failed and successful logins
You should configure the server authentication mode as SQL Server and Windows Authentication mode.

Explanation

Question 146 of 186

1

You are managing two SQL Server 2008 computers called SQLTEST1 and SQTEST2, which include a copy of a database named Sales.
The database is replicated between SQL1 and SQL2 by using transactional replication.
A full backup of each database is performed every night.
Transaction log backups are performed every hour.
Replication latency is typically less than two minutes.
One afternoon, the Sales database on SQLTEST1 becomes corrupted.
You are unable to repair the database.
The Sales database on SQLTEST2 is unaffected.
You need to return the Sales database on SQLTEST1 to normal operation as quickly as possible.
You must ensure a minimum loss of data and minimal impact to users of either server.
What should you do?

Select one of the following:

You should reserve the most recent full database backup and the whole transaction logs made since the past backup could be made.
You should reserve only the newest transaction log backup.
You should detach the Sales database on SQL2. Copy the database file to SQL1, and link the database on both servers.
You should reserve a full database backup on SQL2. Reserve the backup to SQL1.

Explanation

Question 147 of 186

1

You are mastering the company database;
you should use the SQL Server 2005 database which enables business users to operate both ad hoc and predefined searches.
You doubt that some searched will cost too many resources.
You should clarify which searches cost the most resources.
You decide to finish this aim as quickly as possible.
Which is the correct answer?

Select one of the following:

You should utilize the sqldiag function
You should utilize the sys.dm_exec_query_stats dynamic management view (DMV).
You should utilize the DBCC INPUTBUFFER requirements
You should utilize the SHOWPLAN session selection.

Explanation

Question 148 of 186

1

You administer a SQL Server 2008 instance that contains a database named Sales.
A SQL Server login named SalesAppLogin maps to a database user named SalesAppUser in the Sales database.
You need to ensure that the database user can perform only the following tasks:
- Execute all stored procedures that currently exist in the Sales database.
- Execute all stored procedures that will be created in the Sales database.
What should you do?

Select one of the following:

Add SalesAppUser to the appropriate fixed database roles.
Grant the appropriate object-level permissions to SalesAppUser.
Grant the appropriate server-level permissions to SalesAppLogin.
Grant the appropriate database-level permissions to SalesAppUser.

Explanation

Question 149 of 186

1

You are mastering the company database, with a SQL Server 2008 computer called SQLTEST1.
There are three examples of SQL Server in the SQLTEST1.
The ability to connect to the dedicated administrative connection on every example of SQL Server should be included in the disaster recovery plan of the company.
You find that you could connect to the connection on SQLTEST1 default example while testing this ability; you could not connect to the dedicated connection on the two other examples on SQLTEST1.
You should clarify that the examples are implementing and that client applications should be able to approach them.
You should make sure that no other administrators are attempting to connect to any dedicated connections on SQLTEST1.
You should also make sure that you could connect to the dedicated connection on all three examples.
Which is the correct answer?

Select one of the following:

You should begin the SQL Server Browser service. Set the service to run automatically.
You should utilize the IIS tool to make the dedicated administrative connection.
You should stop and restart the default examples of SQL Server.
You should reset the default example to utilize a default port amount other than 1434.

Explanation

Question 150 of 186

1

You are mastering the company database.
During the development, you find that Transact- SQL query below is running slowly:
SELECT VideoTitle, UpcNum, RetailPrice, Release Date FROM Srvideo.
VideoTitle WITH (INDEX(0)) WHERE ReleaseDate BETWEEN '20050401' AND '20050510'
A clustered index exists on the VideoTitle column.
There is a nonclustered index on the ReleaseDate column which contains the UpcNum and RetailPrice columns. The result of the avg_fragmentation_in_percent is 30 percent, when you search the sys.dm_db_index_physical_stats dynamic management function (DMF) or the VideoTitle table,
In order to find this method to solve this problem, which is the correct answer?

Select one of the following:

You should reproduce the whole indexes on the VideoTitle table.
You should delete the query hint from the query.
You should alter the query hint to force the optimizer to force a unclustered index seek.
You should Re-create the index on only the ReleaseDate column.

Explanation

Question 151 of 186

1

You are mastering the company database; there are 15 retail stores in your company.
On its own SQL Server 2008 computer, every retail store keeps transactions of point-of-sale in a database table called Saling. The Saling table also includes sales information from other stores to enable customer returns to any of the 15 retail stores.
You could refresh the data in the main office to every retail store every hour.
You should use trigger called trg_Coupon to produce sales coupons based on customer sales and buying patterns.
Through using the fewest number of steps, you should set replication between the server in every retail store and a middle server in the mainoffice. In real time.
You should not have the Replication.
Which is the correct answer?

Select one of the following:

Snapshot replication should be utilized. You should set the trg_Coupon trigger on the server in every store to utilize the NOT FOR REPLICATION option.
You should utilize merge replication. You should set the trg_Coupon trigger on the server in each retail store to use the NOT FOR REPLICATION option.
You should utilize transactions log between the server in each retail store and the central server in the main office.
You should build multiple merge publications, one at every store and one in the main office.

Explanation

Question 152 of 186

1

You are mastering the company database,
You should check the log of the a SQL Server 2005 Enterprise Edition everyday,
You get the information on the torn page errors of the database.
In order to fine the solution of the torn page,
Which is the correct answer?

Select one of the following:

You should utilize the latest database backup to restore only the torn page. Then restore any transaction logs which have been made since the full backup.
You should reserve the newest database backup and then reserve any transaction logs which have been made since the past backup.
You should operate DBCC CHECKDB.
You should reserve the database from the newest database snapshot.

Explanation

Question 153 of 186

1

You are the administrator of a Microsoft Windows Server 2003 computer.
Your company purchases a new enterprise sales application that runs SQL Server 2008.
The application uses stored procedures that include the EXECUTE AS clause and that use Microsoft Windows accounts.
The company's written security policy states that all enterprise applications must run under the context of a service account that requires the minimum amount of privileges.
You need to configure the SQL Server service torun under the appropriate context for the installation of the new enterprise sales application.
Under which context should the SQL Server service run?

Select one of the following:

Under the context of the local system account
Under the context of the local service account
Under the context of the domain user account
Under the context of the local Administrator account

Explanation

Question 154 of 186

1

You work in a company which is named Wiikigo Corp.
The company uses SQL Server 2008.
You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
There is an Internet Information Services application.
This application will use anonymous access to access the SSAS instance.
You must make sure that the application can access the SSAS instance.
What should you do?

Select one of the following:

The Security\RequireClientAuthentication server configuration should be set to False
The Security\RequireClientAuthentication server configuration should be set to True.
The NTLM Security Support Provider Interface (SSPI) provider should be added to the Security\SecurityPackageList server configuration.
The Kerberos Security Support Provider Interface (SSPI) provider should be added to the Security\SecurityPackageList server configuration

Explanation

Question 155 of 186

1

You maintain a SQL Server 2008 instance.
You have an existing database maintenance plan that performs the following tasks:
- Rebuilds indexes.
- Checks database integrity.
- Writes a report to a text file.
You need to ensure that an e-mail message is sent to the operators when the maintenance plan fails to execute successfully.
What should you modify?

Select one of the following:

The instance.
The SQL Server service.
The SQL Server Agent job.
The database maintenance plan.

Explanation

Question 156 of 186

1

You administer two SQL Server 2008 instances named Instance1 and Instance2.
A database named Customers resides on Instance1.
You move the Customers database from Instance1 to Instance2.
A SQL Server login named User1 that has the password "111999" is used by a user to access the database on Instance1.
You create the same SQL Server login on Instance2.
The user attempts to access the Customers database on Instance2 by using the SQL Server login User1. However, the user receives an error message which indicates that the access to the Customers database is denied.
You need to ensure that User1 can access the Customers database.
Which Transact-SQL statements should you execute on Instance2?

Select one of the following:

USE Customers; ALTER USER User1 WITH LOGIN = User1;
USE Customers; ALTER USER User1 ENABLE;
USE Customers; ALTER LOGIN User1 WITH PASSWORD = '111999' UNLOCK;
USE Customers; ALTER LOGIN User1 WITH DEFAULT_DATABASE = Customers;

Explanation

Question 157 of 186

1

You administer a SQL Server 2008 instance.
You configure a single Database Mail profile that is used to send reports to all the employees of your organization.
The sp_send_dbmail stored procedure is used to send a large volume of reports during business hours.
The reports to the manager take a long time to be delivered.
You need to ensure that the reports to the manager are sent as quickly as possible.
What should you do?

Select one of the following:

Modify the SMTP relay settings.
Create a separate Database Mail profile for the reports.
Configure the security settings of the Database Mail system.
Modify the parameters of the email that contains the reports.

Explanation

Question 158 of 186

1

You maintain a default SQL Server 2008 instance.
You plan to configure FILESTREAM data to meet the following requirements:
- Enable FILESTREAM for file I/O streaming access.
- Allow remote client computers to have streaming access to
- FILESTREAM data.
You need to ensure that FILESTREAM data is enabled.
What should you do?

Select one of the following:

Configure the SQL Server service.
Configure the SQL Server Full Text service.
Modify the Volume Shadow Copy settings.
Modify the Background Intelligent Transfer Service settings.

Explanation

Question 159 of 186

1

You configure a SQL Server 2008 instance that contains a database named Customers.
The Customers database experiences deadlock problems.
You need to capture the deadlock information to the SQL Server error log.
What should you do?

Select one of the following:

Enable file system auditing.
Enable the Windows Event Collector service.
Configure trace flags and then restart the SQL Server instance.
Configure a Data Collector Set and then restart the SQL Server instance.

Explanation

Question 160 of 186

1

You plan to install a SQL Server 2008 instance for a new application on an existing server that contains a default SQL Server 2005 instance.
You need to ensure that both database instances are available for their applications.
Your solution must meet the following requirements:
- Minimize administrative effort.
- Prevent changes to the existing application environments.
What should you do?

Select one of the following:

Configure both instances to use SQL Server 2008.
Configure both applications to use SQL Server 2008.
Configure the new application to use a SQL Server 2008 named instance.
Configure the new application to use the SQL Server 2008 default instance.

Explanation

Question 161 of 186

1

You administer a Microsoft SQL Server 2008 R2 instance.
The instance has a database named CustomerOrders.
The database is backed up by using the weekly scheduled SQL Server Agent jobs.
The backup schedule is shown in the following table:

FULL backups Differential backups Transaction Log backups
Sundays - 18:00 Tuesdays - 18:00 Mondays - 18:00
Wednesdays - 18:00 Fridays - 18:00 Thursdays - 09:00
Thursdays - 18:00
Saturdays - 18:00

A drive fails and the CustomerOrders database goes into suspect mode on Friday at 09:00 hours.
You need to restore the backup.
What should you do?

Select one of the following:

Restore the backup taken on Sunday only.
Restore the backups taken on Sunday and Monday.
Restore the backup taken on Wednesday only.
Restore the backups taken on Wednesday, Thursday at 09:00 hours, and Thursday at 18:00 hours.
Restore the backups taken on Wednesday and Thursday at 09:00 hours.
Restore the backups taken on Wednesday, Thursday, and Friday.
Restore the backups taken on Wednesday and Friday.
Restore the backups taken Wednesday, Friday, and Saturday.

Explanation

Question 162 of 186

1

You administer a Microsoft SQL Server 2008 R2 instance.
The instance has a database named CustomerOrders.
The database is backed up by using the weekly scheduled SQL Server Agent jobs.
The backup schedule is shown in the following table:

FULL backups Differential backups Transaction Log backups
Sundays - 18:00 Tuesdays - 18:00 Mondays - 18:00
Wednesdays - 18:00 Fridays - 18:00 Thursdays - 09:00
Thursdays - 18:00
Saturdays - 18:00

A drive fails and the CustomerOrders database goes into suspect mode on Saturday at
10:00 hours.
You need to restore the backup.
What should you do?

Select one of the following:

Restore the backup taken on Sunday only.
Restore the backups taken on Sunday and Monday.
Restore the backup taken on Wednesday only.
Restore the backups taken on Wednesday, Thursday at 09:00 hours, and Thursday at 18:00 hours.
Restore the backups taken on Wednesday and Thursday at 09:00 hours.
Restore the backups taken on Wednesday, Thursday, and Friday.
Restore the backups taken on Wednesday and Friday.
Restore the backups taken Wednesday, Friday, and Saturday.

Explanation

Question 163 of 186

1

You administer a Microsoft SQL Server 2008 R2 instance.
The instance has a database named CustomerOrders.
The database is backed up by using the weekly scheduled SQL Server Agent jobs.
The backup schedule is shown in the following table:

FULL backups Differential backups Transaction Log backups
Sundays - 18:00 Tuesdays - 18:00 Mondays - 18:00
Wednesdays - 18:00 Fridays - 18:00 Thursdays - 09:00
Thursdays - 18:00
Saturdays - 18:00

A drive fails and the CustomerOrders database goes into suspect mode on Thursday at
17:00 hours.
You need to restore the backup.
What should you do?

Select one of the following:

Restore the backup taken on Sunday only.
Restore the backups taken on Sunday and Monday.
Restore the backup taken on Wednesday only.
Restore the backups taken on Wednesday, Thursday at 09:00 hours, and Thursday at 18:00 hours.
Restore the backups taken on Wednesday and Thursday at 09:00 hours.
Restore the backups taken on Wednesday, Thursday, and Friday.
Restore the backups taken on Wednesday and Friday.
Restore the backups taken Wednesday, Friday, and Saturday.

Explanation

Question 164 of 186

1

You administer a Microsoft SQL Server 2008 R2 database that contains a schema named Reporting.
The Reporting schema contains store procedures, tables, and views.
Stored procedures and views in the Reporting schema access objects that are not contained in the schema.
You need to ensure that the DOMAIN\BusinessReporting Active Directory group meets the following requirements:
- Users are able to access objects in the Reporting schema through existing stored procedures.
- Users are unable to modify tables and views in the database.
What should you grant the DOMAIN\BusinessReporting group?

Select one of the following:

Access to the db_datareader role in the database
The EXECUTE permissions on the Reporting schema
The EXECUTE permissions to each schema that is queried by the stored procedures and views in the Reporting schema
Access to the sysadmin server role

Explanation

Question 165 of 186

1

You are reviewing and configuring the security of a SQL Server 2008 instance that contains database DB1.
The security audit policy specifies the following requirements:
- Only successful and failed logon attempts are recorded in log files.
- The SQL Server instance is shut down if records cannot be written to the log files.
You need to configure the SQL Server instance to comply with the security audit policy.
What should you do?

Select one of the following:

Enable C2 auditing.
Enable common criteria compliance.
Create a server audit specification.
Configure Change Data Capture.

Explanation

Question 166 of 186

1

You administer a Microsoft SQL Server 2008 R2 database instance.
The instance has a Products table that is currently in the OevUser schema.
The Appusers database role is assigned Read and update permissions to the table.
The DevUser schema is owned by a database login that has been renamed.
You need to move the Products table from the DevUser schema to the dbo schema.
Which two actions should you perform in sequence?

Select one of the following:

Run the following Transact-SQL command:
REVOKE SELECT, UPDATE on DevUser.Products FROM Appusers;
Then run the following Transact-SQL command:
ALTER SCHEMA dbo TRANSFER DevUser.Products;
Run the following Transact-SQL command:
GRANT SELECT, UPDATE ON dbo.Products to Appusers;
Then run the following Transact-SQL command:
ALTER SCHEMA dbo TRANSFER DevUser.Products;
Run the following Transact-SQL command:
REVOKE SELECT, UPDATE on DevUser.Products FROM Appusers;
Then run the following Transact-SQL command:
GRANT SELECT, UPDATE ON dbo.Products to Appusers;
Run the following Transact-SQL command:
ALTER SCHEMA DevUser TRANSFER dbo.Products;
then run the following Transact-SQL command:
REVOKE SELECT, UPDATE on DevUser.Products FROM Appusers;
Run the following Transact-SQL command:
ALTER SCHEMA dbo TRANSFER DevUser.Products;
Then run the following Transact-SQL command:
GRANT SELECT, UPDATE ON dbo.Products to Appusers;

Explanation

Question 167 of 186

1

You administer a SQL Server 2008 instance.
A user named Julie reports repeated failed connections because of incorrect password usage for the login name srpc8521. Julie has been able to successfully login using this account in the past. Julie then recalls the correct password. However, her connection is still denied.
You need to enable the SQL Server login.
Which Transact-SQL statement should you use?

Select one of the following:

ALTER LOGIN srpc8521 ENABLE;
ALTER LOGIN srpc8521 UNLOCK;
ALTER LOGIN srpc8521 WITH CHECK_EXPIRATION = OFF;
ALTER LOGIN srpc8521 WITH NO CREDENTIAL;

Explanation

Question 168 of 186

1

You administer a Microsoft SQL Server 2008 R2 server that hosts two databases named NorthwindOLTP and NorthwindOLAP.
Cross database ownership chaining is currently disabled for all databases. A stored procedure in the NorthwindOLTP database writes data to a table in the NorthwindOLAP database.
The public role in both databases has only Read and Execute permissions assigned to all objects. All objects are located in the DBO schema.
Guest access has been disabled for all databases on the server.
You need to ensure that the stored procedure can execute successfully.
Which two actions should you perform in sequence?

Select one of the following:

Enable Cross Database ownership chaining for the NortwindOLAP database.
Then enable Cross Database ownership chaining for the Server instance.
Enable Guest access to the NorthwindOLTP database.
Then enable Guest access to the NorthwindOLAP database.
Disable Cross Database ownership chaining for the Server instance.
Then enable Cross Database ownership chaining for the NortwindOLAP database.
Enable Cross Database ownership chaining for the Server instance.
Then enable Cross Database ownership chaining for the NortwindOLAP database.
Disable Cross Database ownership chaining for the NortwindOLAP database.
Then enable Cross Database ownership chaining for the Server instance.

Explanation

Question 169 of 186

1

You maintain a SQL Server 2008 instance that contains a database named Finance.
You need to configure SQL Server to automatically send an e-mail message when any file in the Finance database increases.
Which technology should you include in your solution?

Select one of the following:

SQL Server Profiler
Trace flag
Query notifications
Event notifications

Explanation

Question 170 of 186

1

You administer a Microsoft SQL Server 2008 R2 database instance.
The service account used by SQL Server services must not have administrative permissions.
You configure a new SQL Server Agent job to run every night.
One of the steps in the job runs a PowerShell step.
The job continuously fails on this step and throws the following error message:
"The process could not be created for step 1 of job (reason: A required privilege is not held by the client). The step failed."
You need to ensure that the SQL Server Agent Job executes successfully.
Which four actions should you perform in sequence?

Select one of the following:

1. Create a Credential object on the SQL Server and assign the object to the Windows Account.
2. Create a proxy in the SQL Server Agent and assign the proxy to the Credential object.
3. Create a Windows domain account. Add the account to the Local Administrators group on the Production SQL Server.
4. Open the job in the SQL Server Agent, and open the Powershell step. Under 'Run As', select SQL Server Agent Account.
1. Create a Credential object on the SQL Server and assign the object to the Windows Account.
2. Create a proxy in the SQL Server Agent and assign the proxy to the Credential object.
3. Create a Windows domain account. Add the account to the Local Administrators group on the Production SQL Server.
4. Open the job in the SQL Server Agent, and open the Powershell step. Under 'Run As', select the proxy.
1. Create a Credential object on the SQL Server and assign the object to the Windows Account.
2. Create a SQL Server account and map the account to the credential. Assign the account to the SysAdmin server role..
3. Create a Windows domain account. Add the account to the Local Administrators group on the Production SQL Server.
4. Open the job in the SQL Server Agent, and open the Powershell step. Under 'Run As', select the proxy.
1. Create a Credential object on the SQL Server and assign the object to the Windows Account.
2. Create a proxy in the SQL Server Agent and assign the proxy to the Credential object.
3. Create a Windows domain account. Add the account to the Users group on the Production SQL Server.
4. Open the job in the SQL Server Agent, and open the Powershell step. Under 'Run As', select the proxy.
1. Create a Credential object on the SQL Server and assign the object to the Windows Account.
2. Create a Windows domain account. Add the account to the Users group on the Production SQL Server.
3. Create a Windows domain account. Add the account to the Local Administrators group on the Production SQL Server.
4. Open the job in the SQL Server Agent, and open the Powershell step. Under 'Run As', select the proxy.

Explanation

Question 171 of 186

1

You administer a Microsoft SQL Server 2008 R2 database instance that supports a sales management application.
Occasionally, users of the application report that they receive a deadlock error.
You plan to troubleshoot this error.
You need to ensure that the instance is configured to capture troubleshooting information that will allow you to isolate the cause of the error.
Which two actions should you perform in sequence?

Select one of the following:

1. Use the SQL Server Configuration Manager tool to enable Trace Flag 1204 when the server starts
2. Use Microsoft SQL Server Management Studio to enable the OleAutomationEnabled management facet.
1. Restart the SQL Server service.
2. Use the SQL Server Configuration Manager tool to enable Trace Flag 1204 when the server starts
1. Use the SQL Server Configuration Manager tool to enable Trace Flag 1204 when the server starts
2. Restart the SQL Server service.
1. Use the SQL Server Configuration Manager tool to enable the Shared Memory protocol.
2. Use Microsoft SQL Server Management Studio to enable the OleAutomationEnabled management facet.
1. Use Microsoft SQL Server Management Studio to enable the OleAutomationEnabled management facet.
2. Restart the SQL Server service.

Explanation

Question 172 of 186

1

You administer a Microsoft SQL Server 2008 R2 database that contains customer invoice accounts.
Backups are performed according to the following schedule:
- Full database backup at 00:00 hours daily.
- Transaction log backups at 06:00 hours, 14:00 hours, and 20:00 hours daily.
You discover that the database failed at 11:59 hours.
You also discover that the database is no longer accessible due to a failure of the hard disk drive that contains the database data file.
You need to restore the database.
You also need to ensure that data loss is minimal.
Which four actions should you perform in sequence?

Select one of the following:

1. Restore the most recent full database backup by using the WITH NORECOVERY option.
2. Back up the transaction log by using the WITH NO_TRUNCATE option on the BACKUP LOG statement.
3. Restore the transaction log backup from 06:00 hours by using the WITH NORECOVERY option.
4. Restore the last transaction log backup by using the WITH RECOVERY option.
1. Back up the transaction log by using the WITH NO_TRUNCATE option on the BACKUP LOG statement.
2. Restore the transaction log backup from 06:00 hours by using the WITH NORECOVERY option.
3. Restore the last transaction log backup by using the WITH RECOVERY option.
4. Restore the most recent full database backup by using the WITH NORECOVERY option.
1. Restore the most recent full database backup by using the WITH RECOVERY option.
2. Back up the transaction log by using the WITH NO_TRUNCATE option on the BACKUP LOG statement.
3. Restore the transaction log backup from 06:00 hours by using the WITH NORECOVERY option.
4. Restore the last transaction log backup by using the WITH RECOVERY option.
1. Back up the transaction log by using the WITH NO_TRUNCATE option on the BACKUP LOG statement.
2. Restore the most recent full database backup by using the WITH NORECOVERY option.
3. Restore the transaction log backup from 06:00 hours by using the WITH NORECOVERY option.
4. Restore the last transaction log backup by using the WITH RECOVERY option.
1. Back up the transaction log.
2. Restore the most recent full database backup by using the WITH NORECOVERY option.
3. Restore the transaction log backup from 06:00 hours by using the WITH NORECOVERY option.
4. Restore the last transaction log backup by using the WITH NORECOVERY option.

Explanation

Question 173 of 186

1

You maintain multiple SQL Server 2008 instances.
You develop a failure recovery strategy.
You need to find out the edition and the build of SQL Server that is deployed in your environment.
What should you use to find the required information?

Select one of the following:

SELECT @@VERSION
SELECT * FROM sys.dm_os_sys_info
DBCC HELP
EXEC xp_msver

Explanation

Question 174 of 186

1

You administer a Microsoft SQL Server 2008 instance that contains two databases named Work and WorkHistory.
Work uses the Latin1_General_CS_AS collation.
WorkHistory uses the Latin1_General_100_CI_AI collation.
Each database has an Orders table that has the following definition:

CREATE TABLE [dbo].[Orders](
[OrderID] [int[ IDENTITY(1,1) NOT NULL,
[OrderAmt] [money] NULL,
[OrderDate] [datetime] NULL,
[OrderTID] [char] (10) NULL,
[OrderUID] [uniqueidetifier] NULL
) ON [PRIMARY]
;

You need to return records from WorkHistory.dbo.Orders that exist in Work.dbo.Orders.
Which Transact-SQL query should you use? (see image)

Select one of the following:

A.
B.
C.
D.

Explanation

Question 175 of 186

1

You administer a Microsoft SQL Server 2008 R2 instance.
Mirroring has been configured between two servers.
You need to ensure that automatic failover will occur when the principal server fails
What should you do?

Select one of the following:

Create a witness server to monitor the mirroring session. On the principal server, use the ALTER DATABASE statement along with the SET WITNESS clause.
Create a witness server to monitor the mirroring session. On both partner servers, use the ALTER DATABASE statement along with the SET WITNESS clause.
Create a Microsoft SQL Server Integration Services package on the partner server to monitor the principal server.
Implement Policy Based Management. Enable the Server Performance management facet.

Explanation

Question 176 of 186

1

You administer a Microsoft SQL Server 2008 R2 database instance.
You plan to automate maintenance tasks.
What task types are included in a SQL Maintenance Plan?
(Pick all that apply.)

Select one or more of the following:

Copy data from another server
Update database statistics
Rebuild indexes
Examine hard disk integrity
Back up databases

Explanation

Question 177 of 186

1

You maintain a SQL Server 2008 instance.
You discover that the database named CityPowerLightDB presents data integrity problems.
You perform a full database backup at 22:00 hours.
The SQL Server instance experiences a power failure at 05:59 hours.
No data modifications have occurred after the backup.
When the database is online, you discover that some table data pages are corrupt.
You need to completely repair the database in the minimum amount of time.
What should you do?

Select one of the following:

Restore the corrupt pages from the full database backup.
Restore the database from the full database backup.
Use the DBCC CHECKTABLE command along with the PHYSICAL_ONLY option.
Use the DBCC CHECKDB command along with the REPAIR_ALLOW_DATA_LOSS option.

Explanation

Question 178 of 186

1

You administer a Microsoft SQL Server 2008 R2 database that contains an OrderItems table.
The table has the following definition:
CREATE TABLE [OrderItems]
(OrderID INT NOT NULL,
OrderDate DATETIME NOT NULL,
OrderLine INT NOT NULL,
ProductID INT NOT NULL,
Quantity INT NOT NULL,
PriceEach MONEY NOT NULL
);

Select one of the following:

A. Remove the clustered index from the table.
B. Use the ALTER PARTITION FUNCTION ... SPLIT RANGE statement.
C. Use the ALTER TABLEstatement to remove the COLLATEoption.
D. Execute the DBCC CLEANTABLEcommand on the OrderItems table.
E. Create a new filegroup. Create a new database file. Use the ALTER PARTITION SCHEME statement along with the NEXT USED clause. Use ALTER INDEX REORGANIZE statement.
F. Create a new Filegroup. Create a new database File. Use the ALTER PARTITION SCHEME statement along with the NEXT USED clause. Use the ALTER PARTITION FUNCTION statement along with the SPLIT RANGE clause.
G. Create a new table. Use the ALTER TABLE statement along with the SWITCH PARTITION clause. Use the ALTER PARTITION FUNCTION statement along with the MERGE RANGE clause.
H. Create a new partition function. Create a new partition scheme. Add a clustered index to place the data onto the partition scheme.
I. Run the following statement: CREATE PARTITION SCHEME SEC_FG AS PARTITION FUNC_FG ALL TO (SECONDARY);
J. Run the following statement: EXECUTE sp_tableoption @TableNamePattern ='OrderItem3', @OptionName= `PartltionByYear'; @OptionValue= 'true';

Explanation

Question 179 of 186

1

You administer a Microsoft SQL Server 2008 R2 database that contains an OrderItems table.
The table has the following definition:
CREATE TABLE [OrderItems]
(OrderID INT NOT NULL,
OrderDate DATETIME NOT NULL,
OrderLine INT NOT NULL,
ProductID INT NOT NULL,
Quantity INT NOT NULL,
PriceEach MONEY NOT NULL
);
The following DDL has been run on the database:
CREATE PARTITION FUNCTION FUNC_FG (INT)
AS RANGE LEFT FOR VALUES (1, 100, 1000);
You need to create a partition scheme that will place all data to the SECONDARY filegroup.
What should you do?

Select one of the following:

A. Remove the clustered index from the table.
B. Use the ALTER PARTITION FUNCTION ... SPLIT RANGE statement.
C. Use the ALTER TABLEstatement to remove the COLLATEoption.
D. Execute the DBCC CLEANTABLEcommand on the OrderItems table.
E. Create a new filegroup. Create a new database file. Use the ALTER PARTITION SCHEME statement along with the NEXT USED clause. Use ALTER INDEX REORGANIZE statement.
F. Create a new Filegroup. Create a new database File. Use the ALTER PARTITION SCHEME statement along with the NEXT USED clause. Use the ALTER PARTITION FUNCTION statement along with the SPLIT RANGE clause.
G. Create a new table. Use the ALTER TABLE statement along with the SWITCH PARTITION clause. Use the ALTER PARTITION FUNCTION statement along with the MERGE RANGE clause.
H. Create a new partition function. Create a new partition scheme. Add a clustered index to place the data onto the partition scheme.
I. Run the following statement: CREATE PARTITION SCHEME SEC_FG AS PARTITION FUNC_FG ALL TO (SECONDARY);
J. Run the following statement: EXECUTE sp_tableoption @TableNamePattern ='OrderItem3', @OptionName= `PartltionByYear'; @OptionValue= 'true';

Explanation

Question 180 of 186

1

You administer a Microsoft SQL Server 2008 R2 database that contains an OrderItems table.
The table has the following definition:
CREATE TABLE [OrderItems]
(OrderID INT NOT NULL,
OrderDate DATETIME NOT NULL,
OrderLine INT NOT NULL,
ProductID INT NOT NULL,
Quantity INT NOT NULL,
PriceEach MONEY NOT NULL
);
Currently, the table is partitioned by year with each year in its own filegroup.
You need to move the data from the oldest year into a new table in a different Schema to archive the data.
What should you do?

Select one of the following:

A. Remove the clustered index from the table.
B. Use the ALTER PARTITION FUNCTION ... SPLIT RANGE statement.
C. Use the ALTER TABLEstatement to remove the COLLATEoption.
D. Execute the DBCC CLEANTABLEcommand on the OrderItems table.
E. Create a new filegroup. Create a new database file. Use the ALTER PARTITION SCHEME statement along with the NEXT USED clause. Use ALTER INDEX REORGANIZE statement.
F. Create a new Filegroup. Create a new database File. Use the ALTER PARTITION SCHEME statement along with the NEXT USED clause. Use the ALTER PARTITION FUNCTION statement along with the SPLIT RANGE clause.
G. Create a new table. Use the ALTER TABLE statement along with the SWITCH PARTITION clause. Use the ALTER PARTITION FUNCTION statement along with the MERGE RANGE clause.
H. Create a new partition function. Create a new partition scheme. Add a clustered index to place the data onto the partition scheme.
I. Run the following statement: CREATE PARTITION SCHEME SEC_FG AS PARTITION FUNC_FG ALL TO (SECONDARY);
J. Run the following statement: EXECUTE sp_tableoption @TableNamePattern ='OrderItem3', @OptionName= `PartltionByYear'; @OptionValue= 'true';

Explanation

Question 181 of 186

1

You maintain a SQL Server 2008 instance.
You use SQL Server Database Mail to distribute multiple reports that are scheduled and generated every night.
You need to enforce a mail retention policy to delete copies of automatically generated mails
after 60 days.
Which process should you use?

Select one of the following:

A. Set the Subscription retention interval to delete mails after 60 days.
B. Set the MailProfile property of a Management Policy on the Server facet to delete mails after 60 days.
C. Create a SQL Agent job to use the sysmail_delete_log_sp stored procedure to delete mails older than 60 days.
D. Create a SQL Agent job to use the sysmail_delete_mailitems_sp stored procedure to delete mails older than 60 days.

Explanation

Question 182 of 186

1

You administer a Microsoft SQL Server 2008 database for an order-processing application.
The following Transact-SQL statements have been run against the database (see attached image)
---
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 a correct & in order?

Select one of the following:

1. CREATE RESOURCE POOL ReportingPool
WITH
(
MAX CPU_PERCENT = 35,
MAX_MEMORY_PERCENT = 45
);
2. CREATE WORKLOAD GROUP ProductionGroup
USING ProductionPool;
3. ALTER RESOURCE GOVERNOR RECONFIGURE;
4. ALTER RESOURCE GOVERNOR WITH
(CLASSIFIER_FUNCTION = dbo.UserClassifier);
1. CREATE WORKLOAD GROUP ProductionGroup
USING ProductionPool;
2. CREATE RESOURCE POOL ReportingPool
WITH
(
MAX CPU_PERCENT = 35,
MAX_MEMORY_PERCENT = 45
);
3. ALTER RESOURCE GOVERNOR RECONFIGURE;
4. ALTER RESOURCE GOVERNOR WITH
(CLASSIFIER_FUNCTION = dbo.UserClassifier);
1. CREATE RESOURCE POOL ReportingPool
WITH
(
MAX CPU_PERCENT = 35,
MAX_MEMORY_PERCENT = 45
);
2. CREATE WORKLOAD GROUP ReportingGroup;
3. ALTER RESOURCE GOVERNOR RECONFIGURE;
4. ALTER RESOURCE GOVERNOR WITH
(CLASSIFIER_FUNCTION = dbo.UserClassifier);
1. CREATE RESOURCE POOL ReportingPool
WITH
(
MAX CPU_PERCENT = 35,
MAX_MEMORY_PERCENT = 45
);
2. CREATE WORKLOAD GROUP ProductionGroup;
3. ALTER RESOURCE GOVERNOR RECONFIGURE;
4. ALTER RESOURCE GOVERNOR WITH
(CLASSIFIER_FUNCTION = dbo.UserClassifier);

Explanation

Question 183 of 186

1

You troubleshoot a SQL Server 2008 instance.
You suspect that an application uses an inefficient locking strategy and causes concurrency problems.
You need to identify the following:
- The sessions that wait for a resource
- The resource needed
- The sessions that block the resource
Which dynamic management view should you use?

Select one of the following:

A. sys.dm_os_waiting_tasks
B. sys.dm_os_wait_stats
C. sys.dm_tran_active_transactions
D. sys.dm_exec_requests

Explanation

Question 184 of 186

1

You administer a SQL Server 2008 instance that contains a very large database named FinanceDB.
You plan to create a maintenance plan that meets the following objectives for the FinanceDB database:
- It executes the DBCC CHECKDB statement.
- It rebuilds all the indexes.
- It updates all index statistics.
You need to ensure that the maintenance plan is executed in the minimum amount of time.
Which two actions should you perform? (Each correct answer presents part of the solution.
Choose two.)

Select one or more of the following:

A. Use the Reorganize Index task.
B. Use the Update Statistics task.
C. Use the Shrink Database task.
D. Use the Check Database Integrity task.
E. Use the Rebuild index task.

Explanation

Question 185 of 186

1

You design a maintenance plan for a SQL Server 2008 instance that contains a database named SalesDB.
The SalesDB database includes spatial indexes to support queries on spatial data.
You need to perform physical consistency checks on SalesDB.
You also need to ensure that the performance effect on the SalesDB database is minimized.
Which Transact-SQL statement should you execute?

Select one of the following:

A. DBCC SYS_CHECK (SalesDB);
B. DBCC SQLPERF (SalesDB);
C. DBCC RSPAIRDB (SalesDB);
D. DBCC CHECKDB (SalesDB);

Explanation

Question 186 of 186

1

You administer a SQL Server 2008 instance.
You need to identify the network protocol used by the current connection.
What should you do?

Select one of the following:

A. View the SQL error log.
B. Use a Dynamic Management View.
C. Set a trace flag.
D. Use the T-SQL template in Profiler.

	Created by Bob Balsman about 8 years ago

Content available in printed form from PassLeader: http://www.passleader.com/70-432.html

SQL Server 2008 MCITP 70-432 Exam Review

Question 1 of 186

Select one of the following:

Explanation

Question 2 of 186

You are the administrator of a SQL Server 2008 instance with a database named DB1. In the Services schema there is a table named Service.Table. The Services.Table has to be moved from the Sales schema to a new schema named Costs. Which Transact-SQL statement should you execute?

Select one of the following:

Explanation

Question 3 of 186

Select one of the following:

Explanation

Question 4 of 186

Select one of the following:

Explanation

Question 5 of 186

Select one of the following:

Explanation

Question 6 of 186

Select one of the following:

Explanation

Question 7 of 186

Select one of the following:

Explanation

Question 8 of 186

Select one of the following:

Explanation

Question 9 of 186

Select one of the following:

Explanation

Question 10 of 186

Select one of the following:

Explanation

Question 11 of 186

Select one of the following:

Explanation

Question 12 of 186

Select one of the following:

Explanation

Question 13 of 186

You are the administrator of a SQL 2008 instance. There is an Internet Information Services application. This application will use anonymous access to access the SSAS instance. You must make sure that the application can access the SSAS instance. What should you do?

Select one of the following:

Explanation

Question 14 of 186

Select one of the following:

Explanation

Question 15 of 186

You are the administrator of a SQL Server 2008 cluster. According to the company requirement, the failover response of the cluster has to be tested. The company assigns this task to you. On the cluster, you have to implement a manual failover. Which actions should you take? (choose more than one)

Select one or more of the following:

Explanation

Question 16 of 186

Select one or more of the following:

Explanation

Question 17 of 186

Select one of the following:

Explanation

Question 18 of 186

Select one of the following:

Explanation

Question 19 of 186

Select one of the following:

Explanation

Question 20 of 186

Select one of the following:

Explanation

Question 21 of 186

Select one of the following:

Explanation

Question 22 of 186

You are the administrator of a SQL Server 2008 instance. There is a database named DB1 in the instance. There are data file and the transaction log file on the E: drive. You find that there is only 6% spare space. Both files have to be moved to the V: drive. Which procedure should you use?

Select one of the following:

Explanation

Question 23 of 186

You are the administrator of a SQL Server 2008 instance with a database named DB1. A backup of DB1 is performed every day. You have to minimize the size of the full database backup files of DB1. Which Transact-SQL statement should you use?

Select one of the following:

Explanation

Question 24 of 186

Select one of the following:

Explanation

Question 25 of 186

You are the administrator of a SQL Server 2008 instance with a database named DB1.
In the Services schema there is a table named Service.Table.
The Services.Table has to be moved from the Sales schema to a new schema named Costs.
Which Transact-SQL statement should you execute?

You are the administrator of a SQL 2008 instance.
There is an Internet Information Services application.
This application will use anonymous access to access the SSAS instance.
You must make sure that the application can access the SSAS instance.
What should you do?

You are the administrator of a SQL Server 2008 cluster.
According to the company requirement, the failover response of the cluster has to be tested.
The company assigns this task to you.
On the cluster, you have to implement a manual failover.
Which actions should you take? (choose more than one)

You are the administrator of a SQL Server 2008 instance.
There is a database named DB1 in the instance.
There are data file and the transaction log file on the E: drive.
You find that there is only 6% spare space.
Both files have to be moved to the V: drive.
Which procedure should you use?

You are the administrator of a SQL Server 2008 instance with a database named DB1.
A backup of DB1 is performed every day.
You have to minimize the size of the full database backup files of DB1.
Which Transact-SQL statement should you use?

You are the administrator of a SQL Server 2008 instance.
A user reports that when he runs a query, it takes a quite long time and it is still unfinished.
As the IT support, you have to verify whether the query is blocked.
Which tool should you use?

You are the administrator of a SQL Server 2008 instance.
You get an order from your company CIO to identify whether a database integrity check (DBCC CHECKDB) was run for a particular database.
Which log file should you examine?

You are the administrator of a SQL Server 2008 instance which contains multiple Agent jobs.
The jobs run each job on each day of the week by using seven shared schedules.
You have to make sure that a job named Job4 doesn't run Wednesday.
Which action should you perform to achieve this goal?

You are a database developer for your company.
You use the following Transact-SQL code.
SELECT e.Resume,j.EmployeeID FROM
JobCandidate j, Employee e
WHERE j.EmployeeID =* e.EmployeeID
You need to rewrite this code to be ANSI compliant.
How should you rewrite the code?