Prepare with Microsoft 070-450 exam practice material, pass for sure

You administer a SQL Server 2008 instance that hosts a large financial database.
The database has the following backup strategies:
A full database backup is performed once a week.
A differential backup is performed every day.
A transaction log backup is performed every hour.
You plan to execute an end-of-year batch process that takes two hours to run. The batch process will modify about five percent of data within the database.
You need to ensure that if the Finance department does not approve the batch process, the batch operation can be rolled back in minimum possible time.
What should you do prior to starting the batch process?

• A. Perform a differential backup.
• B. Create a marked transaction. Perform a transaction log backup.
• C. Create a database snapshot.
• D. Record the time before the batch operation. Perform a transaction log backup.

Your SQL Server 2008 database contains two tables named Customer and Order inside the Sales schema.
The Sales schema is defined as follows:
It has a stored procedure named SetCustomerStatus that reads from Order and modifies Customer.
It is owned by a user named SalesUser.
Another user named MarketingUser wants to be able to call SetCustomerStatus and change the status of Customer.
You need to assign the least permissions necessary to MarketingUser.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

• A. Perform the following tasks: Set Select on Customer.
• B. Set only Take Ownership on Customer and Order.
• C. Perform the following tasks: Set Execute on SetCustomerStatus.
• D. Set only Execute on SetCustomerStatus.

You are the database administrator for a SQL Server that hosts an application defined as follows:
The database consists of 4 data files and one log file.
The physical server has 16 CPUs and 64 GB of memory.
Hardware non-uniform memory access (NUMA) is not available on this server platform.
Users of the application report that it is responding slowly. You use SQL Server Profiler to find out that the I/
O
performance issue is related to the lazywriter process.
You need to improve the performance of the application by reducing the I/O performance issue.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

• A. Configure a CPU affinity mask on the server to limit the instance of SQL Server to four CPUs
• B. Add additional RAM to the server.
• C. Add additional data files to the database.
• D. Configure four software non-uniform memory access (soft NUMA) nodes on the server.

You administer a SQL Server 2008 infrastructure . You design a corporate backup and recovery strategy that has to be validated.
You need to ensure the successful recovery of any single database from a catastrophic failure without requiring a backup data center in a different location.
Which three tasks should you include? (Each correct answer presents part of the solution. Choose three.)

• A. Script SQL login accounts and credentials.
• B. Document the administrative processes and application access requirements.
• C. Store all backup media offsite.
• D. Install all SQL Server instances on a failover cluster.
• E. Maintain one list of all Windows logins and passwords.

You administer a SQL Server 2008 instance that hosts a large database.
The following backup strategy is used for the database:
A full database backup is performed at 02:00 hr every Monday.
A differential backup is performed at 23:00 hr every day.
A transaction log backup is performed on an hourly basis.
A power failure on Thursday causes the SQL Server 2008 server to restart at 09:15 hr.
Fifteen minutes after the server restarts, the users report that they are unable to execute certain queries
that access
customer data. You discover that the customer data is unmodified after the power failure.
When you execute the DBCC CHECKDB command on the database, you receive the following error
message:
Object ID 2814307832, index ID 2, partition ID 83127819437122157, alloc unit ID 82134587923221126
(type In-row data): Page (3421:169) could not be processed. See other errors for d etails.
Table error: Object ID 2814307832, index ID 2, partition ID 83127819437122157, alloc unit ID
82134587923221126 (type In-row data), page (3421:169). Test (IS_OFF (BUF_IOERR, pBUF- > bstat))
failed. Values are 16928422 and -8.
CHECKDB found 0 allocatio n errors and 2 consistency errors in table 'tbl_Customer' (object ID
2814307832).
When you execute the sp_help 'tbl_customer' stored procedure you receive the following result set:
index_name index_description
PK clustered located on PRIMARY
NCIX nonclustered located on PRIMARY
You need to ensure that the data is available as quickly as possible with minimal effect on users.
What should you do?

• A. Restore the latest full database backup. Restore the latest differential backup. Restore all transaction log backups from the latest differential backup.
• B. Drop and recreate the NCIX index.
• C. Drop and recreate the PK index.
• D. Restore the latest full database backup. Restore all transaction log backups from the latest full database backup.