Online Access Free 70-432 Practice Test

You administer a SQL Server 2008 instance that contains a database named DB1.
The DB1 database contains the following stored procedure. (Line numbers are included for reference only.)

When a user named User1 attempts to invoke Procedure1, the following exception is raised:
"Msg 262, Level 14, State 1, Procedure Procedure1, Line 5 CREATE TABLE permission denied in database 'DB1'."
You need to provide User1 access to execute Procedure1 by allocating only the required permissions.
What should you do?

• A.Insert the WITH EXECUTE AS 'dbo' clause between lines 01 and 02.
• B.Insert the EXECUTE AS USER = 'dbo' statement between lines 02 and 03.
• C.Grant the ALTER permission on the Sales schema to User1.
• D.Grant the CREATE TABLE permission and allow User1 to drop the Sales.Table1 table.

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?

• A.cost threshold for parallelism
• B.max worker threads
• C.query governor cost limit
• D.priority boost
• E.precompute rank

You manage two SQL Server 2008 instances on separate servers named Server1 and Server2. The servers are located in different cities.
You plan to implement database mirroring between the two instances.
You need to set up the mirroring session to support the high volume of write transactions to the database.
You do not want small errors to impact the principal server.
What should you do?

• A.Configure the mirroring session with the SAFETY option set to FULL and the WITNESS option set to OFF.
• B.Configure the mirroring session to add a witness.
• C.Configure the mirroring session for high-safety mode with automatic failover.
• D.Configure the mirroring session with the SAFETY option set to OFF and the WITNESS option set to OFF.

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?

• A.You should reserve the most recent full database backup and the whole transaction logs made since the past backup could be made.
• B.You should detach the Sales database on SQL2. Copy the database file to SQL1, and link the database on both servers.
• C.You should reserve a full database backup on SQL2. Reserve the backup to SQL1.
• D.You should reserve only the newest transaction log backup.

You maintain a SQL Server 2008 instance that contains a database named Finance. The data file and the transaction log file are located on the E: drive. The E: drive has only 5 percent available space.
You need to move both files to the V: drive.
Which procedure should you use?

• A.Stop the SQL Server service. Move the data file to the new location. Start the SQL Server service. Run the following Transact-SQL statement. EXEC sp_attach_single_file_db @dbname = N'Finance', @physname = N'v:\SQLServer\Finance_Data.mdf';
• B.Run the following Transact-SQL statement. ALTER DATABASE Finance SET RESTRICTED_USER WITH ROLLBACK_IMMEDIATE; Move the data file and transaction log file to the new location. Run the following Transact-SQL statements. ALTER DATABASE Finance MODIFY FILE ( NAME = Finance_Data, FILENAME = 'v:\SQLServer\Finance_Data.mdf'); ALTER DATABASE Finance SET MULTI_USER;
• C.Stop the SQL Server Service. Move the data file and transaction log file to the new location. Start the SQL Server service. Run the following Transact-SQL statement. EXEC sp_attach_db @dbname = N'Finance', @filename1 = N'v:\SQLServer\Finance_Data.mdf', @filename2 = N'v:\SQLServer\Finance_Log.ldf';
• D.Run the following Transact-SQL statement. ALTER DATABASE Finance SET OFFLINE WITH ROLLBACK_IMMEDIATE; Move the data file and transaction log file to the new location. Run the following Transact-SQL statements. ALTER DATABASE Finance MODIFY FILE (NAME = Finance_Data, FILENAME = 'v:\SQLServer\Finance_Data.mdf'); ALTER DATABASE Finance MODIFY FILE (NAME = Finance_Log, FILENAME = 'v:\SQLServer\Finance_Log.ldf'); ALTER DATABASE Finance SET ONLINE;