Learn About Microsoft SQL Server 2008
By Certification Magazine —
1 | 2 | 3 | 4 |
You are the database administrator for your company. You are maintaining the SQL Server instance named Sql1 on the Sqlserver1 server. The Sql1 instance stores the Prod1 database. The Prod1 database is configured to use the full recovery model.
You are required to configure a secondary database for Prod1 to provide high availability of the database. You are required to initialize the secondary database from a full database backup of the Prod1 database and restore the logs from the online primary server to the secondary server. You do not want to bring the secondary database online immediately.
Which actions should you perform to initialize the secondary database from the primary database? (Choose two. Each correct answer represents part of the solution.)
A. Issue the RESTORE DATABASE statement with the WITH NORECOVERY clause.
B. Issue the RESTORE DATABASE statement with the WITH RECOVERY clause.
C. Issue the RESTORE LOG statement with the WITH NORECOVERY clause.
D. Issue the RESTORE LOG statement with the WITH RECOVERY clause.
E. Issue the RESTORE LOG statement with the WITH STANDBY clause.
Answer:
A, C
Tutorial:
You should perform the following steps to initialize a secondary database from a primary database without bringing the secondary database online immediately:
1. Issue the RESTORE DATABASE statement with the WITH NORECOVERY option.
2. Issue the RESTORE LOG statement with the WITH NORECOVERY option.
To initialize a secondary database from a primary database, you must first restore a full database backup of the primary database to the location where the secondary database will be stored. This is done by issuing the RESTORE DATABASE statement.
Specifying the WITH NORECOVERY clause with the statement will initialize the secondary database. The WITH NORECOVERY clause is used when you need to restore additional transaction logs on the database. Using the WITH RECOVERY clause with the RESTORE DATABASE statement will perform a recovery on the secondary database and bring it online immediately.
In this scenario, you are not required to bring the secondary database online immediately. Using the WITH NORECOVERY clause with the RESTORE LOG statement restores the transaction log. The WITH NORECOVERY clause should be used with the RESTORE LOG statement when additional transaction logs must be applied to the secondary database.
You should not issue the RESTORE DATABASE statement with the WITH RECOVERY clause. Using the WITH RECOVERY clause with the RESTORE DATABASE statement performs a recovery on the secondary database and brings the secondary database online immediately. In this scenario, you are not required to bring the secondary database online.
You should not issue the RESTORE LOG command with the WITH RECOVERY option. The WITH RECOVERY option with the RESTORE LOG command performs a recovery on the secondary database transaction log and brings the secondary database online immediately. The WITH RECOVERY option is used with the RESTORE LOG command when you want to apply the last transaction log to the secondary database and bring it online. In this scenario, you are not required to bring the secondary database online.
You should not issue the RESTORE LOG statement with the WITH STANDBY clause because this is not allowed for an online restore. For an online restore, you must use the RECOVERY or NORECOVERY clause. Using the WITH STANDBY clause leaves the database in read-only, standby mode.
References:
Objective: Monitor and troubleshoot SQL Server.
Sub-objective: Identify concurrency problems.
Multiple answer, multiple-choice
You are the database administrator for your company. You manage all the SQL Server 2008 databases. The Prod_details database is the main database accessed by the company's users. The head office receives data from other branches of the company. After the data is verified by employees of the audit department, the database is updated with this information.
Some employees in the audit department complain they cannot update data in certain tables. You suspect other database users are holding locks on these tables. You must identify the oldest transaction in the database and the SQL Server logins associated with the transaction. Which statements or functions should you use to obtain the desired results? (Choose two. Each answer represents a part of the solution.)
A. The DBCC OPENTRAN statement.
B. The DBCC ROWLOCK statement.
C. The USER_NAME function.
D. The SUSER_SNAME function.
E. The SUSER_SID function.
F. The USER_ID function.
Answer:
A, D
Tutorial:
You should use the DBCC OPENTRAN statement and the SUSER_SNAME function. The DBCC OPENTRAN statement is used to obtain the details of the oldest transaction in the database. This statement returns the security identification number (SID), the server process ID (SPID), the name and the start time of the transaction. The SID value returned can be used to obtain the SQL Server login associated with the transaction. The SUSER_SNAME function is used to obtain the SQL Server login associated with the SID. When you pass the SID returned by the DBCC OPENTRAN statement to the SUSER_SNAME function, you can obtain the SQL Server login associated with the oldest transaction in the database.
You should not use the DBCC ROWLOCK statement because this statement does not provide information about the oldest transaction in the database or the SQL Server log-in associated with the transaction. The DBCC ROWLOCK statement was valid in the earlier versions of the SQL Server but is not supported in SQL Server 2008.
You should not use the USER_NAME function because the USER_NAME function does not provide the information required in this scenario. The USER_NAME function returns the database user name associated with the user identification number passed as an argument.
You should not use the SUSER_SID function because this function does not provide the information required in this scenario. The SUSER_SID function returns the SID associated with the database user name passed as an argument.
You should not use the USER_ID function because this function does not provide information required in this scenario. The USER_ID function returns the user ID associated with the database user name passed as an argument.
References:
