70-028 Exam papers

Posted on 2001-07-11
Last Modified: 2008-02-01

I wish to appear for the MCDBA 70-028 test. Could someone send me some sample test papers for the same. And what are the best practices to clear the test. I want to appear ASAP.

Thanks in advance

Question by:pahmn
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 70

Expert Comment

by:Éric Moreau
ID: 6276691
Transcender exams are THE tool to use:

Expert Comment

ID: 6276773
The best guide for passing the SQL Server exam is a copy of the relevant Microsoft SQL Server course. If you can get a copy of these, sit down, work through the labs and if you're comfortable with the content then the exam should be easy.

I have consistantly found the course notes to be much more accurate and specific guide to the exam content than expensive books, which are quite often either way-off the mark in terms of content, or riddled with technical errors.

Author Comment

ID: 6278056
How much are the minimum passing marks?
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

LVL 70

Expert Comment

by:Éric Moreau
ID: 6278125

Author Comment

ID: 6279136
The total paper is for 1000 marks?
LVL 70

Expert Comment

by:Éric Moreau
ID: 6279147
Yes. All MS exams are on 1000.

Accepted Solution

indoria earned 25 total points
ID: 6289698
Herez some BrainDumps ..
Forwarding u ... Plz check them, I am not sure about give answers..but u will get an idea about questions.... Best of luck

BrainDumps No. 1:
<Question 1 >

Joe uses a Windows 95 computer and sometimes a UNIX computer to connect to SQL Server 7.0. You have created a login for Joe's NT user account and also a SQL Server login that has the same name and password as his NT username and password. If Joe uses a trusted connection to access the SQL server what are his effective permissions.

a. Cumulative of NT and SQL
b. NT overrides SQL Server
c. SQL Server overrides NT
d. SQL only


<Question 2> 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. SQLServerAgent has no rights for the network share
c. SQLAgentCmdExe does not have permissions to write on the network drive
d. The Alerter service is not running.


<Question 3> 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

ANS: A, B, D, E, F

<Question 4 > SQL server is configured with a Net -library with TCP/IP socket 1450 which is non standard port setting How to connect clients.
a) At the server use the Network utility to use port 1450.
b) At the server use the Client utility to use port 1450.
c) At the Client configure the Server Client utility to use port 1450.
d) At the Client modify TCP/IP services to associate to sqlserver on port1450.

Ans.: C

<Question 5 > Replication.

NYY1----T1-----------NY2) -----56kbps------------>( Seattle-----T1----SanFrancisco----T1---Los Angeles)

a.NY1=pub/dist NY1,Seattle,SF,LA as subscriber
b NY1=Pubs/dist SF=subs/dist/pubs NY2,Seattle,LA as subscriber
c.NY1 =pubs NY2=dist Seattle,SF,LA as Subs.
d.NY1=Pubs LA= dis something not workable.

Ans: B (In Transceder)

<Question 6> .Three Users ANITA ,CARMEN,PHILIPPE are all having Db owner roles currently for the FINANCE database. You want PHILIPPE to continue to manage the db as before, Anita to ONLY manage users and grant permissions for users, Carmen should be ONLY able to create Tables, views, indexes and other objects,

a.Remove Carmen from db_owner and add to db_ddladmin role.
b.Remove Anita from db_owner role and add to db_securityadmin and db_accessadmin roles.
c.Remove Anita from DB_owner role and add to Db_security only.
d.something else.

Ans.: A & B

<Question 7> 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

Ans.: B

<Question 8> You 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 corporate 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.

Ans.: D

<Question 9> 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.

Ans.: A

Ref: BOL - Index on: OLE DB Provider for Jet - Topics: Configuring Linked Servers & OLE DB Provider for Jet

<Question 10> Stephen tries to connect to the Sales database by using
Enterprise Manager. He receives the message "Unable to open default database <ID>". You notice that neither Stephen nor 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 account from the db_denydatareader and db_denydatawriter roles.
B. Add Stephen 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.

Ans.: B

<Question 11> You have setup snapshot replication between a publisher and multiple remote subscribers. You set up a distributor on a separate computer on your LAN. You suspect that the snapshot data has become corrupted and you
want to clear it out manually. From where should you clear the data?

A) from the source database on the publisher
B) from the target database on the subscriber
C) from the distribution database on the distributor
D) from the snapshot folder on the distributor

Ans: D

<Question 12) 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

Ans: B

<Question 13> Every night the backup fails. Which DBCC command could repair the Sales DB and check the data integrity and indices?


Ans: C [EXACT]

<Question 14> Reponse poor, to prevent queries from running long:
a) Use SQL profiler to filter on queries
b) Set the query governor to stop long queiries
c) Use perfmonitor to send alerts and kill
d) Decrease query wait option

Ans: B

<Question 15> A query which load data on Production Server runs. Transaction Log is approaching 500MB, less since 50% done. 3GB HD was free on the drive.
DB 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

Ans: A

<Question 16 > 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 T-SQL extended stored procedures in the querys to record trace informations on each query.
b) Use Performance Monitor to log the SQLServer: Acces Methods object
c) Filter in Profiler, maximum time
d) Filter in Profiler, minimum time

Ans: D (filter out all the queries that are not at least X seconds long)


<Question 16 > 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

Ans: D [EXACT]

<Question 17> Load a lot of data to Parts table in Inventory database, quickest way to ensure statistics reflect the changes made.

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

Ans: C (ref. Transcender)

<Question 18 > SQL1 and SQL2 are linked Servers. They have standard logins in both, you can connect both and SELECT on both, issue distributed query for SQL2 on SQL1, failed what is the reason
a) not executed in SQL2
b) login in SQL1 not mapped in SQL2
c) not use NT login
d) must directly connect to SQL2

Ans: B (Linked servers require mapped logins) [EXACT]

<Question 19> 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.

Ans.: C

<Question 20> 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

Ans: A

<Question 21> Web based SQL server with internet users needing to access order entry application to enter new orders and enquiry's. 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

Ans: C (ref. Transcender)

<Question 22 > 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

Ans: B

<Question 23> A user forgot his SQL Server password. You are a member of the SYSADMIN role. What is the easiest way for you to change his password to a new one?:

A. use the login properties page in the Enterprise Manager.
B. delete his login and add it again.
C. use sp_password

Ans: A

<Question 24> 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 SQL Server
Employee ID I Employee ID
Name I Name
Addr1 I Address
Addr2 I City
City I State
State I Zip
Zip I

What method should be used to accomplish the merge.

a. bcp

Ans: C

<Question 25> Your company's SQL server is in the same BDC as the server running Exchange Sever. The exchange server is performing poorly. What should you do to improve performance of the server running exchange while not  adversely 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

Ans: E

<Question 26> Exchange and SQL 7.0 are running on the same server. You notice the performance in exchange is degraded. The Min server memory, Maximum server memory and set working area are set as they were automatically in the installation. What you do to free memory for exchange.
A. increase memory allocated to the procedure cache option
B. Set working area to 1
C. Set working area to 0
D. Reduce Min server memory
E. Increase Min server memory

Ans: E

<Question 27> 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
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

Ans: A

<Question 28> 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.

Ans: D

<Question 29> You plan to give users in the Windows NT Sales group limited access to the Sales and Inventory databases. All access for data modifications and reporting will be through a Microsoft Excel Application. How should you set up access for the Sales group in both databases?
A. Create a single application role. Grant the Sales group permissions on that role in each database.
B. Create an application role for each database. Grant the Sales group permissions in each database.
C. Create a single application role, and grant it permissions in each database.
D. Create an application role in each database, and grant it the appropriate permissions.

Ans: D

<Question 30> To increase security, your company decides to prohibit Windows NT administrators 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 an a domain controller.  

Ans: C

<Question 31> The transaction 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.

Ans: C

<Question 32> You have filegroup1 on disk1 and filegroup2 on disk2. You have a cust table on fg1 and its index on disk2. Now the fg1 on disk1 fails. What is the quickest way to restore?
A. apply fg1 backup and fg2 backup
B. apply fg1 backup and rebuild indexes.
C. apply fg1 backup and fg2 backup. restore all the transaction logs since the backup.
D.a pply fg2 backup and apply all the transaction logs for the fg2.

Ans: C

<Question 33 > You have Disk1, Disk2, Disk3.. (C,D,E)
ON C you have program files, System database files, msdb, etc., D- Tran log and E- user database files There are no frequent updates.. but heavy query ..users report of slow
performance.. what do u do?
A. Create additional files for the trans.log an C
B. Create new filegroups on C and place heavily used indexes on the new filegroups.
C. Create new filegroups on D and place heavily used indexes on the new filegroups.
D. Place System Databases on drive E, put trans.log on Drive C

Ans: C

<Question 34> 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. DB 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 DB Admin?
A. Use SQL Profiler extended stored procedure and send them to the NT App log in the corporate office.
B. Use SQL Profiler extended stored procedure and send them to the table in the corporate office.
C. Use xp_sendmail procedure to send the statistics thru e-mail to the DB Admin.
D. Use OS commands to build a batch file, and send them to the corporate office.

Ans: C

<Question 35 > In Replication: you have finance db. 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.

Ans: C [EXACT]

<Question 36> 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 auto grow 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

Ans: B

<Question 37 > You have a SQL Server 6.5 application. You want developers, working on Win98, 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 ?
a. Install SQL 7.0 desktop on the developers machine and use replication to copy data from 6.5 to 7.0
b. Install SQL 7.0 desktop on the developers machine and use DTS to import the data.
c. Upgrade the OS to Windows NT Workstation and use the SQL Server Setup Upgrade Wizard

Ans: C

<Question 38> Eric needs to manage (TRAP!) 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

Ans: B (I don't think it is a trap. adding and removing user and group accounts is part of managing accounts. The word "Manage" is synonymous to "add and remove")

<Question 39 > 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

Ans: C [EXACT]

<Question 40> If an application generates an error you want to receive notifications. How can the Developers of the application implement this ?
a. Create a user defined SQL server using the SQLALRTR to log the error.
b. RAISERROR Statement
c. Use the NT Application Log to log the error
d. Minor error to occur which executes an alert triggered by resulting error

Ans: B

<Question 41> You are moving a server from FINANCE DOMAIN to MONEY domain. SQL Server is not able to start.
a.Money is a Datatype in SQL. so it cannot be restarted.
b.SQL Server NT account is inaccessible.
c.Internal SQL server name is now different than the network name.
d.Domain name cannot be changed after installing SQL.

Ans: B

<Question 42> Browsing of other servers from an SQL server. running on Win98 is not possible Browse list is not available.. When trying to register they are not able to do it. But they are able to connect for File sharing.
A. verify the Win98 machines are connected to the same sublet.
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

Ans: D

<Question 43> 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.

Ans: A

<Question 44> In order to tighten Security on a SQL Server, remove NT Admin from default Membership. Create on NT-Server Local SQLadmin Group and give him accounts, add these to Sysadmin Role on SQL Server. Server Agent service
will not start and scheduled jobs will not run.
a, SQL Agent have not NT admin privileges
b. SQL Agent have no permission to connect SQL
c. SQL account does not have the right to log on as a services
d. The SQL Server account is disabled

Ans: A

<Question45> Transaction replication, updates synchronized on Publisher and Subscriber, MSrepl_commands is empty, why:
a) automatically cleaned up after transaction
b) log reader purged it for next batch
c) replication distribution clean up job ran and purged it
d) log reader did not log activity to it

Ans: C
<Question 46> Installation from 10 Remote SQL Server. Remote Sysadmin has no experience with SQL Server. You want to ensure that the installation of the remote SQL Server was compatible to the existing.
a) Backup all SQL files and registries to atape and send a copy of the tape to the remote admin
b) Install the SQL Server on a similar file and use the sql70st.log file for the remote installation
c) Install the SQL Server on a similar system, use the local system account and use the sql70.iss file for remote inst.
d) Install the SQL Server on a similar system, use the admin system account and use the sql70.iss file for remote inst.

Ans: C

<Question 47> You create a full backup and three log backups every day. Full backup at 7am Log backup at 10am
Log backup at 1pm  Log backup at 3pm You DB gets corrupted after one of the employees by mistake updates one of
the columns in the DB at 2:30 pm. How can you solve this problem?
a) Restore the full backup of 7am with NORECOVERY option
Restore the Log backup of 10am with NORECOVERY option
Restore the Log backup of 1am with NORECOVERY option
Restore the Log backup of 3am with RECOVERY option, STOPAT 2:29pm
b) Restore the full backup of 7am with NORECOVERY option
Restore the Log backup of 10am with NORECOVERY option
Restore the Log backup of 1am with NORECOVERY option
Restore the Log backup of 3am with NORECOVERY option, STOPAT 2:29pm

Ans: A (Not exactly but similar)

<Question 48 > you have select into/bulk copy set to on. You are making modifications. You decide to turn off select into/bulk copy. What are the things you need to do to ensure viability of future backups.
a) full backup
b) transaction log backup with the default options
c) differential backup
d) transaction log backup with the truncate only statement

Ans: A, B

BrainDumps No 2 :
Test Name: SQL 7.0 Administration Test # 070-028

Very tricky questions. This is the first exam I've failed. 680 needed to pass, got a 660. Had to wait over 2 hours for the exam to be uploaded. Second time this happened for this exam (first time the exam never came through). It's VERY difficult. All the questions are LONG. You MUST take time to read each question very closely. They throw a lot of screwballs and try to trip you up.

Anyway, here's the load-down:

Installation, probably 4 questions:

* Upgrading = what order to upgrade machines that are involved in replication (always do distributor first...after that it doesn't matter what order). Upgrading from SQL 4.2 (must upgrade to 6.x then 7...Not directly) or from win98 running SQL.

* Unattended = during installation a setup.iss file is created storing all settings you've chosen during installation.

* Custom vs. Typical = what options are selected when you choose typical. When should you choose custom, etc.

Planning, 2 questions:

* System requirements (RAM, HDD, CPU speed)

Configuring and Managing Security, TON of questions, probably close to 20 that included something related to security:

* Know the pre-assigned rights for the build in roles (fixed server and fixed database)

* Know in detail what happens when a user creates objects, what happens when users are added to the server, what happens when they are dropped, etc.

* I was heavily tested on this both times I took the exam.

* Had several questions on application roles (probably 3 questions).

* Linked servers.

Managing and Maintaining data, about 10-15 questions:

* Know what utilities are used to import and export data, how they are used, syntax (BCP), and in what situations to use which utilities.

* Must know the implications of using each utility (how it effects the transaction log, performance, etc).

Monitoring and Optimization, about 5 questions:

* They ask very detailed questions about what counters to use in Performance a couple curveballs (watch out here, this was slippery ice!).

* Server Profiler, couple questions = importing stored traces, how and when to use profiler.

* Query governor

* The classic question with Exchange and SQL running on the same machine. Answer is to decrease max memory. Troubleshooting, probably about 5 questions.

BTW: 1/2 of the stuff needed to pass isn't even mentioned in the MOC (Microsoft Official Curriculum) books. I used the MOC, BOL (Books On-Line), Sybex (was a pretty good book!), experience, and it still wasn't enough. STUDY HARD and GOOD LUCK! Make sure you know it inside and out!


Author Comment

ID: 6313662

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question