Microsoft Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 2 (70-458) Free Practice Test
Question 1
You are migrating a database named Orders to a new server that runs Microsoft SQL Server 2012.
You attempt to add a SQL login, [User1], to the database. However, you receive the following error message:
"User already exists in current database."
You need to configure the [User1] login to be able to access the Orders database and retain the original permissions. You need to achieve this goal by using the minimum required permissions.
Which Transact-SQL statement should you use?
You attempt to add a SQL login, [User1], to the database. However, you receive the following error message:
"User already exists in current database."
You need to configure the [User1] login to be able to access the Orders database and retain the original permissions. You need to achieve this goal by using the minimum required permissions.
Which Transact-SQL statement should you use?
Correct Answer: D
Question 2
You are completing the installation of the Data Quality Server component of SQL Server Data Quality Services (DQS).
You need to complete the post-installation configuration.
What should you do?
You need to complete the post-installation configuration.
What should you do?
Correct Answer: C
Question 3
HOTSPOT
You administer a Microsoft SQL Server 2012 database.
The database contains a table that has the following definition:
You want to export data from the table to a flat file by using the SQL Server Import and Export Wizard.
You need to ensure that the following requirements are met:
The first row of the file contains the first row of data.
Each record is of the same length.
The date follows the U.S. date format.
The file supports international characters.
What should you do? (To answer, configure the appropriate option or options in the dialog box in the answer area.)
----
You administer a Microsoft SQL Server 2012 database.
The database contains a table that has the following definition:
You want to export data from the table to a flat file by using the SQL Server Import and Export Wizard.
You need to ensure that the following requirements are met:
The first row of the file contains the first row of data.
Each record is of the same length.
The date follows the U.S. date format.
The file supports international characters.
What should you do? (To answer, configure the appropriate option or options in the dialog box in the answer area.)
----
Correct Answer:
Question 4
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You are writing a SQL Server Integration Services (SSIS) package that transfers data from a legacy system.
Data integrity in the legacy system is very poor. Invalid rows are discarded by the package but must be logged to a CSV file for auditing purposes.
You need to establish the best technique to log these invalid rows while minimizing the amount of development effort.
What should you do?
You are writing a SQL Server Integration Services (SSIS) package that transfers data from a legacy system.
Data integrity in the legacy system is very poor. Invalid rows are discarded by the package but must be logged to a CSV file for auditing purposes.
You need to establish the best technique to log these invalid rows while minimizing the amount of development effort.
What should you do?
Correct Answer: F
Question 5
A SQL Server Integration Services (SSIS) package imports daily transactions from several files into a SQL Server table named Transaction. Each file corresponds to a different store and is imported in parallel with the other files. The data flow tasks use OLE DB destinations in fast load data access mode.
The number of daily transactions per store can be very large and is growing. The Transaction table does not have any indexes.
You need to minimize the package execution time.
What should you do?
The number of daily transactions per store can be very large and is growing. The Transaction table does not have any indexes.
You need to minimize the package execution time.
What should you do?
Correct Answer: B
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 6
You are developing a SQL Server Integration Services (SSIS) project by using the Project Deployment Mode.
The project will be deployed to an SSIS catalog folder where Environments have already been created.
You need to deploy the project.
What should you do?
The project will be deployed to an SSIS catalog folder where Environments have already been created.
You need to deploy the project.
What should you do?
Correct Answer: G
Question 7
Note: This question is part of a series of questions that use the same set of answer choices. An answer choice may be correct for more than one question in the series.
You administer a SQL 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 remove the Select permission for userA on the Regions table. You also need to ensure that UserA can still access all the tables in the Customers schema, including the Regions table, through the Sales role permissions.
Which Transact-SQL statement should you use?
You administer a SQL 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 remove the Select permission for userA on the Regions table. You also need to ensure that UserA can still access all the tables in the Customers schema, including the Regions table, through the Sales role permissions.
Which Transact-SQL statement should you use?
Correct Answer: F
Question 8
You are developing a SQL Server Integration Services (SSIS) package to load data into a data warehouse. The package consists of several data flow tasks.
The package experiences intermittent errors in the data flow tasks.
If any data flow task fails, all package error information must be captured and written to a SQL Server table by using an OLE DB connection manager.
You need to ensure that the package error information is captured and written to the table.
What should you do?
The package experiences intermittent errors in the data flow tasks.
If any data flow task fails, all package error information must be captured and written to a SQL Server table by using an OLE DB connection manager.
You need to ensure that the package error information is captured and written to the table.
What should you do?
Correct Answer: I
Question 9
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 S 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 implement log shipping of the financial database to another SQL Server 2012 instance. You decide to failoverto this secondary database.
You need to ensure that all transactions will be replicated to the secondary database.
Which backup option should you use?
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 S 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 implement log shipping of the financial database to another SQL Server 2012 instance. You decide to failoverto this secondary database.
You need to ensure that all transactions will be replicated to the secondary database.
Which backup option should you use?
Correct Answer: D