SQL Server 2005 - Exam
.JPG "SQL Server 2005 - Exam - Quiz")
Please note that you have to give answers before going to next question and you can not go back.
There are 25 Questions. Read Questions carefully before giving answers.
- 1.
You configure a new SQL Server 2005 computer to use TCP/IP with all default settings. Your corporate policy requires that each server use a firewall. You find that you can connect to the SQL Server instance from the local computer. However, client computers cannot connect to the SQL Server instance. You need to identify the most likely cause of the connection issues. What should you do first?
- A.
Ensure that port 443 is open in your firewall.
- B.
Ensure that port 1443 is open in your firewall.
- C.
Ensure that client computers connect by using Shared Memory protocol.
- D.
Ensure that the server is not paused.
Correct Answer
B. Ensure that port 1443 is open in your firewall.Explanation
The correct answer is to ensure that port 1443 is open in your firewall. By default, SQL Server 2005 uses port 1433 for TCP/IP connections. However, if the SQL Server instance is configured to use a dynamic port, it will select a different port number. In this scenario, it is important to ensure that the firewall allows incoming connections on the correct port, which in this case is port 1443.Rate this question:
-
- 2.
.You are preparing for a new installation of SQL Server 2005. You need to select the protocols that client computers might use to connect to the server. Which two protocols can you use to achieve this goal? Choose two.)
- A.
Named Pipes
- B.
TCP IP
- C.
Shared Memory
- D.
Multiprotocol
Correct Answer(s)
A. Named Pipes
B. TCP IPExplanation
You can use Named Pipes and TCP/IP protocols to allow client computers to connect to the SQL Server 2005. Named Pipes is a protocol that allows communication between processes on the same computer or between computers on the same network. TCP/IP is a protocol that allows communication over a network, making it suitable for client computers connecting to the server. Shared Memory, on the other hand, is a protocol that allows communication between processes on the same computer only. Multiprotocol is not a valid protocol option for connecting client computers to the server.Rate this question:
-
- 3.
You are moving a SQL Server 2005 database named Timesheet to a new database server. The Timesheet database is used by the TimeEntry application. You need to minimize the amount of time that the TimeEntry application is unavailable while you move the database. What should you do?
- A.
Move the database to the new server by using the SQL Management Object method in the Copy Database Wizard.
- B.
Detach the current database. Copy the data files to the new server. Attach the files.
- C.
Back up the database. Copy the backup file to the new server. Restore the database.
- D.
Move the data files. Specify the new location by using ALTER DATABASE.
Correct Answer
B. Detach the current database. Copy the data files to the new server. Attach the files. -
- 4.
A support engineer reports that inserting new sales transactions in a SQL Server 2005 database results in an error. You investigate the error. You discover that in one of the databases, a developer has accidentally deleted some data in a table that is critical for transaction processing. The database uses the full recovery model. You need to restore the table. You need to achieve this goal without affecting the availability of other data in the database. What should you do?
- A.
Back up the current transaction log. Restore the database with a different name and stop at the point just before the data loss. Copy the table back into the original database.
- B.
Back up the current transaction log. Restore the database to the point just before the data loss.
- C.
Restore the database from the existing backup files to a time just before the data loss.
- D.
Restore the database to the point of the last full backup.
Correct Answer
A. Back up the current transaction log. Restore the database with a different name and stop at the point just before the data loss. Copy the table back into the original database.Explanation
To restore the deleted table without affecting the availability of other data in the database, the support engineer should first back up the current transaction log. Then, they should restore the database with a different name and stop at the point just before the data loss. This will allow them to access the database as it was before the data was deleted. Finally, they can copy the table back into the original database, ensuring that the critical data is restored without impacting the availability of other data in the database.Rate this question:
-
- 5.
You are responsible for implementing maintenance jobs on a SQL Server 2005 database server. Certain jobs run every Sunday and other jobs run at the beginning of every month. You need to schedule the jobs in the way that uses the least amount of administrative effort. What should you do?
- A.
Create a job schedule that runs every Sunday. Assign weekly tasks to this schedule. Create a second schedule that runs on the first day of every month. Assign monthly tasks to this schedule.
- B.
Create a job for each task that runs once a day. Use a TransactSQL statement to check the date and day of the week. If the day is either a Sunday or the first day of the month, execute the code.
- C.
Create a job schedule that runs once a day. Assign jobs to this job schedule. If the day is either a Sunday or the first day of the month, execute the jobs.
- D.
Create a job for each task that runs once a week on Sunday. Add a second job schedule that runs the job on the first of the month.
Correct Answer
A. Create a job schedule that runs every Sunday. Assign weekly tasks to this schedule. Create a second schedule that runs on the first day of every month. Assign monthly tasks to this schedule.Explanation
The most efficient way to schedule the maintenance jobs is to create two separate schedules. One schedule should run every Sunday and should be assigned to the weekly tasks. The other schedule should run on the first day of every month and should be assigned to the monthly tasks. This approach ensures that the jobs are executed at the appropriate times without the need for additional logic or checks in the job code.Rate this question:
-
- 6.
Company.com has multiple servers in a distributed environment. You work with two SQL Server 2005 computers named SQL1 and SQL2. Each server uses SQL Server Authentication and they use different logins. You need to write a distributed query that joins the data on SQL1 with the data on SQL2. What should you do?
- A.
Ensure that both SQL1 and SQL2 use the same login name as the security context for each server.
- B.
Configure SQL2 as a remote server. Write the query on SQL1.
- C.
Configure SQL2 as a linked server to impersonate the remote login.
- D.
Configure SQL2 as a distributed server. Use pass-through authentication.
Correct Answer
C. Configure SQL2 as a linked server to impersonate the remote login.Explanation
To join the data on SQL1 with the data on SQL2 in a distributed environment, you need to configure SQL2 as a linked server. By doing this, you can impersonate the remote login on SQL2, allowing you to access and query the data on both servers. This will enable you to write the query on SQL1 and retrieve the necessary data from SQL2.Rate this question:
-
- 7.
A power failure occurs on the storage area network (SAN) where your SQL Server 2005 database server is located. You need to check the allocation as well as the structural and logical integrity of all databases, including their system catalogs. What should you do?
- A.
Execute DBCC CHECKFILEGROUP for each filegroup.
- B.
Execute DBCC CHECKCATALOG.
- C.
Execute DBCC CHECKDB.
- D.
Execute DBCC CHECKTABLE for each table.
Correct Answer
C. Execute DBCC CHECKDB.Explanation
Executing DBCC CHECKDB is the correct answer because it checks the allocation, structural, and logical integrity of all databases, including their system catalogs. This command will scan the entire database and perform various checks on the physical and logical consistency of the database. It will check the integrity of the data pages, indexes, and system tables, ensuring that there are no corruption or allocation issues. By running this command, you can identify and fix any potential problems caused by the power failure on the SAN.Rate this question:
-
- 8.
9. You use a SQL Server 2005 database named DB1, which is located on a server named SQL1. DB1 is in use 24 hours a day, 7 days a week. A recent copy of DB1 exists on a second server named SQLtest that also runs SQL Server 2005. You detect a high number of full scans on SQL1 and conclude that additional indexes in DB1 are needed. A workload file that is suitable for Database Engine Tuning Advisor (DTA) already exists. You need to analyze the workload file by using DTA. You must ensure maximum performance on SQL1 during analysis. You must also ensure availability during the implementation of any recommendations suggested by the DTA. What should you do?
- A.
Store the workload file on SQL1. Start DTA on SQLtest and connect to SQL1. Specify all workload and tuning options as necessary. In the Advanced Tuning Options dialog box, select the Generate only online recommendations check box.
- B.
Store the workload file on SQLtest. Start DTA on SQLtest and connect to SQLtest. Specify all workload and tuning options as necessary. In the Advanced Tuning Options dialog box, select the Generate only online recommendations check box.
- C.
Store the workload file on SQL1. Start DTA on SQL1 and connect to SQL1. Specify all workload and tuning options as necessary. In the Advanced Tuning Options dialog box, select the All recommendations are offline check box.
- D.
Store the workload file on SQLtest. Start DTA on SQLtest and connect to SQLtest. Specify all workload and tuning options as necessary. In the Advanced Tuning Options dialog box, select the All recommendations are offline check box
Correct Answer
B. Store the workload file on SQLtest. Start DTA on SQLtest and connect to SQLtest. Specify all workload and tuning options as necessary. In the Advanced Tuning Options dialog box, select the Generate only online recommendations check box.Explanation
Storing the workload file on SQLtest and running DTA on SQLtest will ensure that the analysis does not impact the performance of SQL1. By selecting the "Generate only online recommendations" option, DTA will only suggest recommendations that can be implemented without taking the database offline, ensuring availability during the implementation of the recommendations.Rate this question:
-
- 9.
A new employee needs access to a SQL Server 2005 database that is located on a server named SQL1. You create a login named ajones by using the following Transact-SQL statement. CREATE LOGIN ajones WITH PASSWORD = 'SQLServer$1' The new employee reports that when he logs in, he receives the following error message: "Login failed. The user is not associated with a trusted SQL Server connection." You need to resolve the error and allow the new employee to gain access to SQL1. What should you do?
- A.
Change the SQL Server security mode from Windows Authentication mode to SQL Server and Windows Authentication mode.
- B.
Change the SQL Server security mode from SQL Server and Windows Authentication mode to Windows Authentication mode.
- C.
Ensure that the login name is created with square brackets ([]).
- D.
Give the login access to a specific database by using the CREATE USER Transact-SQL statement.
Correct Answer
A. Change the SQL Server security mode from Windows Authentication mode to SQL Server and Windows Authentication mode.Explanation
The error message indicates that the new employee is trying to log in using a trusted connection, but the login created for them is not associated with a trusted connection. To resolve this, the SQL Server security mode needs to be changed from Windows Authentication mode to SQL Server and Windows Authentication mode. This will allow the new employee to log in using either a trusted connection or a SQL Server login.Rate this question:
-
- 10.
You manage a SQL Server 2005 computer that was installed using default settings. After a power failure, the SQL Server (MSSQLSERVER) service on your database server does not start. You need to find out the cause of the problem. Which three actions should you perform? (Each correct answer presents part of the solution. Choose three.)
- A.
In Event Viewer, view the system log.
- B.
In Event Viewer, view the application log.
- C.
In Notepad, view the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ErrorLog.1 file.
- D.
In Notepad, view the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ErrorLog file.
- E.
In Notepad, view the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLAgent.out file.
Correct Answer(s)
A. In Event Viewer, view the system log.
B. In Event Viewer, view the application log.
D. In Notepad, view the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ErrorLog file.Explanation
To troubleshoot the issue of the SQL Server service not starting after a power failure, it is recommended to perform the following actions:
1. In Event Viewer, view the system log: This will help identify any system-level errors or events that might have occurred during the power failure and could be preventing the SQL Server service from starting.
2. In Event Viewer, view the application log: This will provide insights into any application-level errors or events related to SQL Server that could be causing the service startup issue.
3. In Notepad, view the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ErrorLog file: This log file contains detailed information about SQL Server errors and can provide clues about the specific error preventing the service from starting.
By performing these three actions, you can gather information from both the system and application logs, as well as the SQL Server error log, to identify the cause of the problem and take appropriate steps to resolve it.Rate this question:
-
- 11.
You are responsible for managing a SQL Server 2005 database. This database is updated with data that is exported from another site. On a monthly basis, data is loaded into the database from a flat file by using the bcp utility. This operation should be minimally logged. However, users report that they receive error messages indicating that the transaction log is full. You investigate and discover that the errors occur when the data is being imported. You need to prevent this problem from occurring. What should you do?
- A.
Disable the clustered index and all nonclustered indexes on the target table.
- B.
Set the database recovery model to full.
- C.
Set the database recovery model to bulk-logged.
- D.
Use the BULK INSERT statement to load the data.
Correct Answer
C. Set the database recovery model to bulk-logged.Explanation
Setting the database recovery model to bulk-logged is the correct answer because when the recovery model is set to bulk-logged, the transaction log will not record every individual change made during the data import process. Instead, it will only record the minimal information needed to recover the database in case of a failure. This will prevent the transaction log from filling up and causing errors.Rate this question:
-
- 12.
Company.com has a server named SQL1 that is dedicated to SQL Server 2005. SQL1 is configured for SQL Server and Windows Authentication mode. Using Windows Authentication, you have sysadmin privileges on SQL1. All users who work with SQL1 report that all applications that use SQL1 stop responding. Your supervisor asks you to investigate the problem. In Task Manager, you notice that CPU usage is at 100 percent. You try to connect to SQL1 by using SQL Server Management Studio. SQL1 does not respond. You need to connect to SQL1 to diagnose the problem. What are two possible ways to achieve this goal? (Each correct answer presents a complete solution. Choose two.)
- A.
Connect to SQL1 by using the osql command-line utility.
- B.
Connect to SQL1 by using the sqlcmd command-line utility using a dedicated administrator connection.
- C.
Connect to SQL1 by using SQL Server Management Studio. Open a query window and execute the following command.DBCC TRACEON 1224
- D.
In SQL Server Management Studio, on the toolbar, click Database Engine Query to open the Connect to Database dialog box. In the Server name text box, type ADMIN:SQL1. Select SQL Server Authentication and provide your sa credentials. Click the Connect button.
Correct Answer(s)
B. Connect to SQL1 by using the sqlcmd command-line utility using a dedicated administrator connection.
D. In SQL Server Management Studio, on the toolbar, click Database Engine Query to open the Connect to Database dialog box. In the Server name text box, type ADMIN:SQL1. Select SQL Server Authentication and provide your sa credentials. Click the Connect button.Explanation
To diagnose the problem with SQL1, you can connect to it using the sqlcmd command-line utility with a dedicated administrator connection. This will allow you to access the server even if it is unresponsive. Additionally, you can connect to SQL1 using SQL Server Management Studio by opening a query window and executing the command "DBCC TRACEON 1224". This command enables a trace flag that can help diagnose the issue.Rate this question:
-
- 13.
During the design process for a new production installation of SQL Server 2005, you determine that you need to partition the database. Which edition supports data partitioning?
- A.
SQL Server Express Edition
- B.
SQL Server Enterprise Edition
- C.
SQL Server Workgroup Edition
- D.
SQL Server Standard Edition
Correct Answer
B. SQL Server Enterprise EditionExplanation
SQL Server Enterprise Edition supports data partitioning. Data partitioning is a feature that allows large tables to be divided into smaller, more manageable pieces called partitions. This can improve query performance and simplify administration tasks. The other editions of SQL Server mentioned in the options (Express Edition, Workgroup Edition, and Standard Edition) do not support data partitioning.Rate this question:
-
- 14.
As a database developer, you need to create an application that will be downloaded from the Internet. The application requires a database to store application data. Which SQL Server edition can you use for your application that does not require application users to purchase a license for SQL Server 2005?
- A.
SQL Server Express Edition
- B.
SQL Server Workgroup Edition
- C.
SQL Server Developer Edition
- D.
SQL Server Standard Edition
Correct Answer
A. SQL Server Express EditionExplanation
The SQL Server Express Edition is the correct answer because it is a free, lightweight version of SQL Server that is designed for small-scale applications. It does not require users to purchase a license for SQL Server 2005, making it an ideal choice for the application in question. The Workgroup Edition, Developer Edition, and Standard Edition all require a license and would not be suitable for this scenario.Rate this question:
-
- 15.
Which authentication mode lets you use both SQL Server logins and Windows logins?
- A.
Kerebos authentication
- B.
Windows
- C.
Mixed Mode
- D.
Network Service account
Correct Answer
C. Mixed ModeExplanation
Mixed Mode authentication allows you to use both SQL Server logins and Windows logins. With Mixed Mode, you can choose to authenticate using either a SQL Server login, which is a username and password stored within the SQL Server database, or a Windows login, which is a login associated with a Windows user account. This provides flexibility in authentication options for users accessing the SQL Server database.Rate this question:
-
- 16.
Which SQL Server 2005 services require that you install them with their own account? (Choose all that apply.)
- A.
SQL Server Agent
- B.
Log Reader Agent
- C.
SQL Server
- D.
.Replication Agent
Correct Answer(s)
A. SQL Server Agent
C. SQL ServerExplanation
The SQL Server Agent and SQL Server services require that you install them with their own account. This is because these services need to have separate permissions and security settings in order to function properly. By installing them with their own account, you can ensure that they have the necessary privileges to perform their tasks effectively without compromising the security of the system. On the other hand, the Log Reader Agent and Replication Agent do not require separate accounts for installation.Rate this question:
-
- 17.
Which upgrade data-movement method requires that users not be accessing the database you want to upgrade?
- A.
Copy Database Wizard
- B.
Backup/restore
- C.
Manual scripting
- D.
None of the above
Correct Answer
D. None of the aboveExplanation
All of the options mentioned in the question do not require users to stop accessing the database during the upgrade process. The Copy Database Wizard, Backup/Restore, and Manual Scripting methods can all be performed while users are still accessing the database. Therefore, the correct answer is None of the above.Rate this question:
-
- 18.
Which of the following is a prerequisite for Database Mail?
- A.
Service Broker
- B.
Database Mirroring
- C.
Extended MAPI Profile
- D.
Microsoft Exchange Server
Correct Answer
A. Service BrokerExplanation
A prerequisite for Database Mail is Service Broker. Service Broker is a messaging framework in SQL Server that allows different components to communicate asynchronously. Database Mail relies on Service Broker to send email messages from the database. Without Service Broker, Database Mail would not be able to function properly and send emails. Therefore, Service Broker is a necessary requirement for setting up and using Database Mail.Rate this question:
-
- 19.
Which of the following sentences is true for authentication mechanisms when the SMTP server is being accessed?
- A.
Database Mail accesses the SMTP server using the SQL Server Agent service credentials by default.
- B.
Database Mail accesses the SMTP server using the SQL Browser service credentials by default.
- C.
Database Mail accesses the SMTP server using the database engine service credentials by default.
- D.
Database Mail accesses the SMTP server using the SQL Server Active Directory Helper service credentials by default.
Correct Answer
C. Database Mail accesses the SMTP server using the database engine service credentials by default. -
- 20.
Which of the following sentences is true for recovery models?
- A.
In the Full recovery model, most transactions are minimally logged
- B.
In the Simple recovery model, most transactions are minimally logged.
- C.
In the Bulk-Logged recovery model, all transactions are logged.
- D.
In the Simple recovery model, all transactions are logged.
Correct Answer
B. In the Simple recovery model, most transactions are minimally logged.Explanation
In the Simple recovery model, most transactions are minimally logged. This means that only the information necessary for recovering the database to a consistent state in the event of a failure is logged. This model is suitable for databases where data loss is acceptable and the ability to restore to a specific point in time is not required.Rate this question:
-
- 21.
Which of the following restore operations are NOT allowed in the Simple recovery model? (Choose two.)
- A.
Differential
- B.
Point-in-Time Restore
- C.
Full
- D.
Page Restore
Correct Answer(s)
B. Point-in-Time Restore
D. Page RestoreExplanation
In the Simple recovery model, only Full and Differential restore operations are allowed. Point-in-Time Restore and Page Restore are not allowed in this recovery model.Rate this question:
-
- 22.
When do you need to specify an external data source by using a linked server?
- A.
When you need to access a different database.
- B.
When you need to access a different instance.
- C.
When you need to access a different database schema
- D.
When you need to access objects of a different user owner.
Correct Answer
B. When you need to access a different instance.Explanation
When you need to access a different instance, you need to specify an external data source by using a linked server. Linked servers allow you to connect to and access data from different instances of SQL Server, even if they are located on different servers or have different versions. By creating a linked server, you can access and query data from the remote instance as if it were a local database. This is useful when you need to retrieve or manipulate data from multiple instances or when you need to integrate data from different sources into a single query or report.Rate this question:
-
- 23.
You work as the database administrator at Hi-tech Company. The Hi-tech Company network contains a SQL Server 2005 database server named Server-DB01 that runs on a Windows Server 2003 computer. Server-DB01 hosts a database named DB_Orders. Several data capturers enter data into the DB_Orders database every day. The Simple Recovery Model is implemented for the DB_Orders database. A Full backup of the DB_Orders database is performed every Saturday at 2:00 P.M. You are concerned that data loss may occur should Server-DB01 suffer a hard disk failure. You decide to implement additional backups of the DB_Orders database on a daily basis. You want to minimize the amount of time required for the backups as well as the time required restoring the database. What should you do?
- A.
Perform differential backups of the DB_Orders database on every week day.
- B.
Switch the DB_Orders database to the Full Recovery Model.
- C.
Perform a Full backup of the DB_Orders database on every week day.
- D.
Perform incremental backups of the DB_Orders database on every week day.
Correct Answer
A. Perform differential backups of the DB_Orders database on every week day.Explanation
Performing differential backups of the DB_Orders database on every weekday would be the best approach in this scenario. Since the Simple Recovery Model is implemented, performing a full backup every weekday would not be efficient as it would take longer to complete and restore. Switching to the Full Recovery Model would require more frequent transaction log backups and could increase the overall complexity and time required for backups and restores. Incremental backups would also require more time and effort for restores. Differential backups, on the other hand, only back up the changes made since the last full backup, reducing the backup time and simplifying the restore process.Rate this question:
-
- 24.
How many default instances can you install on a single SQL Server server?
- A.
1
- B.
2
- C.
3
- D.
5
Correct Answer
A. 1Explanation
Only one default instance can be installed on a single SQL Server server. A default instance is the first instance of SQL Server that is installed on a server without specifying an instance name. It listens on the default port number and does not require specifying the instance name when connecting to it.Rate this question:
-
- 25.
10. You are working as a DBA at the Cape Town office of Company.com. Company.com use a SQL Server 2005 database that does not contain any views. You use Database Engine Tuning Advisor (DTA) to tune this database. A workload file that is suitable for DTA already exists. You are required to locate only missing nonclustered indexes. During this process, you need to insure that existing structures remain intact, and that newly recommend structures msut not be partitioned . You want to accomplish this goal by configuring the tuning options in DTA. Which tuning options should you use?(Select one answer in each section) Total answers will be three.
- A.
Indexed Views
- B.
Indexes
- C.
Non culstered Indexes
- D.
Partitioning
- E.
No Partitioning
- F.
Do not keep all Existing PDS
- G.
Keep all Existing PDS
- H.
Keep Indexes only
Correct Answer(s)
C. Non culstered Indexes
E. No Partitioning
G. Keep all Existing PDSExplanation
The correct answer suggests using the tuning options of "Nonclustered Indexes" to locate missing nonclustered indexes, "No Partitioning" to ensure that newly recommended structures are not partitioned, and "Keep all Existing PDS" to ensure that existing structures remain intact. This combination of options will allow the DBA to tune the SQL Server 2005 database using DTA without altering the existing structures and without partitioning the newly recommended structures.Rate this question:
-
Quiz Review Timeline +
Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.
-
Current Version
-
Mar 21, 2023Quiz Edited by
ProProfs Editorial Team -
Aug 12, 2011Quiz Created by
Ashokbsodha
Back to top