Restoration of Database using SQL Server 2005 : Part III


In my previous post, we have discussed how to restore a database from a Full and a Differential data backup file. In this post, we are going to discuss, how to restore a database from both data and log backup files. For the demo, we are going to restore the data and log backup file on the dev instance to the database SKG_TEST3. All of our backup files are present on the same backup media set.

C. Restoring a Database from Both Data & Log backup File

1. Connect to the dev instance of SQL Server 2005 using SSMS, and expand the Database folder. Right-click on the database folder and select Restore Database.

image

2. On the Restore Database page, select the backup media set from the From device list. After we select the backup media set, select both the data backup files (Full & Differential backup files), select the name of the database in To Database, where we are going to restore both the data and log files.

image

3. Expand the Option  tab in the Restore Database  page. If the database is already existing on the instance, choose the option of Overwrite the existing database. Select the recovery state as RESTORE WITH NORECOVERY. If we choose the option of NORECOVERY, it means a subsequent transaction log backup file is to follow to complete the restore process. Click on OK to start the restoration process.

image

4. Once the data files are restored, we will get the following option. The database is inaccessible with Restoring mode.

image

5. To begin the transaction-log restoration process, right-click on the database, go to Tasks –> Restore –> Transaction Log.

image

6. On the Restore Transaction Log page, select the backup file list, choose the backup media set, on which we have the transaction-log backup file. From the  Database  option, select SKG_TEST3.

image

7. Click on the Option tab in the Restore Transaction Log page. Select the recovery state as RESTORE WITH RECOVERY. Click on Ok to begin the t-log restoration process.

image

8. Once the restoration process is complete, we will get the following message:

image

9. After the restoration process is complete, we will get screen, when we expand the Database folder in SSMS.

image

With this, the restoration of database is completed by using both the data and log backup file. In our next series, we are going to discuss how to restore the database using Transact SQL query language.

Advertisements

About Sudeepta Ganguly

A SQL Server User... Still Learning
This entry was posted in Backup - Restore. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s