Microsoft 070-457 Exam Syllabus Topics:

Microsoft Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 1 Sample Questions:

1. You administer a Microsoft SQL Server 2012 database. You need to ensure that the size of the transaction log file does not exceed 2 GB. What should you do?

A) In SQL Server Management Studio, expand the Storage leaf under the database. Select the transaction log file and set the maximum size of the file.
B) In SQL Server Management Studio, right-click the database, select Properties, and then click Files. Open the Transaction log Autogrowth window and set the maximum size of the file.
C) In SQL Server Management Studio, right-click the instance and select Database Settings. Set the maximum size of the file for the transaction log.
D) Use the ALTER DATABASE...SET LOGFILE command along with the midsize parameter.

2. You administer a Microsoft SQL Server 2012 server. The MSSQLSERVER service uses a domain account named CONTOSO\SQLService. You plan to configure Instant File Initialization. You need to ensure that Data File Autogrow operations use Instant File Initialization. What should you do? Choose all that apply.

A) Disable snapshot isolation.
B) Add the CONTOSO\SQLService account to the Server Operators fixed server role.
C) Restart the SQL Server Agent Service.
D) Enable snapshot isolation.
E) Add the CONTOSO\SQLService account to the Perform Volume Maintenance Tasks local security policy.
F) Restart the SQL Server Service.

3. You are creating an application that will connect to the AgentPortal database by using a SQL login named AgentPortalUser. Stored procedures in the database will use sp_send_dbmail to send email messages. You create a user account in the msdb database for the AgentPortalUser login. You use the Database Mail Configuration Wizard to create a Database Mail profile. Security has not been configured for the Database Mail profile. You need to ensure that AgentPortalUser can send email messages. What should you do?

A) In the Database Mail Configuration Wizard, create an email account for each recipient's email address in the Database Mail profile.
B) In the Database Mail Configuration Wizard, configure the Database Mail profile as a private profile for the AgentPortalUser account.
C) Disable the guest user in the msdb database.
D) Use the sysmail_help_profileaccount_sp stored procedure to add accounts to the Database Mail profile.

4. You administer a Microsoft SQL Server 2012 database that contains a table named OrderDetail. You discover that the NCI_OrderDetail_CustomerID non-clustered index is fragmented. You need to reduce fragmentation. You need to achieve this goal without taking the index offline. Which Transact-SQL batch should you use?

A) ALTER INDEX ALL ON OrderDetail REBUILD
B) ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REBUILD
C) ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE
D) CREATE INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID WITH DROP EXISTING

5. You administer a Microsoft SQL Server 2012 instance named SQL2012 that hosts an OLTP database of 1 terabyte in size. The database is modified by users only from Monday through Friday from 09:00 hours to
17:00 hours. Users modify more than 30 percent of the data in the database during the week. Backups are performed as shown in the following schedule:

The Finance department plans to execute a batch process every Saturday at 09:00 hours. This batch process will take a maximum of 8 hours to complete. The batch process will update three tables that are 10 GB in size. The batch process will update these tables multiple times. When the batch process completes, the Finance department runs a report to find out whether the batch process has completed correctly. You need to ensure that if the Finance department disapproves the batch process, the batch operation can be rolled back in the minimum amount of time. What should you do on Saturday?

A) Create a marked transaction in the transaction log at 08:59 hours. Perform a transaction log backup at
08:59 hours.
B) Record the LSN of the transaction log at 08:59 hours. Perform a transaction log backup at 17:01 hours.
C) Record the LSN of the transaction log at 08:59 hours. Perform a transaction log backup at 08:59 hours.
D) Perform a differential backup at 08:59 hours.
E) Create a marked transaction in the transaction log at 08:59 hours. Perform a transaction log backup at
17:01 hours.
F) Create a database snapshot at 08:59 hours.

Solutions: