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 touse 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 restorethe logs from the online primary server to the secondary server.
You do not want to bring the secondary databaseonline 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 withoutbringing 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 theprimary 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 initializethe secondary database. The WITH NORECOVERY clause is used when you need to restore additionaltransaction 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, youare not required to bring the secondary database online immediately. Using the WITH NORECOVERY clausewith the RESTORE LOG statement restores the transaction log. The WITH NORECOVERY clause should beused with the RESTORE LOG statement when additional transaction logs must be applied to the secondarydatabase. You should not issue the RESTORE DATABASE statement with the WITH RECOVERY clause. Using the WITHRECOVERY 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 thesecondary 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 andbrings the secondary database online immediately. The WITH RECOVERY option is used with the RESTORELOG command when you want to apply the last transaction log to the secondary database and bring it online. Inthis 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 allowedfor 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 DatabaseSub-Objective:
Restore databases.References:
TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQLReference > 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 > ProductDocumentation > 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 LogBackup (Transact-SQL)