Review Questions

1.	You are a database administrator for your company. Your company has two offices located in Cuzco, 50 kilometers (31 miles) from each other connected by a 100Mb WAN. You need to design a fault-tolerant SQL Server 2005 database solution so that if a server crashes in one office, the solution will automatically failover to the other office. What high-availability technology should you use? Clustering Merge replication Log shipping Transactional replication
2.	You are a database administrator for your company responsible for a database solution running on a SQL Server 2005 instance. The database consists of a primary, secondary, and log file, all of which are stored on three separate drives. The database is using the SIMPLE recovery model. The database is backed up weekly on Sunday at 10 P.M. Differential backups are performed at the end of each working day at 9 P.M. A database snapshot is taken every morning at 9 A.M. When you come in at 10 A.M., you are informed that a contractor accidentally deleted a table in the database at 9:45 A.M. Other data modifications were made to the database up until 9:45 A.M. As you are examining this situation, the disk drive containing the database’s secondary data file fails. You replace the disk drive in the server. You need to recover the database as soon as possible before the contractor modified the data. What do you do? Revert the database to that morning’s database snapshot. Restore the latest full database backup. Restore last night’s differential backup. Back up the tail-log. Restore the latest full database backup. Restore the transaction log using the STOPAT option with a time of 9:44 A.M. today. Write a query that determines the changes between the database and the database snapshot. Insert or update the data into the tables as required.
3.	You are a database administrator for your company responsible for a database solution running on a SQL Server 2005 Enterprise Edition instance. The database is using the FULL recovery model. The database consists of the default file group that contains only the system tables, a second READ_WRITE file group that contains the tables modified by database users, and a READ_ONLY file group that contains market and census reference data. The market and census reference data are modified once, annually. These three file groups are kept on three separate drives. A full database backup is performed on the weekend. The READ_ONLY file group is backed up annually. The default and READ_WRITE file groups are backed up nightly at 10 P.M. Transaction log backups are performed every two hours throughout the week. When you come in at 11 A.M., you are informed that the disk drive containing the READ_WRITE file group has failed. A replacement disk drive has been installed. You need to recover the database as soon as possible. What steps do you need to perform? (Choose the answer with the steps in the correct sequence.) Restore all transaction logs since last night’s READ_WRITE file group backup. Restore the default file group backup from last night. Restore the latest READ_ONLY file group backup. Restore the tail-log. Restore all transaction logs since the last full database backup. Restore all transaction logs since last night’s default file group backup. Back up the tail-log. Restore the latest full database backup from the weekend. Restore the READ_WRITE file group backup from last night. 7, 9, 2, 5 2, 1, 5, 3 6, 3, 2, 5 7, 9, 1, 4
4.	You are a database administrator for your company. You have two separate databases for the sales and marketing departments. Fault tolerance and performance are critical for both departments. What fault-tolerant solution should you implement? A single-instance cluster A peer-to-peer replication solution A multi-instance cluster A merge replication solution
5.	You are a database administrator for your company responsible for a database solution running on a SQL Server 2005 instance. The database consists of a primary, secondary, and log file, all of which are stored on three separate drives. The database is using the BULK_LOGGED recovery model. The database is backed up weekly on Sunday at 10 P.M. Differential backups are performed at the end of each working day at 9 P.M. A database snapshot is taken every morning at 9 A.M. When you come in at 10 A.M., you are informed that a contractor has accidentally deleted a table in the database at 9:45 A.M. No other data modifications have been made after 9:45 A.M. Five data modifications have been made this morning before 9:45 A.M. As you are examining this situation, the disk drives containing the database’s log and secondary data file fails. You replace the disk drives in the server. You need to recover the database as soon as possible before the contractor modified the data. What do you do? Revert the database to that morning’s database snapshot. Restore the latest full database backup. Then restore last night’s differential backup. Back up the tail-log. Restore the latest full database backup. Restore last night’s differential backup. Restore the transaction log using the STOPAT option with a time of 9:44 A.M. today. Write a query that determines the changes between the database and the database snapshot. Insert or update the data into the tables as required.
6.	You are a database administrator for your company. Your backup strategy performs a full database backup of all the databases on the weekend. Coming in on Monday morning, you notice that your SQL Server 2005 instance crashed on Sunday night. Upon starting the server, your SQL Server 2005 instance refuses to start. The Windows Event Logs indicate that there has been some data loss during a write for the partition for the D: drive. You have run CHKDSK /F on the partition, and it seems there are no more errors. Upon examining the error log, you notice the following error: Error 2 (The system cannot find the file specified.) occurred while opening file 'D:\Microsoft SQL Server 2005\System Databases\master.mdf' to obtain configuration information at startup…' What is the first thing you should do? Run CHKDSK /F on the C: drive. Run the DBCC CHECKDB command. Run the SQL Server setup program from the CD, and reinstall the default instance. Run the SQL Server setup program from the CD, and rebuild the system databases.
7.	You are a database administrator for your company setting up a new SQL Server 2005 instance. You need to set up a fault-tolerance solution at the disk subsystem. The server has five 100GB disk drives purchased for it. Two of these disk drives are going to be used in a RAID-1 configuration for the Windows operating system, the SQL Server 2005 instance, and the transaction log files of the user databases. You plan to use the remaining drives for the database data files. The database files will initially be 120GB in size. You anticipate an annual growth of 10GB. What RAID level should you use? RAID-0 RAID-1 RAID-5 RAID-10
8.	You are a database administrator for your company. A database on a SQL Server 2005 instance has come up with a suspect status. The disk drive that contains the transaction seems to be responsible. Your backup set does not reflect the latest data within your database. You have tried to repair the database without success. You want to salvage as much good data as you can from the damaged database and want to perform and emergency repair. What steps do you need to perform? (Choose the answer with the steps in the correct sequence.) Set the database state to EMERGENCY. Set the database state to SINGLE_USER. Set the database state to SINGLE_USER. Run DBCC CHECKDB with the DATA_PURITY option. Run DBCC CHECKDB with the PHYSICAL_ONLY option. Run DBCC CHECKDB with the REPAIR_ALLOW_DATA_LOSS option. Run DBCC CHECKDB with the REPAIR_REBUILD option. Run DBCC CHECKDB with the REPAIR_FAST option. 2, 3 1, 6 4, 8 3, 7
9.	You are a database administrator for your company. You are responsible for configuring a VLDB on a SQL Server 2005 instance. You want to minimize the size of the transaction log for the VLDB. Your database recovery strategy must also cater to point-in-time recovery. What database recovery model should you choose? BULK_LOGGED FULL NON_LOGGED SIMPLE
10.	You are a database administrator for your company responsible for a database solution running on a SQL Server 2005 instance. The database is using the BULK_LOGGED recovery model. The database is backed up weekly on Sunday at 10 P.M. Differential backups are performed at the end of each working day at 9 P.M. Transaction log backups are made every hour between 7 A.M. and 7 P.M. A database snapshot is taken every morning at 9 A.M. When you come in today, you are informed that a contractor accidentally deleted two tables in the database at 9:45 A.M. No other data modifications have been made to the database yet. You need to recover the database as soon as possible. What do you do? Revert the database to that morning’s database snapshot. Restore the latest full database backup. Then restore last night’s differential backup. Restore the latest full database backup. Restore last night’s differential backup. Then restore all transaction log backups made today. Write a query that determines the changes between the database and the database snapshot. Then insert or update the data into the tables as required.
11.	You are a database administrator for your company. A new instance of SQL Server 2005 Express Edition was installed on a server two weeks ago by a junior DBA. The server was rebooted over the weekend as part of a planned maintenance task. On Monday, database users are complaining that they cannot access the SQL Server 2005 instance. What should you do? Check to make sure that the SQL Server Full-Text Search service has been configured to start automatically. Check to make sure that the SQL Server VSS Writer service has been configured to start automatically. Check to make sure that SQL Server Agent service has been configured to start automatically. Check to make sure that the SQL Server service has been configured to start automatically.
12.	You are a database administrator for your company restoring a SQL Server 2005 database solution. You plan to restore a full database backup followed by five transaction log backups. You want to know whether there are any media errors when you restore the database, but the database needs to be restored no matter what. What RESTORE options should you use? (Each correct answer represents part of the solution. Choose two.) CHECKSUM CONTINUE_AFTER_ERROR NO_CHECKSUM MOVE STOP_ON_ERROR RECOVERY
13.	You are a database administrator for your company. You are in the process of installing and configuring a new SQL Server 2005 instance, for which downtime for hardware failures must be minimized. Additionally, performance will be critical for this particular SQL Server 2005 database solution. You have decided to use RAID-1 for the Windows operating system and RAID-5 for the database files. The database solution will make heavy use of the tempdb system database. What RAID level should you use for the tempdb database? RAID-0 RAID-1 RAID-5 RAID-10
14.	You are a database administrator for your company. You are responsible for administering a SQL Server 2005 Enterprise Edition instance. Database users have notified you of error messages they are getting from their queries. After investigating these errors, you have determined that there are five corrupt pages in the database. You plan to restore these pages while the database is online. The backup strategy for this database solution includes a full database backup nightly and an hourly transaction log backup. What steps do you need to perform? (Choose the answer with the steps in the correct sequence.) Perform a new full database backup that will contain the damaged pages. Restore the full database backup using the PAGE clause. Perform a new full differential backup that will contain the damaged pages. Restore the transaction log backup that contains the final LSN of the damaged pages. Restore the new full database backup that will contain the damaged pages. Restore all transaction logs since the last full database backup. Perform a new transaction log backup that will contain the final LSN for the damaged pages. Restore the new differential database backup that will contain the damaged pages. 8, 3, 5, 1 1, 2, 3, 7 2, 6, 7, 4 6, 2, 1, 4
15.	You are a database administrator for your company responsible for a database solution running on a SQL Server 2005 instance. The database is using the SIMPLE recovery model. The database is backed up weekly on Sunday at 10 P.M. Differential backups are performed at the end of each working day at 9 P.M. A database snapshot is taken every morning at 9 A.M. When you come in at 11 A.M., you are informed that a contractor accidentally deleted a table in the database at 9:45 A.M. Other data modifications to other tables that need to be kept have been made to the database since. You need to recover the database as soon as possible. What do you do? Revert the database to that morning’s database snapshot. Restore the latest full database backup. Then restore last night’s differential backup. Back up the tail-log. Restore the latest full database backup. Then restore the transaction log using the STOPAT option with a time of 9:45 A.M. today. Write a query that determines the changes between the database and the database snapshot. Then insert or update the data into the tables as required.
16.	You are a database administrator for your company that has offices in Sydney, London, and New York. You are planning a fault-tolerant solution for a database solution running on a SQL Server 2005 instance located in New York. You want the database to be available in the Sydney and London offices in case of a disaster. What high-availability solution should you use? Single-instance cluster Database mirroring Log shipping Multi-instance cluster
17.	You are a database administrator for your company responsible for a database solution running on a SQL Server 2005 instance. The database consists of a primary, secondary, and log file, all of which are stored on three separate drives. The database is using the FULL recovery model. The database is backed up weekly on Sunday at 10 P.M. Differential backups are performed at the end of each working day at 9 P.M. A database snapshot is taken every morning at 9 A.M. When you come in at 10 A.M., you are informed that a contractor accidentally deleted a table in the database at 9:45 A.M. Other data modifications have been made to the database up until 9:45 A.M. As you are examining this situation, the disk drives containing the database’s primary and secondary data files fails. You replace the disk drives in the server. You need to recover the database as soon as possible before the contractor modifies the data. What do you do? Revert the database to that morning’s database snapshot. Restore the latest full database backup. Back up the tail-log. Restore the latest full database backup. Restore last night’s differential backup. Then restore the transaction log using the STOPAT option with a time of 9:44 A.M. today. Write a query that determines the changes between the database and the database snapshot. Then insert or update the data into the tables as required.
18.	You are a database administrator for your company. You are responsible for configuring a VLDB on a SQL Server 2005 instance. You want to minimize the size of the transaction log for the VLDB. The VLDB will be configured for database mirroring as part of a fault tolerance solution. What database recovery model should you choose? BULK_LOGGED FULL NON_LOGGED SIMPLE
19.	You are a database administrator for your company responsible for a database solution running on a SQL Server 2005 Enterprise Edition instance. The database is using the FULL recovery model. The database consists of the default file group that contains the tables modified by database users and a READ_ONLY file group that contains market reference data. The market reference data is modified once, annually. These two file groups are kept on two separate drives. A full database backup is performed on the weekend. The READ_ONLY file group is backed up annually. The default file group is backed up nightly at 10 P.M. Transaction log backups are performed every hour. Today, at 4:15 P.M., you are informed that the disk drive containing the READ_ONLY file group has failed. A replacement disk drive has been installed. You need to recover the database as soon as possible. What steps do you need to perform? (Choose the answer with the steps in the correct sequence.) Restore the default file group backup from last night. Restore the latest READ_ONLY file group backup. Restore all transaction logs since the last full database backup. Restore the tail-log. Back up the tail-log. Restore all transaction logs since last night’s default file group backup. Restore the latest full database backup from the weekend. 4, 3 2 1, 7 7, 1, 2, 4
20.	You are a database administrator for your company responsible for a sales database running on a SQL Server 2005 Standard Edition instance. The database is 300GB in size. It is running in SIMPLE recovery model. The sales department wants to run a batch operation after-hours on the database, which will take three hours to run. There is a possibility that they might want to undo the changes to the database after the batch operation has been committed. What should you do? Take a database snapshot. If the sales department decides to undo the batch operation, revert the database to the snapshot. Create a mark in the transaction log. If the sales department decides to undo the batch operation, initiate a database recovery, stopping at the mark. Record the time before the batch operation commences. If the sales department decides to undo the batch operation, initiate a database recovery, stopping at the time. Perform a full database backup. If the sales department decides to undo the batch operation, restore the full database backup.

Answers

1.	A. Only clustering offers an automatic failover solution.
2.	B. Because of the disk drive failure, you will have to restore the latest full database backup and last night’s differential backup. Yes, you have lost all changes made to the database in the morning. The snapshot cannot be used because of the disk failure. The tail-log is unavailable because the database is using the SIMPLE recovery model.
3.	D. The tail-log should be backed up to ensure that no data has been lost. Only the READ_ WRITE backup from last night needs to be restored. Then all transaction log backups after the READ_WRITE backup need to be restored. The tail-log needs to be restored last.
4.	C. A multi-instance cluster will allow the databases for the sales and marketing departments to run on separate physical servers, thus providing the best performance.
5.	B. Restore the latest full database backup. Then restore last night’s differential backup. You cannot use the database snapshot because of the disk drive failures. You cannot back up the tail-log because of the same disk failures.
6.	D. You should first get the SQL Server 2005 instance up and running. The SQL Server 2005 instance cannot run without the system database files. Rebuilding the system databases will enable you to start the SQL Server 2005 instance and continue your recovery process as required.
7.	C. A RAID-5 solution will provide you with fault tolerance and have sufficient capacity to store the database files. A RAID-0 solution is not fault tolerant. A RAID-1 solution will not have enough capacity. You do not have enough disk drives to implement a RAID-10 solution.
8.	B. To perform an emergency repair, you need to set the database to EMERGENCY mode and then run DBCC CHECKDB with the REPAIR_ALLOW_DATA_LOSS option.
9.	A. The BULK_LOGGED database recovery model will have a smaller transaction log size than FULL, while still supporting point-in-time recovery. The SIMPLE database recovery model does not support point-in-time recovery.
10.	A. Because no one else has modified the database, you can simply revert the database to that morning’s database snapshot.
11.	D. The SQL Server service needs to be running so that database users can access the SQL Server 2005 instance.
12.	A, B. The CHECKSUM option will indicate whether any backup checksums have failed. The CONTINUE_AFTER_ERROR option will ensure that the restore process will continue, irrespective of whether any checksums have failed.
13.	D. RAID-10 will give you both performance and fault tolerance. RAID-0 has no fault tolerance. RAID-1 will not give you the same performance benefits as RAID-10. RAID-5 will slow down write operations.
14.	C. Page restores need to be performed in this order.
15.	D. You will have to query the differences between the database and the database snapshot and recover the data as required.
16.	C. Only log shipping allows you to have multiple fault-tolerant databases. Database mirroring allows only a single mirror database. Clustering uses a shared disk array.
17.	C. Back up the tail-log. Restore the latest full database backup. Restore last night’s differential backup. Then restore the transaction log using the STOPAT option with a time of 9:44 A.M. today. You cannot use the database snapshot because of the disk drive failures.
18.	B. Only the FULL recovery model supports database mirroring.
19.	B. It is sufficient to restore just the READ_ONLY file group. There is no need to do anything else because no data has been modified in the READ_ONLY file group.
20.	D. Restoring the full database backup will enable you to recover from the batch operation. You cannot create a mark in the transaction log or stop at a point in time because the database is using the SIMPLE recovery model. Database snapshots are not supported in SQL Server 2005 Standard Edition.