Microsoft TS:MS SQL Server 2008,Implementation and Maintenance - 070-432 Exam Practice Test
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?
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?
Correct Answer: C
DRAG DROP
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? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)
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? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)
Correct Answer:
You administer a Microsoft SQL Server database.
You plan to deploy a new database application. You want to use SQL Server common language runtime (CLR) integration.
You need to ensure that CLR assemblies can be used.
What should you do?
You plan to deploy a new database application. You want to use SQL Server common language runtime (CLR) integration.
You need to ensure that CLR assemblies can be used.
What should you do?
Correct Answer: C
You administer a Microsoft SQL Server 2008 R2 instance. You need to rectify a damaged index without losing any data. What should you do?
Correct Answer: C
You administer a Microsoft SQL Server 2008 R2 database that contains an OrderItems table. The table has the following definition:
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?
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?
Correct Answer: J
You administer a Microsoft SQL Server 2008 R2 database that contains an OrderItems table.
The table has the following definition: Currently, the table is partitioned by year with each year in its own filegroup.
You need to add a new partition for the upcoming year.
What should you do?
The table has the following definition: Currently, the table is partitioned by year with each year in its own filegroup.
You need to add a new partition for the upcoming year.
What should you do?
Correct Answer: D
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:
A drive fails and the CustomerOrders database goes into suspect mode on Sunday at
09:00 hours.
You need to restore the backup.
What should you do?
The database is backed up by using the weekly scheduled SQL Server Agent jobs. The backup schedule is shown in the following table:
A drive fails and the CustomerOrders database goes into suspect mode on Sunday at
09:00 hours.
You need to restore the backup.
What should you do?
Correct Answer: E
You administer a SQL Server 2008 instance.
A SQL Server Agent job executes every 2 minutes. The job logs information to a text file named SQL1.log located on the E: drive.
Users report that the server is unresponsive. You discover the following problems:
- The SQL Server Agent job no longer functions.
- The SQL Server Agent service does not run.
You need to find out the cause of the SQL Server Agent service failure.
Which log should you examine?
A SQL Server Agent job executes every 2 minutes. The job logs information to a text file named SQL1.log located on the E: drive.
Users report that the server is unresponsive. You discover the following problems:
- The SQL Server Agent job no longer functions.
- The SQL Server Agent service does not run.
You need to find out the cause of the SQL Server Agent service failure.
Which log should you examine?
Correct Answer: A
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?
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?
Correct Answer: C
You administer a Microsoft SQL Server 2008 R2 database that contains an OrderItems table. The table has the following definition:
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?
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?
Correct Answer: C
You maintain a SQL Server 2008 instance that contains 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?
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?
Correct Answer: A
You administer a SQL Server 2008 instance.
You need to configure the instance to use a single thread for queries that have an estimated execution cost less than 3.
Which sp_configure configuration option should you set?
You need to configure the instance to use a single thread for queries that have an estimated execution cost less than 3.
Which sp_configure configuration option should you set?
Correct Answer: D
DRAG DROP
You administer a Microsoft SQL Server 2008 R2 instance by using a database named AdventureWorks2008R2.
You need to implement transparent data encryption for a database hosted by the server.
Which four Transact-SQL statements should you use? (To answer, move the appropriate SQL statements from the list of statements to the answer area and arrange them in the correct order.)
You administer a Microsoft SQL Server 2008 R2 instance by using a database named AdventureWorks2008R2.
You need to implement transparent data encryption for a database hosted by the server.
Which four Transact-SQL statements should you use? (To answer, move the appropriate SQL statements from the list of statements to the answer area and arrange them in the correct order.)
Correct Answer:
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?
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?
Correct Answer: A