How do you recover one SQL Server database?

Question 1

Before you restore a SQL database?

Answer 1

Limitations and restrictions

The system administrator restoring a full database backup must be the only person currently using the database to be restored.

Question 2

What are the prerequisites?

Answer 2

Prerequisites

Under the full or bulk-logged recovery model, before you can restore a database, you must backup the active transaction log.

To restore an encrypted database, you must have access to the certificate or asymmetric key used to encrypt the database!

Without it , you cannot restore the database. So you must retain that certificate for as long as you need the backup!

Question 3

Step by Step

Answer 3

1. Connect to the appropriate instance of the SQL Server Database Engine, and then in Object Explorer, click the server name to expand the server tree.
2. Right-click Databases, and then click Restore Database. The Restore Database dialog box opens.
3. On the General page, use the Source section to specify the source and location of the backup sets to restore.

Question 4

What are SQL recovery models?

Answer 4

A recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available. Three recovery models exist: simple, full, and bulk-logged.

In bulk logged recovery mode certain bulk operations are minimally logged. In FULL recovery mode these are fully logged. These bulk operations are as mentioned below

1. SELECT INTO
2. BULK IMport operations including BULK INSERT and BCP
3. INSERT INTO SELECT command using the OPENROWSET(BULK) function
4. Partial updates to columns having large value data type
5. Using WRITE clause in UPDATE statements
6. Index operations e.g CREATE INDEX, ALTER INDEX REBUILD , DROP INDEX

In Bulk Logged Recovery Mode when you execute these operations SQL Server only logs the fact that these operation occurred and information about space allocation. The actual change in the data is maintained in the BCM (Bulk Changed Map)

Since the actual changes are not recorded in the log file, the log file size in the relatively less in size but this tradeoff comes with the price of increased backup time. This is so because during the log backup its not just the log being backup, but also the extents that are marked by the Bulk Changed Map as changed.