In this series of posts, we are going to discuss how to perform database restoration using both SSMS and Transact-SQL. In case of a disaster, we should be able to restore the data till the time of failure, as early as possible. That’s why, it is often advised in the SQL communities to plan for a restore strategy as it covers the initial backup strategy of the database. However, planning a good restore strategy does not helps us to recover most of our data, if our backup does not work, when required. Thus, we should test our backups periodically by restoring the same in the test environment. This will validate our most recent backup and keep us ready for the future.
I have installed two instances of SQL Server 2005 Enterprise Evaluation with Service Pack 3 in my test environment. All of the test databases are reside on the default instance. However, we are going to restore all the databases on the named instance, SQL2K5Dev. In my earlier post, I have mentioned that only Full and Bulk-Logged recovery models support transaction-log backup, however, data files backup is supported in all recovery models. Thus, we are starting this restore series with some scenarios.
A. Restoring a Database from a Full Backup File
In this demo, we are going to restore the SKG_TEST database on the named instance from the backup file “SKG_TEST_backup_200911150206.bak” using SQL Server Management Studio.
1. Open SQL Server Management Studio (SSMS) from the Start Menu and connect to the default instance SQL2K5Dev.
2. Once we connected to the named instance, Right-click on the Databases and select Restore Database.
3. In the Restore Database page, select From Device option to select the backup file, also enter the name of the database to be restored in To Database option.
4. Once we select the backup file and enter the restore-database name, we will get the following screen:
5. In the Option page of Restore Database properties, set the location where the data file and log file of the database will reside. In the Recovery state option, select the RESTORE WITH RECOVERY option.
6. If the database is already existing on the current instance, then select Overwrite the existing database from the Restore options as shown above in the Restore Database screenshot.
7. Click on OK to start the restoration process. Once the restoration process is completed, we will get the below screen.
Thus, we have restored the SKG_TEST database on the instance SQL2K5Dev from a full database backup file. Full database restoration is possible for all recovery models.
In our next post, we will see how to restore a database using full and differential backup.