Dumps4cert.com : Latest Dumps with PDF and VCE Files
2018 Aug Microsoft Official New Released 70-462
100% Free Download! 100% Pass Guaranteed!

Administering Microsoft SQL Server 2012 Databases

Question No: 21 – (Topic 1)

You administer a Microsoft SQL Server 2012 database.

You provide temporary securityadmin access to User1 to the database server. You need to know if User1 adds logins to securityadmin.

Which server-level audit action group should you use?





Answer: D




This event is raised when the SQL Server service state is modified. Equivalent to the Audit Server Starts and Stops Event Class.


This event is raised when there is an impersonation within server scope, such as EXECUTE AS lt;logingt;.

Equivalent to the Audit Server Principal Impersonation Event Class.

SUCCESSFUL_LOGIN_GROUP Indicates that a principal has successfully logged in to SQL Server. Events in this class are raised by new connections or by connections that are reused from a connection pool. Equivalent to the Audit Login Event Class.


This event is raised whenever a login is added or removed from a fixed server role. This event is raised for the sp_addsrvrolemember and sp_dropsrvrolemember stored procedures. Equivalent to the Audit Add Login to Server Role Event Class.

Question No: 22 – (Topic 1)

You use a contained database named ContosoDb within a domain.

You need to create a user who can log on to the ContosoDb database. You also need to ensure that you can port the database to different database servers within the domain without additional user account configurations.

Which type of user should you create?

  1. User mapped to a certificate

  2. SQL user without login

  3. Domain user

  4. SQL user with login

Answer: C

Question No: 23 – (Topic 1)

You administer a Microsoft SQL Server 2012 database.

You configure Transparent Data Encryption (TDE) on the Orders database by using the following statements:

Dumps4Cert 2018 PDF and VCE

You attempt to restore the Orders database and the restore fails. You copy the encryption file to the original location.

A hardware failure occurs and so a new server must be installed and configured.

After installing SQL Server to the new server, you restore the Orders database and copy the encryption files to their original location. However, you are unable to access the


You need to be able to restore the database.

Which Transact-SQL statement should you use before attempting the restore?

Dumps4Cert 2018 PDF and VCE

  1. Option A

  2. Option B

  3. Option C

  4. Option D

Answer: C

Question No: 24 – (Topic 1)

You administer a Microsoft SQL Server 2012 database.

The database contains a Product table created by using the following definition:

Dumps4Cert 2018 PDF and VCE

You need to ensure that the minimum amount of disk space is used to store the data in the Product table.

What should you do?

  1. Convert all indexes to Column Store indexes.

  2. Implement Unicode Compression.

  3. Implement row-level compression.

  4. Implement page-level compression.

Answer: D Explanation: References:

http://msdn.microsoft.com/en-us/library/cc280449.aspx http://msdn.microsoft.com/en-us/library/cc280464.aspx http://msdn.microsoft.com/en-us/library/cc280576.aspx http://msdn.microsoft.com/en-us/library/ee240835.aspx

Question No: 25 – (Topic 1)

You administer a Microsoft SQL Server 2012 server that hosts a transactional database and a reporting database. The transactional database is updated through a web application and is operational throughout the day. The reporting database is only updated from the transactional database.

The recovery model and backup schedule are configured as shown in the following table:

Dumps4Cert 2018 PDF and VCE

At 16:20 hours, you discover that pages 17, 137, and 205 on one of the database files are corrupted on the transactional database.

You need to ensure that the transactional database is restored. You also need to ensure that data loss is minimal.

What should you do?

  1. Perform a partial restore.

  2. Restore the latest full backup, and restore the latest differential backup. Then, restore each log backup taken before the time of failure from the most recent differential backup.

  3. Perform a point-in-time restore.

  4. Restore the latest full backup.

  5. Restore the latest full backup, and restore the latest differential backup. Then, restore the latest log backup.

  6. Perform a page restore.

  7. Restore the latest full backup. Then, restore each differential backup taken before the time of failure from the most recent full backup.

  8. Restore the latest full backup. Then, restore the latest differential backup.

Answer: F Explanation:

Requirements for Restoring Pages

A page restore is subject to the following requirements:

->The databases must be using the full or bulk-logged recovery model. Some issues exist if you are using the bulk-logged model. For more information, see the following section.

->Pages in read-only filegroups cannot be restored. Trying to make a filegroup read- only will fail if there is a page restore going on at the same time in the filegroup.

->The restore sequence must start with a full, file, or filegroup backup.

->A page restore requires an unbroken chain of log backups up to the current log file, and they must all be applied so that the page is brought up to date with the current log file.

->As in a file-restore sequence, in each restore step, you can add more pages to the roll forward set.

->A database backup and page restore cannot be run at the same time.

Bulk-logged Recovery Model and Page Restore

For a database that uses the bulk-logged recovery model, page restore has the following additional conditions:

->Backing up while filegroup or page data is offline is problematic for bulk-logged

data, because the offline data is not recorded in the log. Any offline page can prevent backing up the log. In this cases, consider using

->DBCC REPAIR, because this might cause less data loss than restoring to the most

recent backup.

->If a log backup of a bulk-logged database encounters a bad page, it fails unless WITH

->CONTINUE_AFTER_ERROR is specified.

->Page restore generally does not work with bulk-logged recovery.

->A best practice for performing page restore is to set the database to the full recovery model, and try a log backup. If the log backup works, you can continue with the page restore. If the log backup fails, you either have to lose work since the previous log backup or you have to try running DBCC must be run with the REPAIR_ALLOW_DATA_LOSS option.

Question No: 26 DRAG DROP – (Topic 1)

You administer a Microsoft SQL Server 2012 database.

All database traffic to the SQL Server must be encrypted by using secure socket layer (SSL) certificates or the connection must be refused.

Network administrators have deployed server certificates to the Windows store of all Windows servers on the network from a trusted Certificate Authority. This is the only Certificate Authority allowed to distribute certificates on the network.

You enable the Force Encryption flag for the MSSQLServer protocols, but client computers are unable to connect. They receive the following error message:

quot;A connection was successfully established with the server, but then an error occurred during the pre-login handshake, (provider: SSL Provider, error: 0 – The certificate chain was issued by an authority that is not trusted.) (Microsoft SQL Server)quot;

You notice the following entry in the SQL Server log:

quot;A self-generated certificate was successfully loaded for encryption.quot;

You need to configure SQL Server to encrypt all client traffic across the network.

You also need to ensure that client computers are able to connect to the server by using a trusted certificate.

Which three actions should you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)

Dumps4Cert 2018 PDF and VCE


Dumps4Cert 2018 PDF and VCE

Question No: 27 – (Topic 1)

You administer a Microsoft SQL Server 2012 instance that contains a financial database hosted on a storage area network (SAN).

The financial database has the following characteristics:

->A data file of 2 terabytes is located on a dedicated LUN (drive D).

->A transaction log of 10 GB is located on a dedicated LUN (drive E).

->Drive D has 1 terabyte of free disk space.

->Drive E has 5 GB of free disk space.

The database is continually modified by users during business hours from Monday through Friday between 09:00 hours and 17:00 hours. Five percent of the existing data is modified each day.

The Finance department loads large CSV files into a number of tables each business day at 11:15 hours and 15:15 hours by using the BCP or BULK INSERT commands. Each data load adds 3 GB of data to the database.

These data load operations must occur in the minimum amount of time.

A full database backup is performed every Sunday at 10:00 hours. Backup operations will be performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours.

You need to ensure that the minimum amount of data is lost. Which recovery model should the database use?

  1. FULL






  7. Transaction log

  8. SKIP





  13. Differential


Answer: A

Question No: 28 – (Topic 1)

You administer a Microsoft SQL Server 2012 database. The database has a table named Customers owned by UserA and another table named Orders owned by UserB. You also have a stored procedure named GetCustomerOrderInfo owned by UserB. GetCustomerOrderInfo selects data from both tables.

You create a new user named UserC.

You need to ensure that UserC can call the GetCustomerOrderInfo stored procedure. You also need to assign only the minimum required permissions to UserC.

Which permission or permissions should you assign to UserC? Choose all that apply.

  1. The Select permission on Customers

  2. The Execute permission on GetCustomerOrderInfo

  3. The Take Ownership permission on Customers

  4. The Control permission on GetCustomerOrderInfo

  5. The Take Ownership permission on Orders

  6. The Select permission on Orders

Answer: A,B Explanation: References:

http://msdn.microsoft.com/en-us/library/ms188676.aspx http://stackoverflow.com/questions/2212044/sql-server-how-to-permission-schemas http://sqlservercentral.com/blogs/steve_jones/2012/03/14/ownership-chains-in-sql-server

Question No: 29 – (Topic 1)

You administer a Microsoft SQL Server 2012 server that has SQL Server Integration Services (SSIS) installed.

You plan to deploy new SSIS packages to the server. The SSIS packages use the Project Deployment Model together with parameters and Integration Services environment variables.

You need to configure the SQL Server environment to support these packages. What should you do?

  1. Create SSIS configuration files for the packages.

  2. Create an Integration Services catalog.

  3. Install Data Quality Services.

  4. Install Master Data services.

Answer: B

Question No: 30 – (Topic 1)

You administer a Microsoft SQL Server 2012 Enterprise Edition server that uses 64 cores.

You discover performance issues when large amounts of data are written to tables under heavy system load.

You need to limit the number of cores that handle I/O. What should you configure?

  1. Processor affinity

  2. Lightweight pooling

  3. Max worker threads

  4. I/O affinity

Answer: D



100% Dumps4cert Free Download!
70-462 PDF
100% Dumps4cert Pass Guaranteed!
70-462 Dumps

Dumps4cert ExamCollection Testking
Lowest Price Guarantee Yes No No
Up-to-Dated Yes No No
Real Questions Yes No No
Explanation Yes No No
Free VCE Simulator Yes No No
Instant Download Yes No No

Comments are closed.