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.
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.
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.
4. Once the data files are restored, we will get the following option. The database is inaccessible with Restoring mode.
5. To begin the transaction-log restoration process, right-click on the database, go to Tasks –> Restore –> Transaction Log.
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.
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.
8. Once the restoration process is complete, we will get the following message:
9. After the restoration process is complete, we will get screen, when we expand the Database folder in SSMS.
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.