Welcome to Certifiable, your exam prep headquarters. Here you'll find questions about some of the tricky areas that are fair game for the certification exams. Following the questions, you'll find the correct answers and explanatory text. We change the questions weekly.
Exam 70-028: Administering Microsoft SQL Server 7.0 includes five critical topics: data storage, security, replication, backups, and performance optimization. The exam focuses on these topics because they're critical job functions that every SQL Server administrator needs to know how to do. In fact, most Microsoft exams concentrate on a set of key topics. Microsoft lists all the topics an exam can cover on a separate Web page for each exam, but one secret to successfully passing the exams is to learn which topics correspond to critical job skills.
For example, Exam 70-028's topic list includes choosing the appropriate character set, but I can tell you that most of the time administrators simply choose the defaults. You choose the correct character set at installation time, and most people install SQL Server very rarely. You could probably spend 30 minutes reading the Books Online topic about character sets and be properly prepared for the exam. In contrast, all administrators must face disaster recovery sooner or later; therefore, you can expect more questions about the backup and restore commands.
My goal when I choose questions for this column is to find examples that cover those critical job skills so that I can help you make more efficient use of your study time. Don't neglect any topics, but do realize that some topics are more important than others.
You have filegroup1 on disk1 and filegroup2 on disk2. You have a customer table in filegroup1 and its index in filegroup2. Filegroup1 on disk1 fails. What is the quickest way to recover?
- Apply filegroup1 backup and filegroup2 backup.
- Apply filegroup1 backup, and rebuild indexes.
- Apply filegroup1 backup and filegroup2 backup, and restore all the transaction logs since the backup.
- Apply filegroup2 backup, and apply all the transaction logs for filegroup2.
Consider the following timeline:
8:00 A.M.—You start a full database backup.
8:10 A.M.—User1 enters order 7 for customer 333, whose name in the database is Matthew.
8:11 A.M.—The full database backup completes successfully.
8:12 A.M.—User2 at a remote server changes the name Matthew to Mark.
8:13 A.M.—User3 changes the name Mark to Luke.
8:14 A.M.—User3 commits the transaction.
8:20 A.M.—Media fails for the database.
8:30 A.M.—You restore the database from the 8:00 A.M. backup.
What is the status of the order 7 in the database?
- Order 7 is not in the database.
- Order 7 is in the database, and the name is Matthew.
- Order 7 is in the database, and the name is Mark.
- Order 7 is in the database, and the name is Luke.
The Database Owner (DBO) creates table A and grants Frank permissions to create views and stored procedures. Frank then creates a view based on table A and a stored procedure that updates table A. Subsequently, Frank grants Sherry SELECT permission on the view and EXECUTE permission on the stored procedure; however, Sherry can't use either the view or the stored procedure. How do you fix this permissions problem? (Choose all that apply.)
- Have the DBO grant Sherry SELECT permission on the view and the stored procedure.
- Have Frank grant Sherry SELECT permission on table A.
- Have the DBO grant Sherry SELECT and UPDATE permissions on table A.
- Have Frank grant Sherry SELECT permission on table A and EXECUTE permission on the stored procedure.
- Have the DBO take ownership of the view and stored procedure and grant Frank and Sherry appropriate permissions.
Answer to Question 1
The correct answer is C—Apply filegroup1 backup and filegroup2 backup, and restore all the transaction logs since the backup. If a table's indexes span multiple filegroups, you must back up all filegroups that contain the table and its indexes together, after which you must create a transaction log backup. Otherwise, the system might back up only some of the indexes, preventing you from recovering the indexes if you later restore the backup.
Answer to Question 2
The correct answer is B—Order 7 is in the database, and the name is Matthew. A database backup records the complete state of the data in the database at the time the backup operation completes, including transactions that completed during the backup process.
Answer to Question 3
The correct answers are C—Have the DBO grant Sherry SELECT and UPDATE permissions on table A; and E—Have the DBO take ownership of the view and stored procedure and grant Frank and Sherry appropriate permissions.
This situation in the question is known as a "broken ownership chain." When all objects that a view or stored procedure reference have the same owner, SQL Server checks only to see whether the user has appropriate permissions to use the view or stored procedure. With a broken ownership chain, SQL Server must check to see whether each referenced object's owner has granted appropriate permissions for the user to use the object. In this example, Sherry can't use Frank's view because she doesn't have SELECT permission for table A. She can't use Frank's stored procedure because it updates table A, and she doesn't have permission to perform updates to table A. Answer E is the preferred approach because answer C lets Sherry update the table directly without going through the stored procedure.