10 tips on 70-457 practice test

Want to know Ucertify 70-457 Exam practice test features? Want to lear more about Microsoft Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 1 certification experience? Study Vivid Microsoft 70-457 answers to Regenerate 70-457 questions at Ucertify. Gat a success with an absolute guarantee to pass Microsoft 70-457 (Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 1) test on your first attempt.

2016 Sep 70-457 study material:

Q1. You administer a single server that contains a Microsoft SQL Server 2012 default instance. 

You plan to install a new application that requires the deployment of a database on the server. The application login requires sysadmin permissions. 

You need to ensure that the application login is unable to access other production databases. 

What should you do? 

A. Use the SQL Server default instance and configure an affinity mask. 

B. Install a new named SQL Server instance on the server. 

C. Use the SQL Server default instance and enable Contained Databases. 

D. Install a new default SQL Server instance on the server. 

Answer: B 

Reference: http://msdn.microsoft.com/en-us/library/ms187104.aspx 


Q2. 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, right-click the instance and select Database Settings. Set the maximum size of the file for the transaction log. 

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. Use the ALTER DATABASE...SET LOGFILE command along with the midsize parameter. 

D. 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. 

Answer: B 


Q3. You administer two instances of Microsoft SQL Server 2012. 

You deploy an application that uses a database on the named instance. 

The application is unable to connect to the database on the named instance. 

You need to ensure that the application can connect to the named instance. 

What should you do? 

A. Use the Data Quality Client to configure the application. 

B. Start the SQL Server Browser Service. 

C Use the Master Data Services Configuration Manager to configure the application. 

C. Start the SQL Server Integration Services Service. 

Answer: B 


Q4. You create a table that has the StudentCode, SubjectCode, and Marks columns to record mid-year marks for students. The table has marks obtained by 50 students for various subjects. 

You need to ensure that the following requirements are met: 

. Students must be ranked based on their average marks. . If one or more students have the same average, the same rank must be given to these students. . Consecutive ranks must be skipped when the same rank is assigned. 

Which Transact-SQL query should you use? 

A. SELECT StudentCode as Code, RANK() OVER(ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode 

B. SELECT Id, Name, Marks, DENSE_RANK() OVER(ORDER BY Marks DESC) AS Rank FROM StudentMarks 

C. SELECT StudentCode as Code, DENSE_RANK() OVER(ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode 

D. SELECT StudentCode as Code, NTILE(2) OVER(ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode 

E. SELECT StudentCode AS Code,Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks ASC) AS Rank FROM StudentMarks) tmp WHERE Rank = 1 

F. SELECT StudentCode AS Code,Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks DESC) AS Rank FROM StudentMarks) tmp WHERE Rank = 1 

G. SELECT StudentCode AS Code,Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANK() OVER(PARTITION BY StudentCode ORDER BY Marks ASC) AS Rank FROM StudentMarks) tmp WHERE Rank = 1 

H. SELECT StudentCode AS Code,Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANXO OVER(PARTITION BY StudentCode ORDER BY Marks DESC) AS Rank FROM StudentMarks) tmp WHERE Rank = 1 

Answer: A 

Reference: 

http://msdn.microsoft.com/en-us/library/ms189798.aspx 


Q5. You administer a SQL Server 2012 server that contains a database named SalesDb. SalesDb contains a schema named Customers that has a table named Regions. A user named UserA is a member of a role named Sales. 

UserA is granted the Select permission on the Regions table. The Sales role is granted the Select permission on the Customers schema. 

You need to ensure that UserA is disallowed to select from any of the tables in the Customers schema. 

Which Transact-SQL statement should you use? 

A. DENY SELECT ON Object::Regions FROM UserA 

B. DENY SELECT ON Object::Regions FROM Sales 

C. REVOKE SELECT ON Schema::Customers FROM Sales 

D. REVOKE SELECT ON Schema::Customers FROM UserA 

E. REVOKE SELECT ON Object::Regions FROM Sales 

F. REVOKE SELECT ON Object::Regions FROM UserA 

G. DENY SELECT ON Schema::Customers FROM Sales 

H. DENY SELECT ON Schema::Customers FROM UserA 

I. EXEC sp_addrolemember 'Sales', 'UserA' 

J. EXEC sp droprolemember 'Sales', 'UserA' 

Answer: H 


70-457 question

Renew books for exam 70-457:

Q6. DRAG DROP 

You administer a Microsoft SQL Server 2012 server that has multiple databases. 

You need to ensure that users are unable to create stored procedures that begin with sp_. 

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.) 

Answer: 


Q7. You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.) 

You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 in the following XML format. 

Which Transact-SQL query should you use? 

A. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers-CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW 

B. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers=CustomerId = 1 FOR XML RAW, ELEMENTS 

C. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId 

WHERE Customers.CustomerId = 1 FOR XML AUTO 

D. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId - Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO, ELEMENTS 

E. SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId- 1 FOR XML AUTO 

F. SELECT Name, Country, Crderld, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO, ELEMENTS 

G. SELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML PATH ('Customers') 

H. SELECT Name AS 'Customers/Name', Country AS 'Customers/Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML PATH ('Customers') 

Answer: F 


Q8. 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 backup size is as small as possible. 

Which backup should you perform every two hours? 

A. BULK_LOGGED 

B. NO_CHECKSUM 

C. FULL 

D. RESTART 

E. CHECKSUM 

F. STANDBY 

G. DBO.ONLY 

H. NORECOVERY 

I. SIMPLE 

J. Transaction log 

K. Differential 

L. CONTINUE_AFTER_ERROR 

M. COPY_ONLY 

N. SKIP 

Answer: J 

Reference: http://msdn.microsoft.com/en-us/library/ms186865.aspx http://msdn.microsoft.com/en-us/library/ms191429.aspx http://msdn.microsoft.com/en-us/library/ms179478.aspx 


Q9. You administer a Microsoft SQL Server 2012 database. 

The database contains a table named Employee. Part of the Employee table is shown in the exhibit. (Click the Exhibit button.) 

Unless stated above, no columns in the Employee table reference other tables. 

Confidential information about the employees is stored in a separate table named EmployeeData. 

One record exists within EmployeeData for each record in the Employee table. 

A. DateHired 

B. DepartmentID 

C. EmployeeID 

D. EmployeeNum 

E. FirstName 

F. JobTitle 

G. LastName 

H. MiddleName 

I. ReportsToID 

Answer: I 


Q10. 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: At 14:00 hours, you discover that pages 71, 520, and 713 on one of the database files are corrupted on the reporting database. 

You need to ensure that the databases are restored. You also need to ensure that data loss is minimal. 

What should you do? 

A. Perform a partial restore. 

B. 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. 

C. Restore the latest full backup. 

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

E. Perform a page restore. 

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

G. Perform a point-in-time restore. 

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

Answer: H 



see more 70-457 dumps