70-028 SQL7.0管理
1) You suspect, that most performance problems are caused by a small amount of process intensive queries. To reduce the overhead caused by these query you first need to identify the specific queries that are causing the problem. What should you do to identify these queries?
A.Use Transact SQL extended stored procedures in the queries to record trace information on each query.
B.Use Performance Monitor to log the SQLServer: Access Methods object
C.Filter in SQL Sever Profiler maximum milliseconds of CPU
D.Filter in SQL Sever Profiler minimum milliseconds of CPU
ANSWER: D
2)You want to repair allocation errors and delete corrupted text objects in your database my_db.
A. DBCC CHECKDB ('my_db', REPAIR_ALLOW_DATA_LOSS)
B. DBCC CHECKDB ('my_db', REPAIR_REBUILD)
C. DBCC CHECKALLOC ('my_db', REPAIR_ALLOW_DATA_LOSS)
D. DBCC CHECKALLOC ('my_db', REPAIR_REBUILD)
E. DBCC REPAIR_DB
Answer: A
3).You place the distribution database on the publication for replication. You then decide to use the distribution database that is located on the remote server.
What must you do to enable your publication to use the distribution database that is on the remote server?
A. Using SQL server Enterprise Manager, drag the publication to the distribution database on the remote server.
B. Disable the current publication and enable a new publication to use the distributor database on the remote server. Recreate all publication and subcribers.
C. Configure the current publication to use the distributor database on the remote server.
D. Back up the current distribution database and restore the backup to the distribution database on the remote server.
ANSWER: B
4) Every night the sales database backup fails. Which DBCC command could repair the Sales database and check the data integrity and indices?
A. DBCC NEWALLOOC
B. DBCC CHECKALLOC
C. DBCC CHECKDB
D. DBCC DBREPAIR
Answer: C
5) You restore the database from tape backup - upon 50% you have a tape drive failure - you replace the drive and want to finish your backup - what now?
A. EXEC RESTORE WITH RECOVERY
B. EXEC RESTORE WITH RESTART
C. EXEC RESTORE WITH STANDBY
D. EXEC RESTORE WITH REPLACE
E. EXEC RESTORE WITH NORECOVERY
Answer: B
6).Maria preceded you as administrator for NT server and SQL server, Maris create sql server login and password matching users’NT accounts. When users log in to the WINNT server domain, the obtain access to sql server, which permission do uses receive when they access sql server by using a trusted connecting?
A. Only the same permissions as those that are granted to theirs sql server login.
B. Only the same permissions as those that are granted to theirs WINNT account.
C. Cumulative WINNT account and sql login permissions
D. Only the permissions that are granted in common to theirs sql server login and the WINNT account.
Answer: B
7) Some front-end applications receive error 1205. The developers invoked error handling. The queries appear to be very slow and sometimes appear to stop responding.
You want to find out which objects are causing the error 1205 error message and weather the objects are degrading user response time.
A. NT Performance Monitor : SQLServer:Locks
B. NT Performance Monitor : SQLServer:Lock .request/sec
C. SQL Profiler trace lock: acquired, lock:released, lock cancel
D. SQL Profiler trace lock: dead lock, lock:dead lock:chain, lock: timeout
Answer: D
8).The server’s reponse time has recently slowed as the result of a small number of very long-running queries.These are ad hoc queries that are pooly written.You
need to prevent the queries from running for long periods of time?
What must you do?
A.Set SQL profiler to filter on queries time
B.Set the query governor to stop long-running queries
C.Use Windows NT performance Monitor to send an alerts alert that will run a job
to kill the process.
D. Decrease the query wait setting.
Answer: B
9) A query which load data on Production Server runs. Log is approaching 500MB, less since 50% done. 3GB HD was freeDB growth was auto. The query blocks out other users.
How could you give the general user quick access to the database for use.
A. Kill update process
B. Let the update process runs until it finished
C: Kill the blocked processes
D. Stop and restart the SQL server
E. Truncate the transaction log
Answer: A
10). You load a large amount of data to Parts table in Inventory database in four hours, what is the quickest way to ensure that statistics reflect the changes.
A.sp_updatestats on Inventory database
B.sp_updatestats on Parts database
C.UPDATE STATISTICS on Parts table
D.UPDATE STATISTICS on each index in Parts table
Answer:C
11) Your domain has two SQL Server computers named SQL1 and SQL2. Two related databases are on SQL1 and SQL2. You have standard logins on both servers. You are able to connect to both databases on both servers, and you are able to execute queries on both databases on both servers. You are attempting to execute a distributed query that joins data on SQL2 to data on SQL1. On SQL1, you add SQL2 as a linked server. However, when you try to execute the distributed query on SQL1, you receive a message that your login failed. What is the most likely cause of the problem?
A. You did not execute the query on SQL2.
B. You did not map your login on SQL1 to your login on SQL2.
C. You did not use a Windows NT login for the distributed query.
D. You did not establish a direct connection to SQL2, so you are not authenticated for the distributed query.
Answer: B
12) Your SQL sever database is currently running on a single computer. To improve the availability of the SQL server database to an accounting application, you are implementing a second computer running Microsoft NT Server. The accounting application must be available every business day between AM 9 and PM 5. In event of disk drive fail the data must be recoverable to the end of the previous business day. How should you configure the second computer?
A. install it as a SQL Server standby server
B. Install it to use SQL Server Failover support.
C. Set up SQL Server Transaction replication.
D. Set up Windows NT-based replication
Answer: A
13) Web based SQL server with internet users needing to access order entry application to enter new orders and enquires. You must develop a strategy to ensure availability of application and integrity of database. You use a 2nd PC, how do you configure it:
A. Standby server
B. Failover Support
C. SQL Transactional Replication
D. Windows NT based replication
Answer: B
14).Publisher(SQL6.5;TOR01),Distributor(SQL6.5;TOR02), Subscriber(SQL6.5;TOR03)
In which order should you upgrade the server to SQL 7.0?
A. TOR01, TOR02, TOR03
B. TOR02, TOR01, TOR03
C. TOR03, TOR02, TOR01
D. TOR03, TOR01, TOR02
Answer: B
15) A user who has a SQL Server login forgot his password. How should you restore his ability to connect to SQL Server?
A. Drop and re-create both his Windows NT account and his SQL Server login with a new password.
B. Drop and re-create only his SQL Server login with a new password.
C. Change his account password in Windows NT User Manger.
D. Change his password in SQL Server login properties dialog box.
Answer: D
16) Your company acquired a new Company which has an Oracle database for its employees. Your company uses SQL Server for its employee database. You want to merge the data from the new company into your SQL Server Employee database. Below is the structure of both databases:
Oracle
(Southwest Financial Serivces employee table) SQL Server
(Woodgrove Bank emplyee table)
Employee ID Employee ID
Fname First name
Lname Last name
Address Addressline1
City Addressline2
State City
Zip_code State
Zip_code
What method should be used to accomplish the merge.
A. Use the bcp utility.
B. Use the statement SELECT..INTO
C. Use the statement INSERT INTO
D. Use the statement BULK INSERT
Answer: C
17). You recently set up transactional replication,and you are now testing.
You update 100 rows in a published table.You verity that the changes were made on
The corresponding table on the Subscriber.
You look at the Msrepl_commands table in the distribution database,and you notice
That the table is empty.What is the most likely cause?
A.The Msrepl_commands table was automatically purged after transactions were received by the Subscriber.
B.The log Reader Agent purged the the Msrepl_commands table in preparation for another batch of replicated changed-transactions.
C.The replication distibution cleanup job run and purged the Msrepl_commands table
D.The log Reader Agent did not log activity to the Msrepl_commands table
Answer: C
18) Your company sql server is in the same BDC as the server running Exchange Sever. The exchange server is performing poorly. What should you do in SQL server configuration to improve performance of the exchange server while not adversly affecting SQL server?
A. increase memory allocated to the procedure cache option
B. set the working size option to 1
C. decrease the min server memory setting
D. increase the min server memory setting
E. decrease the max server memory setting
Answer: E
19) Your company's server running SQL Server is on the same BDC as the server running Microsoft Exchange Server. Exchange Server is performing poorly. The SQL Server set working set size option is at the settings you configured for the min server memory and max server memory options. What should you do to improve the performance of the server running Exchange Server while not adversely affecting SQL Server?
A. Increase the memory allocated to the procedure cache option.
B. Set the set working set size option to 1.
C. Decrease the min server memory setting.
D. Increase the min server memory setting.
Answer: C
20) You have 50,000 records in a database file and you know you want to add another 100,000 records in the next several weeks.What fill factor would you use to maximize performance.
A. 0 (default setting)
B. 30
C. 70
D. 100
Answer: B
21) You define full-text indexing on the ProductName column in the Products table. You then execute a full-text query on the column. You specify a word that you know is present in the column, but the result set is empty.
What is the most likely cause?
A. The catalog is not populated
B. You did not crete a unique SQL Server index on the ProductName column
C. The Microsoft Service is not running
D. The SQL ServerAgent Service is not running
Answer: A.
22) You are relocating a computer running SQL Server to a different region,and you want to change the unicode collation.What must you do?
A. Run the SQL Server setup program
B. Back up your database; Run the rebuidm.exe utility;Restore your database.
C. Script your database objects, and export your data to files;Run Regrebld.exe;
Re-create your database objects, and reload the data.
D. Script your database objects, and export your data to files; Run Rebuildm.exe;
Re-create your database objects, and reload the data.
Answer: D
23) You need to setup SQL Server security to base access for two applications. The applications are named Payroll and Sales. Two types of users in the Payroll application. Those who can modify data and those who can only query on existing data. There is only one type of users in the Sales application. Those who can both modify and query on existing data. What should you do to setup security access to the table?
A. Create a single application role. Grant permissions for the role for each type of access to the Payroll application. Grant permissions for the role for the single type of access to the Sales application.
B. Create one application role for each application. Grand permissions on each role with different passwords.
C. Create two application roles for the Payroll application and grant permissions on each role for each type of access. Create one application role for the Sales application and grant permissions to the role for the single type of access.
D. Create two application roles for the Payroll application and grant permissions on each role for each type of access. Create one application role for the Sales application and grant all users of the sales application the same permissions.
Answer: C
24) To increase security, your company decides to prohibit Windows NT administratiors from accession SQL Server administrative functions. What should you do to prohibit access?
A. Deny access to the Windows NT Administrator group.
B. Rename the current Windows NT Administrator group, and create and empty Windows NT administrator group.
C. Remove the Windows NT Administrator group from the sysadmin role in SQL Server.
D. Install SQL Server on a member Server instead of a domain controller.
Answer: C
25) You are the DBA for a 750-MB database that was recently upgrated to SQL Server 7.0.The user’s hardware includes three 1-GB hard disk drives,There is no RAID in effect.
The following table shows the files on each disk drive.
Drive C Drive D Drive E
Windows NT Server system
Files SQL Server database Transaction log
SQL Server system files Backuo devices
Application data User files
Master database and msdb
Database
The database is updated infrequently, but it is heavily queried.The user reports that queris are requring too much time.
What can you do to improve query performance?
A. Move the Transaction log form Drive E to Drive C.
B. On Drive E, define a new filegroup for database, and place heavily used indexs in the new filegroup.
C. On Drive C, define a new filegroup for database ,and place heavily used indexs in the new filegroup.
D. Define a new filegroup for Transaction log,and place it on Drive C.
ANSWER: B
26) You have SQL 7.0 on Win 98 machines in Branch offices and in the corporate you have SQL 7.0 on Win NT Server. Branch offices report slow performance. database Admin is in the corporate office. How should the profiler be configured in the branch offices so that they can be centrally collected in the Corporate office by the database Admin?
A. Use SQL Profiler Xtended sp and send them to the NT App log in the central office.
B. Use SQL pr.Xtended sp and send them to the centralized table in the corp. office.
C. Use xp_sendmail procedure to send the statistics thru e-mail to the database Admin
D. use Operation system commands to build a file etc., and send them to the corp office.
Answer: B
27) In Replicaiton: you have finance database. there are many publications on it. accounting group subscribes to one of the publication. but they want to subscribe only to one article..how it can be done with min admin steps.
A. filter on the publication so that they can subscribe to one article and use pull subs.
B. filter on the publicaiton same as above but with push subs.
C. create a new publication with the above article.
Answer: C
28) You start receiving continuos error messages by e-mail indicating that the transaction log is full. After 2 days, the messages suddenly stopped. What could be the reason ?
A. The autogrow option of SQL server automatically corrected the problem
B. Windows NT App log is full
C. SQL Server Agent stopped
D. SQL Server Log Reader Agent stopped
Answer: B
29) You have a SQL Server 6.5 application. You want developers to start working on 7.0 but do not want to upgrade the database until the new application is ready. What is the quickest way to do this ?
Answer: Upgrade the four win98 client computer to Microsoft Windows NT workstaion
Install SQL Server 7.0 onto the four computer using the same installation options as for the current
Finance department server
Run the SQL Server Upgrade Wizard on the four computers to upgrade the Finance database
30) The computer which was running SQL server crashed. Windows NT is reinstalled. What is the quickest way to get SQL server up and running?
A. Restore registry from backup
B. Run regrebld.exe
C. Restore database from last backup
D. Rebuilder Master database
E. Run SQL Server Setup again
Answer: B
31) You are a member of fixed server role serveradmin. You create a job which backup up the database and then copies the backup file on a network driver. When you do the individual steps, they run okay. However, when executed as a job, it backs up the database but fails to copy to the network drive. Why ?
A. SQLServerAgent service is not running
B. SQLAgentCmdExe does not have permissions to write on the network drive
C. Alerter service is not running.
D. SQLServerAgent has no rights for the network share
Answer: B
32) You want to increase security on the Finance Database.so that only members of the FINANCE group can run and execute applications. You create a Testlogin that is not a member of the finance group but is able to query and get results "finance.costs."
A. Disable the guest login
B. Deny permission for the Finance database to all NT groups except the Finance users
Answer: B
33) Installing SQL on a network, there are Windows 95, NT Workstations and Netware clients. All must be able to connect. Choose all that apply:
A. Create NT logons for Windows NT clients
B. Create NT logons for Windows 95 clients
C. Create NT logons for Netware clients
D. Create SQL logons for Windows NT clients
E. Create SQL logons for Windows 95 clients
F. Create SQL logons for Netware clients
Answer:A,B,F
34) Eric needs to manage user accounts on the HumanResources database. Which minimum role can you apply to him:
A. db_securityAdmin
B. db_accessadmin
C. db_owner
D. securityadmin
e) serveradmin
Answer: B
35) You install SQL server on an IIS machine. You need to optimise IIS. What do you configure on SQL Server ?
A. Set working set size
B. Min Server memory
C. Max server memory
D. Max worker threads
Answer: C
36) Your company has a Microsoft Visual Basic Application that stores its data in SQL Server .You want to use a SQL Server alert to provide motification of problems with application.
What should you do so that you will receive notification when there is a problem with the application
A. Create a user-defined SQL Server message that corresponds to the error.Instruct the developer to program the application to use SQL Server’s sqlalrtr writing to log the error on SQL Server.
B. Create a user-defined SQL Server message that corresponds to the error.Instruct the developer to program the application to use RAISERROR statement to invoke the error on SQL Server.
C. Create a user-defined SQL Server message that corresponds to the error.Instruct the developer to program the application to use log the error in Windows NT application event
Log and use user-defined SQL Server message number.
D. Instruct the developer to program the Application to cause a minor error on SQL Server.Configure an alert to be triggered by the resulting error number.
ANSWER: B
37) You are moving a server from FINANCE DOMAIN to MONEY domain. SQL Server is not able to start.why?
A.Money is a Datatype in SQL. so it cannot be restarted.
B.SQL NT Accessing is inaccessible.
C.Internal SQL server is now different than the Network name.
D.Domain name cannot be changed after installing SQL.
Answer: B
38) SQL server is configured with a Net-library with TCP/IP socket 1450 which is non standard port setting. How to configure the client computers?
A. At the server use the SQL Server Network utility to use port 1450.
B. At the server use the SQL Server Client utility to use port 1450.
C. At the Client configure the SQL Server Client Network Utility to change the TCP/IP port to 1450.
D. At the Client modify TCP/IP services to associate to sqlserver on port 1450.
Answer: C
39) Browsing of other servers from an SQL server. running on Win98 is not possible
Browse list is not avilable..When trying to register they are not able to do it.
But they are able to connect for File sharing.why?
A. verify the Win98 machines are connected to the same subnet.
B. verify the Win98 machines are connected to using the same Net-library
C. verify the Win98 machines are having proper logins
D. Browsing is not supported in win98
Answer: D
40) Replication. (Note: the 56kbps is in the drawing is not directly connected to any computer!!)
( NY1----------T1-----------NY2)
I
56kbps
I
( Seattle-----T1----SanFrancisco----T1---Los Angeles)
NY1=NewYork1, NY2=NewYork2, ,SF= SanFrancisco, LA= Los Angeles
A.NY1=pub/dist NY2,Seattle,LA as subscriber,SF as a sub/pub/dis
B NY1=Pubs SF=subs/dist/pubs NY2,Seattle,LA as subscriber
C.NY1 =pubs NY2=dist Seattle,SF,LA as Subs.
D.NY1=Pubs LA= dist something not workable.
Answer: A
41) Three Users ANITA ,CARMEN,PHILIPPE are all having db_owner roles currently.for the FINANCE database.you want PHILIPPE to continue to manage the Database as before,Anita to ONLY manage users and grant permissions for users,Carmen should be ONLY able to create Tables,views,indexs and other objects.
A.Remove Carmen from db_owner and add to db_ddladmin role.
B.Remove Anita from db_owner role and db_accessadmin role
C.Remove Anita from db_owner role and add to db_security and db_accessadmin roles.
D.Remove Anita from db_owner role and add to db_security only.
Answer: A,C
42) You want to change the sort order of the company’s customer database. How can you implement?
A. Run Regrebld.exe
B. Run Rebuildm.exe
C. Use Properties Dialog Box from your database
D. Use SP_CONFIGURE
E. Run SQL Server Setup again
Answer: B
43) You want to export your information from your databse into a comma-seperated-values format textfile named output.csv. what cmdline do you choose?
A. bcp db..developer in c:\data\txt\output.csv -n -t\c -Usa -Ppaswd
B. bcp db..developer out c:\data\txt\output.csv -N -t\t -Usa -Ppaswd
C. bcp db..developer out c:\data\txt\output.csv -c -t, -Usa -Ppaswd
D. bcp db..developer in c:\data\txt\output.csv -w -t -Usa -Ppaswd
Answer: C
44) You want export data from a temp tabel #tempdb with bcp, but it does not work.
bcp #tempdb..table out c:\tempfiel.txt -n -Sserver -Uuser –Ppassword
how should you do?
A. Use ##tempdb as the destination for the SELECT..INTO statement, then use bcp
B. Use DTS Export Wizard instead
C. Use the BULK INSERT statement
D. You can not export data from a temporary table with bcp
Answer: A
Notice: D也是正确的，可根据不同的问法来选择。
45) Arbor Shoes has 100 store. Their Pricing database resides on the cooperate server. The pricing database server has limited space. The pricing database contains a price table. The price table has a high frequency of updates. The in-store checkout application requires an up-to-date local price table in order to ensure accurate pricing which type of SQL Server replication should you use to replication the price table to the in-store computer?
A. Snapshot replication with a central Published, remote Distributed, and Multiple Subscriber
B. Transactional replication with a central Published, remote Distributed, and Multiple Subscriber
C. Transactional replication with a central Published/ Distributor, and Multiple Subscriber
D. Snapshot replication with a central Published/ Distributor, and Multiple Subscriber
Answer: B
46) Your company has five call centers that are located worldwide. These call centers manage the reservations for an airline.
The reservation application has a customer table on the cooperate server. The customer table is read locally and can be updated locally but the updates must maintain consistency across all five locations.
Which type of SQL Server replication should you implement?
A. Merge replication with push subscribers.
B. Merge replication with pull subscribers.
C. Transaction replication with push subscriptions.
D. Transaction replication with immediate-updating subscribers.
Answer: D
47). You company use SQL 4.2 but plan to upgrade SQL7.0. How must you accomplish to migration?
A. Use the SQL Server Upgrade Wizard.
B. Use the bcp utility to extract all the data from the SQL 4.2 database and then load the data into a new SQL 7.0 database.
C. Upgrade SQL4.2 to SQL 6.5 and then upgrade to SQL7.0
D. Use Data Transaction Service (DTS) to migrate the data from SQL4.2 to SQL7.0
Answer: C
48). You are developing security strategy for a new installation of SQL. You want to minimize the administration required of login. What should you do?
A. Use Windows NT Authentication
Add Windows NT accounts as SQL Server login.
B. Use Windows NT Authentication
Add Windows NT groups as SQL Server login.
C. Use SQL Server Authentication
Add SQL Server login for authorized users
D. Use SQL Server Authentication.
Add Windows NT accounts as SQL Server login.
Answer: B
49). You install SQL Server onto a Microsoft Windows NT Server computer that is a member of the workgroup SQLGROUP. Your users' computers ate also members of SQLGROUP. None of the users can connect to the SQL Server by using SQL Server Query Analyzer, even though they are Using valid SQL Server login IDs and passwords. You use the SQL Server Client Network Utility to confirm that the default Net-Library is Named Pipes. What is the most likely cause of the problem?
A. The users' computers are, not configured with a valid ODBC DSN for the SQL Server.
B. The users' computers are not connecting by using a trusted connection.
C. The SQL Server database files are not available in a network share.
D. The server's windows NT Quest account Is disabled.
Answer: B
50). Your company's current applications use MSAccess. New application will use SQL Server. The new application must be able to access data from the Access database. What must you do, so that SQL Server queries can access the Access database?
A. Register connection information for the Access database in SQL Server.
B. Register connection information for SQL Server in the Access database.
C. Establish a connection to the Access database when a query is executed.
D. Use the same MS Windows NT domain account for the Access database and SQL Server.
Answer: A
51) Your servers run SQL Server 6.5 and 7.0.You want to centrally manage these servers from your workstation by using SQL Server Enterprise Manager.
What must you do?
A. Install only the SQL Server 7.0 administrative tools and Client connecitivity components on your workstation
B. Install the SQL Server 6.5 and the SQL Server 7.0 administrative tools and Client connecitivity components on your workstation
C. Install the SQL Server 7.0 administrative tools and Client connecitivity components on the servers that run SQL Server 6.5.
D. Install the SQL Server 7.0 administrative tools and Client connecitivity components on the servers that run SQL Server 7.0
Answer: B
52) You change the memory settings for SQL Server. Now SQL Server will not start. You want to restore SQL Server's previous functionality, and you want to minimize downtime and the potential for loss of other server settings or objects. What should you do?
A. Start SQL Server at the command prompt with the appropriate parameters, and reconfigure the memory settings.
B. Edit the appropriate section of the registry to change memory settings.
C. Reinstal SQL Server. Start SQL Server in single-user mode, and restore master database from the most recent SQL Server backup.
D. Restore the master database files from the most recent Windows NT file backup
Answer: A
53) As the database owner you grant Franz permission to create views and to create stored procedures in the Finance database. Franz created a stored procedure that does an update on the dbo.prices table. He creates a view that selects price informations for a report. He grants Susanne select permission on the view and execute permission on the stored procedure. What must be done so that Susanne can obtain the results by using the view and the stored procedure?
A. You must grant Susanne select permissions on the view and the stored procedure.
B. Franz must grant Susanne select permissions on the Prices table
C. You must grant Susanne select and update permissions on the Prices table
D. Franz must grant Susanne execute permissions on the stored procedure and select permmissions on the prices table.
Answer: C
54) The total of the column definitions for one row of one table in your database is 5000 bytes. You expect to have 100,000 rows in the table when the data is fully loaded into it. The data portion of this table will consume approximately how much disk space.
A. 5 GB
B. 300 MB
C. 500 MB
D. 800 MB
Answer:D
55). Your History database consists of two files,History_data.mdf and History_log.ldf.History data has grown from its original size of 500 MB to 1 GB.
The database is not configured to automatically shrink.
You delete data so that you now have free space of 800 MB in the History database.
You want to reclaim 600 MB of the free database space.
Which statement should you execute to reduce the physical size of the History_data
File to the desired size?
A. DBCC SHRINKFILE (History_data,60)
B. DBCC SHRINKFILE (History_data,400)
C. DBCC SHRINKDATABASE(History,50)
D. DBCC SHRINKDATABASE(History,400)
E. ALTER DATABASE History MODIFY FILE(NAME= History_data.SIZE =200)
Answer:B
56). Your company purchases a new Pentium computer that has 256 MB of RAM and five 18. 2-GB disk, drives. The new computer will be used to support the Payroll application. The application requires the highest availability and performance possible. The Payroll database has been initially sized at 25 GB. You need to organize the database files on the new computer. Which actions should you take?
A. Create a hardware RAID 0 configuration that uses all the disk drives. Create the primary data file and a transaction log data file on the RAID 0 set.
B. Create a software RAID 5 configuration that uses all the disk drives. Create the primary data file and a transaction log data file on the RAID 5 set.
C. Create one mirrored set that uses drives D and E. Create another mirrored set that uses drives F and G. Create the primary data file on one of the mirrored sets, Create the transaction log file on the other mirrored set.
D. Create a mirrored set that uses drives F and G. Create the transaction log file on the mirrored set. Create a hardware RAID 5 set that uses drives C, D, and E. Create the primary data file on the RAID 5 set.
Answer: D
57) Consider the following time line:
8:00 Backup starts
8:01 Jan enters order 84 for customer 10, whose name in the database is Bill Smith
8:14 Transaction for order 84 is committed
8:15 Backup finishes
8:20 Joan changes the name for customer 10 to James Jones. Transaction is committed
8:30 Media fails for database
8:40 Database is restored from 8:15 backup
What is the state of the order 84 and customer 10?
a Order 84 is not in the database
b Order 84 is in the database for Customer 10 whose name is Bill Smith
c. Order 84 is in the database for Customer 10 whose name is James Jones
Answer: B
Notice:只要交易能够在备份完成之前完成，就可以保存在备份中。更复杂的变例如下：
如果只有从文件损坏，而数据库的主文件和log文件没被损坏，就可以备份log，在恢复时，先应用
fullback,再应用log备份。就上例而言，8：20所做的变更也能被恢复。
58) . Most of your database query are executed as stored procedures you need to find out whether most of the store procedures are precomplied when they are executed. You also need to identity store procedure that are not precompiled but that should be precompiled.
How should you modify the performance of the store procedure?
A. in NT performance monitor, monitor the sqlserver: cache manager cache hit radio counter and the sqlserver: cache manager cache use counts/sec counter while repeatedly executing the store procedure.
B. in NT performance monitor, monitor the adhoc sql plans and the prepared sql plans instances of the sqlserver: cache manager object.
C. In sql profiler, monitor the sp: cachehit and sp:cacheinsert event class.
D. In sql server profilers, monitor the sp:starting and sp:cacheremove event class
Answer: C
59) you must restore the MASTER database from a backup on a SQL Sever7.0 computer.You attempt to start the restore process but receive an error message,what must you do before you can restore MASTER?
A.Run the Rebuildm.exe program.
B.Run the Sqlservr.exe program with the -m option
C.Run the RegreblD.exe program
D.Drop the MASTER database.
Answer: B
NOTICE:if the MASTER database needs to be restored from a backup you must start SQL Sever in the single-user mode. the rebuildm utility rebuilds MASTER from scratch to the state it was in after SQL Sever was installed
60) .You plan to implement a large batch job.You want to be able to restore the database quickly and easily in the event that the batch job fails.
What should you do before running the batch job?
A. Perform a differential database backup of the database.
B. Perform a full database backup of the database.
C. Perform a backup of the transaction log.
D. Use a single transaction to perform the batch job.
ANSWER: B
61) .You install transactional replication on the Manufacturing server and make several publications availabele.The Accounting department subscribes to one of the publications,but Accounting users need data from only the WorkSchedule
article.
What is the easiest way to provide only this data for them?
A. Create a subscription directly to the WorkSchedule article on the Accounting server.
B. Create a filter on the publication so that only the WorkSchedule article can be subscribed to implement a push subscription to the Accounting server.
C. Create a filter on the publication so that only the WorkSchedule article can
be subscribed to implement a full subscription to the Accounting server.
D. Create a new publication containing only the WorkSchedule table.
ANSWER:D
62). Your company’s server is runing both SQL and EXCHANGE, The EXCHANGE administrator Reports that EXCHANGE need more memory. You examine SQL server and find out that there is a very low value for the SQLserver: page writes/sec counter, and that the size of the working set is below the configured value in WINNT performance monitor ,the free buffers and buffer cache hit ratio counters in the SQLserver buffer manager object are high.
What can you do to improve the performance of EXCHANGE sever without degrading the performance of SQL Server?
A. low the max lazywrite I/O setting.
B. Increase the mix server memory setting
C. Decrease the max server memory setting
D. Set the configuration option set working set size to zero
Answer: C
63) Your company has three servers running SQL Server in adjoining buildings.You are responsible for monitoring performance of stored procedures and for monitoring
the connnections to the servers.
In order to do this monitoring,you want to forward all applicable information to
The server in your building.You also want to automatically start monitoring in the
Event that SQL Server shuts down and then restarts on any of the three servers.
What must you do to most these goals?
A. Use Windows NT Performance Monitor to monitor all those servers from on location.
B. Use SQL Server Profiler extended stored procedures to forward a trace to the server in you building,and make the stored procedures startup stored procedures.
C. Use the Create Trace Wizard in SQL Server Profiler to create a trace on the server in your building, and reply the trace on the other two servers.
D. Use the Create Trace Wizard in SQL Server Profiler to create a trace on eachserver in your building, and reply the trace on the servers in your building.
E. Use Windows NT Performance Monitor from each server,Log each server’s activity and forward it to the server in your building.
ANSWER: B
64) You are planning to install the Standard edtion of SQL Server 7.0 on three Windows NT Server computers.For the purpose of database replication,you want one of the servers to function as a Publisher and the other two servers to function as Subscribers What is the best way to configure the MSSQLServer service account for the three computers?
A. On each of the three computers,you should assign the MSSQLServer service to a local syestm account
B. On each of the three computers,you should assign the MSSQLServer service to a local user account
C. For all three computers,you should assign the MSSQLServer service to a common domain user account
D. For each of three computers,you should assign the MSSQLServer service to a separate domain user account
ANSWER:C
65) .To support a new transaction,you create an index on the largeswt table
in the Sales database,It takes you two hours to create the index.
You want to ensure the fastest possible reconstruction of the index in the event of a database failure.what should you do immediately after creating the index?
A. Perform a transaction log backup of the Sales database.
B. Perform a full database backup of the Sales database.
C. Perform a differential database backup of the Sales database.
D. Perform a full database backup of the master database.
ANSWER:B
66) . Your Database is updated by a batchprocess every day at 2.00am with the data from the last day.Which backup-strategy should you use?
A. full back up at sunday, differential back up every day after batchprocess
B. full back up at sunday, differential back up every day
C. full back up at sunday, log back up every 6 hours
D. another option with log back up
ANSWER: A
67). .productgroup has acces to database. Carmen is member of the productgroup.You want deny productgroup access to database but Carmen should access to this database. how?
A. grant Carmens account to database after denying productgroup access to database
B. create a new SQL-login for Carmen
C. remove Carmen from productgroup and grant her account to the database
D. do nothing, the publicrole and the guestaccount grants Carmen enough access
ANSWER: C
68) Your database is replicated among four servers. You want to schedule database
consistency checks and full database backups of all four copies of the database
You also want to minimize your administrative workload.Server configurations are
Shown in the following table.
Server name Operating system SQL Server version
SQL7NT1 Windows NT server 7.0
SQL7NT2 Windows NT server 7.0
SQL65NT1 Windows NT server 6.5
SQL65NT2 Windows NT server 6.5
How should you implement scheduled checks and backups of all four copies of the
Database so that your administrative workload will be minimized?
A. Separately implement scheduled database checks and backups on each of the four
servers
B.Create a job on the SQL7NT1 to check and backup the database.
Script the job on SQL7NT1.Execute the script on the other servers to install the
scheduled checks and backups.
C.Make SQL7NT1 a master server.
Enlist SQL7NT2 as a target server.
Create a job on the master server to check and backup the database on both SQL
Server7.0 servers.
Script the job on SQL7NT1.Execute the script on the version 6.5 to install the
the scheduled checks and backups.
D.Make SQL7NT1 a master server.
Enlist SQL7NT2 as a target server.
Create a job on the master server to check and backup the database on both SQL
Server7.0 servers.
Separately implement scheduled database checks and backups on the other two
servers.
ANSWER:D
69). It took you hours to create an index on a table. What should you do to
protect it?
A. back up master database
B. back up user database
C. make differential backup of user database
D. back up log
ANSWER: B
70).You created procedures on your testsystem. Now you want to transfer this
procedures to the Productserver. Who?
A. back up msdb on testsystem and restore on Productserver
B. recreate procedures on Productserver
C. make a script creating this procedures, run this script on Productserver
ANSWER: C
71)Your company's SQL Server 7.0 database is backed up daily at midnight.One morning you load a large amount of data by using a nonlogged transaction.You then disable the selcet into/bulkcopy option.What is the easiest way to restore the validity of the transcaction log backup sequence?
A.Create a database backup.
B.Truncate the transaction log.
C.Back up the transaction log.
D.Create a differential database backup.
ANSWER: D
72) .You are planning the installation of SQL Server onto servers at various international offices in your company.You want to accomplish the following goals:
.Minimize the time required to complete the installations.
.Minimize network utilization during the installations.
.Minimize the possibility of errors during the installations.
.Maximize the compatiblility of the SQL Servers.
.Allow each international office to store characters that are specific to its region.
Which course of action will best accomplish your goals?
A. Instruct the server administrators at each international office to install each SQL Server by using the Typical installation option.
B. Instruct the server administrators at each international office to use the character set,sort order,and Unicode collation that are optimal for their region.
C. Create a standardized installation of SQL Server at your office.
Back up the installation to tape by using the Windows NT Backup utility.
Send each international office to a copy of the backup tape.and instruct the
Server administrators at each international office to restore the tape’s
contents onto theirserver
D. From you office,perform a remote installation of SQL Server for each international office ,using a standized set of installation options.
ANSWER: A
73). You are responsible for backups on the Sales database,The Order and Customer talbes are in the sales database.The following events occur
8:00 P.M Full database backup of the Sales database starts
8:10 P.M User begins a transaction.
User inserts Order 53 for Customer 10 into the Order table.
The name for Customer 10 is Laura Jennings.
8:11 P.M Backup of the Sales database is completed
8:12 P.M Users update the name for Customer 10 in the Customer talbe to
Amy Jones.
User commits transaction.
8:30 P.M Sales database becomes corrupted due to media failure.
8:40 P.M The backup of the Sales database from HO P.M is restored to the
Sales database
You query the Customer and Order tables in the restored Sale database.
What is the state of the data?
A. Order 53 exists in the Order table.
The name for Customer 10 in the Customer talbe is Laura Jennings.
B. Order 53 exists in the Order table.
The name for Customer 10 in the Customer talbe is Amy Jones.
C. Order 53 does not exists in the Order table.
The name for Customer 10 in the Customer talbe is Laura Jennings.
D. Order 53 does not exists in the Order table.
The name for Customer 10 in the Customer talbe is Laura Jennings.
ANSWER: C
74).You install merge replication.You are investigating the default conflict
resolution.You verity that the value of the ContactName field is Carolyn Seeley
for customer 10 in a merge article on both the Publisher and the Subscriber.
the value of ContactName for customer 10 on the Publisher is changed to Amy Jones. The value of ContactName for customer 10 on the Subscriber is changed to Jenny Sm.The Merge Agent runs.
What is the value of ContactName for customer 10 on the Publisher and the Subscriber after conflict resolution?
A. Amy Jones on both Publisher and the Subscriber.
B. Jenny Sax on both Publisher and the Subscriber
C. Amy Jones on both Publisher and Carolyn Seeley on the Subscriber.
D. Jenny Sax on both Publisher and Amy Jones on the Subscriber.
ANSWER: A
75) You want to examine the master database file settings in SQL Server Enterprise Manager, but the master database is not listed in the Database folder. You want to set the option to make the master database visible. Where can you set this option?
A. in the master database
B. in the server configuration
C. in the registry
D. in the Enterprise Manager registration
Aw: D
76). You want to configure SQL Server to notify you by e-mail when an alert occurs. Which series of steps must you perform?
A. Configure the SQL Server Agent service to log on as the local system account.
Log on to the server running SQL Server as your user account,and configure
e-mail connectity.
Configure the SQL Server Agent service to use the resulting e-mail profile.
B. Configure the SQL Server Agent service to log on as a domain user account.
Log on to the server running SQL Server as SQL Server agent account,and
Configure e-mail connectity.
Configure the SQL Server Agent service to use the resulting e-mail profile.
C. Configure the MSSQLServer service to log on as a domain user account.
Log on to the server running SQL Server as your user account,and configure
e-mail connectity.
Configure the SQL Server Agent service to use the resulting e-mail profile.
D. Configure the MSSQLServer service to log on as a domain user account.
Log on to the server running SQL Server as SQL Server account,and configure
e-mail connectity.
Configure the SQL Server Agent service to use the resulting e-mail profile.
ANSWER:B
77). You load a large amount of data in to your database.You notice that the select into/bulk copy option is still enabled and that your transaction log is almost full. You turn off the select into/bulk copy option.Which 2 additional steps should you take to ensurethe viability of future backups? (Choose 2)
A. Perform a full database backup.
B. Perform a transaction log backup and use default options.
C. Perform a differential database backup.
D. Turn on the trunc. log on the chkpt option.
E. Perform a transaction log backup and use the TRUNCATE_ONLY option.
ANSWER: A,E
78)。Your insurance company is purchased by Duluth Mutual Life. The new management wants a copy of your 120-GB Policy database. Your current procedure is to back up the Policy database nightly to a media set of two device families, named PolicyBackup1 and PolicyBackup2. Each device family contains three tapes. The server at Duluth Mutual Life is named Server1. There is adequate disk space on Server1 to store the database, but there is only one local tape drive. What should you do to restore the Policy backup to Server1 with minimal administrative workload?
A. Make a separate backup device, specifying only one of your tape drives.
B. Restore the tapes from the PolicyBackup1 and PolicyBackup2 media families by using the single tape drive on Server1. Load the tapes in order of sequence number.
C. Detach the Policy database from your server, and attach the database to Server1
D. Restore the tapes from the PolicyBackup1 and PolicyBackup2 media families to Server1 by executing the RESTORE VERIFYONLY statement with the UNLOAD option.
Answer: B
79). You are using DTS to load 500 MB of data from a flat file to a SQL Server table. Each row contains 6000 bytes. You have 100 MB of free disk space for the
transaction log.Which is the important options to set on the target database to minize possiblity that the transaction log will not have any disk space available?
A. autochk
B. trunc.log on chkpt
C. select into/bulk copy
D. autoclose
ANSWER: B
80) You are running a web sit, you want accounting who is accessing specfic tables,which should you use performance monitor to monitor?
A. Connect event, execution plan event, service control event, sql user name, NT user name.
B. Attention event, exec prepared sql event, sql username, NT user name.
C. Connect event, disconnect event, existingconnection event, NT user name
D. Object: opened event, sql user name, NT user name.
Answer: D
81) The departmental server has Full Text search implemented on it's SQL Server database. Currently both the Full Text database and the database files reside on the same logical drive. You do not want the Full Text indexing to use more than 25% of the available drive space because you want to allow space for dynamic expansion of the data files. You want to be able to remotely monitor the ammount of space in use by the catalogs. How should you remotly monitor the space usage with a minimum number of additional administrative steps?
A. Use the Windows NT Performance Monitor to connect to the Server and to monitor the size of the Microsoft Search catalog
B. Create a scheduled job to regulary monitor the size of the Full Text indexes and send the result to your E-Mail address.
C. Create a batch file to monitor the size of the Full Text indexes and send the result to you via Network Message. Use the Windows NT Scheduler Service to schedule the batch file to run regulary
D. Create a batch file to monitor the size of the Full Text indexes and send the result to you via Network Message. Create a scheduled job to run the batch file regulary
ANSWER：A
Notice:涉及到监视catalog 空间的工具，一般用NT Performance Monitor
82)。you load a lot of data into your database, then you find that the space for log is almost full,how can you do to ensure the restore :(choose all the applay)
A.make a full backup
B.make a differental backup
C.backup the transaction log with the default options
D.backup the transaction log with truncate_only
ANSWER: A,D
83) You need to provide access to the Sales database for internet users who do not have Window NT accounts. How can you provide access? (Choose all that apply)
A. Add a guest user account to the Sales database, and grant the guest user account the appropriate permissions in the Sales database.
B. Create a SQL Server login for internet guest access.
C. Add the Windows NT Domain users account to the SQL Server public role in the Sales database.
D. Grand the Windows NT Guest account access to SQL Server.
ANSWER:A,B
84) The transation log in a published database is full. You attempt to truncate the log, but you notice that the log remains full.What is the most likely cause?
A. The Microsoft repl-commands table in the distribution database is full.
B. The distribution Agent has stopped.
C. The Log Reader Agent has stopped.
D. Subscribed database has become unavailable
ANSWER: C
85) The engine manufacturing application records data about all the engines the are manufactured at your plant,. The database contains approximately 500 million records and is approximately 500GB in size. The database increases by 1,000 records per day. You want to accomplish the following goals;
1.Minimize the time required to recover the database
2.Provide the ability to recover the database to a specific time.
3.Minimize the number of transaction logs that need to be during recovery.
4.Minimize the time required to back up the database.
You take the following actions.
1.Schedule a full database back up of the entire. database to occur every Sunday at AM 1
2.Schedule transaction log back up to occur every day at P.M
Which result or results do these action produce? (Choose all the apply)
A. The time required to recover the database is minimized.
B. The database can be recovered to a specific time.
C. The number of transaction logs that need to be applied during recovery is minimized.
D. The time required to back up the database is minimized
ANSWER:B,D
Notice:有log备份，则可选备份时间最小。有差异备份则可选恢复时间最小。
86) You enable merge replication. What must you do to enable conflict resolution on the articles in your publications?
A. Add a column to each articles and assign the IDENTITY properly to each column.
B. Add a column of the timestamp data type to each article.
C. Add a column of the UNIQUEIDENTIFIER data type to each article.
D. Do nothing. SQL Server will add a column of the UNIQUEIDENTIFIER data type to each article.
ANSWER: D
87) Katrin is the system administrator of SQL Server and she is in the role db_owner of the corporate database. Carlos is in the roles of setupadmin and db_accessadmin. Julia is in the roles securityadmin and db_securityadmin. Katrin must ensure that only she can grant permissions in the database and that only Juliacan grant users and groups access to SQL Server and the corporate database?
How must role membership be changed to meet these requirements? (Chose two)
A. Add Julia to the serveradmin role.
B. Remove Carlos from db_accessadmin role.
C. Remove Julia from db_securityadmin role.
D. Add Julia to the db_accessadmin role.
E. Add Katrin to the db_securityadmin role.
F. Add Katrin to the db_accessadmin role.
G. Remove Carlos from the setupadmin role.
ANSWER: B,C
88) You have 2 tables, T1 & T2. You have G1 should be able to update T1, G2 should be only able to select from T1 and third group G3 able to select and update on T2. The question is how many application roles do you need?
ANSWER:3
89) Sales representatives use portables computers. They need to implement replication scenario so that the sales representatives can enter the orders. You want to minimise network traffic:
A.Transactional
B. Snapshot with pull
C. Snapshot with push
D. Merge
ANSWER: B
90) Computers A1 and A2 and connected by a high-speed T1 line. Also, Computers B1, B2 and B3 are connected to each other by a high-speed T1 line. There is a slow-link between A1 and B2. (To visualize this, you need to draw this scenario on a piece of paper. It came as an exhibit in the exam). You want to publish a price list on computer A1 to all the other computers. What replication type is the best for this situation ?
A. A1 as publisher/distributor. A2, B1, B2, and B3 are subscribers
B. A1 as publisher. B2 as distributor. A2, B1 and B3 as subscribers
C. A1 as publisher, B2 as distributors/publisher, A2, B1 and B3 as subscribers
D. A1 as publisher, A2 as distributor, B1, B2, B3 as subscribers
ANSWER: D.
91) You have the following backups for the sales database on salesbak device
Name Date File Seq. Type
Monday Data 7/7/99 8AM 1 Complete atabase
Monday Log 7/7/99 6PM 2 Tran.Log
Tuesday Data 8/7/99 8AM 3 Complete atabase
Tuesday Log 8/7/99 6PM 4 Tran.Log
You are asked to restore the latest backup. Which command can you use?
A. RESTORE DATABASE sales FROM salesbak WITH FILE=3, NORECOVERY
RESTORE LOG sales FROM salesback WITH FILE=4, RECOVERY
B. RESTORE DATABASE sales FROM salesbak WITH FILE=3, RECOVERY
RESTORE LOG sales FROM salesback WITH FILE=4, RECOVERY
C. RESTORE DATABASE sales FROM salesbak WITH FILE='Tuesday Data', RECOVERY
RESTORE LOG sales FROM salesback WITH FILE='Tuesday Log', RECOVERY
D. RESTORE DATABASE sales FROM salesbak WITH FILE='Tuesday Data', NORECOVERY
RESTORE LOG sales FROM salesback WITH FILE='Tuesday Log', RECOVERY
ANSWER: A
92) You have 2 applications. HR and Sales. In HR there are 2 types of users; those who only read and those who read and update data. For the Sales app, there are only 1 type of users; those who read and update. How will you set up your security ?
A. Create 2 application roles for HR and give appropriate rights to each role. Create 1 application role for Sales and give appropriate rights to the role
B. Create 1 application role for HR and assign appropriate rights. Create 1 application role for Sales
C. Create separate accounts for each user
ANSWER: A
93)You want exprot data from the temp tabel with bcp,but it does not work:
bcp #tmepdb..table out c:\tempfile.txt -n -Sserver -Uuser –Password
A. wrong syntax
B. the object file must be exist
C. you can’t do that on a temp table
D. you have used native format instead of character format
ANSWER: C
94) The disk driver containing your Master Database failed.Your User Databases are unaffected. You cannot located a recent master database backup. So you rebuild the master database. What should you do to ensure that your use databases are accessible in the shortest ammount of time?
A. Restore the user database form an existing backup.
B. Attach the existing user database to the new master database.
C. Manually edit the master database system catalog and add to the user database.
D. Do nothing. The process of rebuilding the master database automatically restores links to the user database.
ANSWER: B
95). You have 3 Tables - Table1, Table2, Table3
4 groups - group1a, group1b, group2a, group2b
all groups have select permissions on all tables
group1b should be the only group that can change and delete data in table1
group2b should be the only group that can change and delete data in table2 and table3
(Choose two)
A. add group1b to denydatawriter for table2 and table3; add group1b to datawriter for table1
B. add group2b to denydatawriter for table1; add group2b to datawriter for table2 and table3
C. give group1b update and delete permissions on table1
D. give group2b update and delete permissions on table2 and table3
ANSWER:C,D
96) During an upgrade of SQL Server 6.5 to SQL Server 7 ,a customer stored procedure in not upgraded.The procedure reports on database object constraints by referenceing the sysobjects, syscolumns, and sysconstraints tables.
What should you do to enable the stored procedure bo ne upgraded?
A. Rcompile the stored procedure on the version 7.0 server.
B. Rewrite the procedure to use the appropriate system tables.
C. Rewrite the procedure to use the appropriate information schema view.
D. Prior to upgrading the procedure,upgrade other databases referenced by the procedure.
ANWSER:C
97) You have two groups of user access Payroll and Sale databases. On the Payroll some uses only modify and some query. One group needs query and modify on Sales database. You want to security access to these databases. How do you configured?
A. Create two application roles on Payrolls and assign permissions to each application roles, create one application role on Sales and assign permission to the application role.
B. Create two application roles on Payroll and assign permission to each application role, create one application role on Sales and assign all user to this role.
ANWSER:A
98) Laura is the new sales coordinator for your company. She will process all orders. You want Laura to be able to modify the order database only through the order applications
.How should you control access to the order database?
A. Use a stored procedure the Laura has permission to execute
B. Use a User-defined application role that has UPDATE permission on the order database.
C. Use SQL Server user account that has UPDATE permission on the order database.
D. Use a view of the Order database in which Laura has UPDATE permission on the Order database.
ANWSER: B
99). You install merge replication. You are investigating the default conflict
resolution. name Contact Name field is CS for customer 10 in a merge article
on Publisher and Subscriber. The value of CN for customer 10 on Publisher is changed to AJ.
The value of CN for customer 10 on Subscriber is changed to JS. The merge agent runs.
What is the value of CN for customer 10 on Publisher and Subscriber after conflict resolution?
A. AJ on both.
B. JS on both.
C. AJ on Publisher and CS on Subscriber.
D. JS on Publisher and AJ on Subscriber.
ANWSER: A
NOTICE:This question can't be answered with the information supplied by book online. The subscriber could have a priority higher than that of the publisher in which case the subscriber would win the conflict. The publisher could have a priority higher than the subscriber in which case the publisher would win the conflict. They could have equal priority in which case the publisher would win the conflict.
100). Stephen tries to connect to the Sales database by using SQL Server Enterprise Manager. He receives the message "Unable to open default database '< ID> ' . " You notice that neither Stephen nor the guest account was granted access to the Sales database. What must you do to give only Stephen access with the minimum permissions?
A. Remove Stephen from the db_denydatareader and db_denydatawriter roles.
B. Add Stephen's login as a new user in the Sales database.
C. Change the default database in Stephen's Login properties dialog box.
D. Add the guest account to the Sales database in the Database users dialog box.
Answer: B
101) You want to delete an object generated by snapshot replication. Where do you find these objects?
A． mssql7/repldata on the distributor
B． mssql7/repldata on the subscriber
C， msrepl commands in the distributor
D． sysarticles
ANSWER: A
102). As the database owner,you grant Franz permissions to create views and creat stored procedures in the Finance database.
Franz creats a stored procedure that does an update on the dbo.Price table.He creates a view that selects price information for a report.He grants Suzanne SELECT permissions on the view and EXECUTE permissions on the stored procedure.
What must be done so that Suzanne can obtain the results by using the view and stored procedures?
A.You must grant Suzanne SELECT permissions on the view and the stored procedure.
B.Franz must grant Suzanne SELECT permissions on Price table.
C.You must grant SELECT and UPDATE permissions on Price table.
D.Franz must grant Suzanne EXECUTE permissions on the stored procedure and SELECT permissions on Price table.
ANSWER:C
103). You implement 10 scheduled jobs on your development server,and you verify that they function correctly. You now want to transfer the jobs to your production server.
How should yuo transfer the jobs with the fewest administrative steps?
A.Script the jobs, and execute the resulting script on the production server.
B.Back up the msdb database and restore it onto the production server.
C.Make the test server a master server,and make the production server a target server
Configure the jobs to run on the target server.
D.Manually re-create the jobs on the production server.
ANSWER:A
104). You are attempting to use the bcp utility to copy data from the #tmpcust temporary
table to the tmpcust.txt file.The name of the server is Svr and the password is
Pss0.You type the following instruction at the command line:
Bcp tempdb..#tmpcust out tmpcust.txt –n –SSvr –Usa Pss0
Your command fials.What is the most likely cause of the failure?
A. Temporary tables must be global in order to be copied by using the bcp utility
B. Temporary talbes cannot be copied by using the bcp utility
C. The tmpcust.txt file has not been created.
D. You used native format instead of character format
ANSWER:A
105). Maria is a member of the Windows NT HumanResource group.You need to deny database access to the HumanResource group,but you need to ensure that Maria has more than guest access.
You deny database access to the HumanResource group by adding the group to the db-denydatareader and db-denydatawriter roles.What must you do to give Maria the access she had before the HumanResource group was denied access?
A.Ensure that you add Maria's database access after you deny access to the HumanResource group
B.Add Maria as a user with a SQL Server login under Mixed Mode.
C.Remove Maria from the HumanResource group,and add her as a user.
D.Maria already has database access as a member of the guest user account and the public role
ANSWER :C
106) The Customer table in the Sales database resides on filegroup Sales_FG1.
The CustomerID index on the Customers table resides on filegroup Sales_FG2
The hard drive containing Sales_FG1 fails.You need to restore the filegroup
From the lastest backup.
What must you do to restore the Customers talbe in the shortest amount of time?
A. Restore the backup for Sales_FG1,and then load the backup for Sales_FG2.
B. Restore the backup for Sales_FG1,and then re-create the CustomerID index on Sales_FG2.
C. Backup the transaction log. Restore the backup of Sales_FG1 and Sales_FG2.Restore all transaction log backups made since the filegroup
Backups.
D. Backup the transaction log. Restore the backup of Sales_FG1,Restore all transaction log backups made since the filegroup backup.
ANSWER: C
107). You implement full-text indexing on 10 tables in your production database. You want to maintain the accuracy and performance of the full-text indexes, and you want to minimize overhead associated with their maintenance. What should you do?
A. Expand the database to accommodate future growth of the full-text indexes.
B. Backup the full-text indexes.
C. Schedule regular repopulations of the full-text indexes.
D. Schedule regular rebuilds of the full-text indexes.
E. Create triggers to automatically repopulate the corresponding full-text indexes whenever data modifications occur
ANSWER: C
108). Bruno’s NT accounts is in the marketing group, the marketing group’s access to the inventory database has been revoked. What should you do to give Bruno access to the inventory database ?
A. remove Bruno from the marketing group.
B. Create a new WINNT group ,and place Bruno in this group, along with anyone else who should get access.
C. Add Bruno as a user in the inventory database.
D. Create a sql server login for Bruno for the inventory database.
Answer: C
109) Maria preceeded you as administrator for Microsoft Windows NT Server and SQL Server. Maria created SQL Server logins and passwords matching users Windows NT accounts. When users logon to the Windows NT Server domain they obtain access to the SQL Server. Witch permissions do users receive to access SQL Server by using a trusted connection?
A. Only the same permissions as those that are granted to their SQL Server login
B. Only the same permissions as those that are granted to their Windows NT account
C. The cummulative permissions that are granted to their SQL Server login and their Windows NT account
D. Only the permissions that are granted in common to their SQL Server login and their Windows NT account
ANSWER:B
110). You want to ensure that the full-text indexes for a SQL Server 7.0 database are kept current.You want the full-text catalog to be updated soon after changes are made in the columns of talbes that are enabled for full-text serach;however,you want to minimize the impact on the performance of the SQL Server computer.Which of the following strategies should you implement?
A.Create triggers to start a job that will repopulate the full-text indexes each time that a change is made.
B.Schedule a job to regularly repopulate the full-text indexes.
C.Create triggers to rebuild the full-text indexes each time that a change is made.
D.Schedule a job to regularly rebuild the full-text indexes.
ANSWER: B
111)The trace you currently use is saved. You want to use similar traces frequently. How must you edit the saved trace you currently use to include additional events?
A. Import the trace in SQL Server Profiler, and edit the event classes.
B. Open the trace file, and edit the event classes.
C. Open the trace definition, and edit the event classes.
D. Edit the saved registry settings for the trace.
ANSWER: C
112) Antia Camen and Philippe will administrate different aspects of the sqlserver finance database, Currently they are all in the db_owner role, philippe must remain the db_owner. Antia must be only able to setup user accounts and grant the permission. Carmen must be only able to create table,views that are needed in the database. Which two changes should you make to need those require?
A. Remove Camen from db_owner role and add Camen to db_accessadmin role.
B. Remove Camen from db_owner role, and add her to the db_ddladmin role.
C. Add Antia to sysadmin role and to the db_accessadmin role.
D. Remove Antia from the db_owner role, and add her to only the db_sercurityadmin role.
E. Remove Antia from the db_owner role and add her to the db_sercurityadmin role and the db_accessadmin role.
Answer: B,E
113). Paulo is a member of NT marketing group with access to the marketing database, He is also a member of the WINNT manufacturing group with access to the products, paulo creates a table in the marketing database, paulo is then transferred to the manufacturing department. You want to remove paulo from the marketing group, what must you do so he can have access to the table he created?
A. You cannot remove him from the marketing group, because he is an object owner..
B. Grant him access under sql server mixed mode.
C. Grant him access to the marketing database.
D. Grant him access through the public role and the sql server guest user account.
Answer: C
114). In order to tighten security on SQL Server, you remove the Windows NT Administrators group from its default membership in the sysadmin role. You create a local group named SQLadmins and add it to the sysadmin role. You add your Windows NT account to SQLadmins. Now, the SQLServerAgent service will not start and your scheduled jobs will not run. What is the most likely cause of the problem?
A. The SQLServerAgent service does not have the Windows NT administrative privileges that are required in order for it to run.
B. The SQLServerAgent service does not have permission to connect to SQL Server.
C. The SQLServerAgent service account does not have the right to log on as a service.
D. The SQLServerAgent service account is disabled.
ANSWER: A
115).To make UNIX and Netware user access database like Windows user, (choose all apply)
A. create logins in SQL Server
B. add UNIX users to NT Logins
C. add UNIX and Netware users to WinNT groups
D. add UNIX and Netware users to server roles
E. ensure mixed authentication
ANSWER: A,D,E
116)You recently set up transactional replication,and you are now testing.
You update 100 rows in a published table.You verity that the changes were made on
The corresponding table on the Subscriber.
You look at the Msrepl_commands talbe in the distibution database,and you notice
That the table is empty.What is the most likely cause?
A. The Msrepl_commands talbe was automatically purged after transactions were received by the Subscriber.
B. The log Reader Agent purged the the Msrepl_commands table in preparation for another batch of replicated changed-transactions.
C. The replication distibution cleanup job ran and purged the Msrepl_commands table
D. The log Reader Agent did not log activity to the Msrepl_commands table
Answer: C
117) You are planning the installation of SQL Server onto 10 remote servers. The administrators of the remote servers do not have experience with SQL Server. You want to implement the installations so that the administrators participation Is minimized. You also want to ensure installations that are compatible with your existing SQL Server installation. How should you implement the installations?
A. Back up all SQL Server files and registry entries from your existing server to tape. Copy the backup tape, send copies of it to the remote administrators, and Instruct them to restore-the backup onto the remote servers.
B. Install SQL Server onto a similarly, configured test server at your location. Configure the SQL Server services to use the local system account, Use the resulting setup. iss file to automate the Installations on the remote servers.
C. Install SQL Server onto a similarly configured lost server at your location. Configure the SQL Server services to use the local administrator account, Use the resulting setup. iss file to automate the installations on the remote servers.
D. Install SQL Server onto a similarly configured test server at your location. Configure the SQL Server services to use the local administrator account. Use the resulting sqlstp. log file to automate the installations on the remote servers
Answer: B
118). You have installes Windows NT Server with the dafualts. You are going to have Windows 95 and Netware clients. What else must you do befor you install SQL server?
A Install Windows NT service pack 4
B Install Internet Explorer 4.01 with service pack 1
C Install gateway Service for Netware
D Install NWLink IPX/SPX
E Install TCP/IP
F Install the Option Pack
ANSWER: A,B,D
