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.
Explanation:
You should perform the following steps to initialize a secondary database from a primary database without bringing the secondary database online immediately:
Issue the RESTORE DATABASEstatement with the WITH NORECOVERY option.
Issue the RESTORE LOGstatement with the WITH NORECOVERY option.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.) gfedcb Issue the RESTORE DATABASE statement with the WITH NORECOVERY clause.
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. Objective: Maintaining a SQL Server Database Sub-Objective: Restore databases. References: TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > Product Documentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQL Reference > RESTORE Statements for Restoring, Recovering, and Managing Backups (Transact-SQL) > RESTORE Arguments (Transact-SQL) TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > Product Documentation > SQL Server 2008 Books Online > Database Engine > Operations > Administration >Administration: How-to Topics >Backing Up and Restoring How-to Topics > How to: Apply a Transaction Log Backup (Transact-SQL)