How to Restore Database from Backup File in SQL Server 2012

To restore database in SQL Server 2012 from database backup file is a method to move a database from a SQL Server instance to another SQL Server 2012 instance. If SQL Server Database administrator has a backup file of the SQL database which he wants to move or copy to a new SQL Server 2012 instance, a direct database restore task can be executed on the target database instance.

This SQL Server tutorial shows the steps how to restore a new database from its backup file on a SQL Server 2012 database instance. This method prevents the administrators and developers to create a new blank database and restore the desired database onto new database from existing backup file.

SSMS Restore Database Task

Microsoft SQL Server Management Studio aka SSMS provides wizards like restore database wizard which makes daily tasks of a database administrator easier. In this SQL Server tutorial, I will use Restore Database task to create a new database from a database backup file. Let's start. First launch SQL Server Management Studio (SSMS). Connect to the SQL Server 2012 database instance where you want to create your new database by restoring database from its backup file.

When you connect to the database server, right click on the Databases node in Solution Explorer view as seen in below screenshot. In context menu choose Restore Database... task.

SQL Server Restore Database task on SQL Server 2012
Microsoft SQL Server "Restore Database" task on a SQL Server 2012 database instance

When restore database wizard started, it will first request to know the data folder where the database backup file is stored. Since I have an external database backup file which is taken from an other SQL Server, choose Device option as Source to point the local folder and backup file. Device selection will activate the "..." button which will enable database admins to browse the server folders for the backup file. Press "..." button to open the following screen.

choose backup media and database backup file location
Specify the backup media and its location for your restore operation.

Press Add button. New SQL Server Management Studio wizard will display default backup folders on the database server. You can browse and navigate to the folder where the database backup files is located using the left screen.

locate database backup file to restore on SQL Server
Locate backup file

After you select the backup file you want to move to the new SQL Server instance, click and highlight the backup file and press OK button to continue.

You will be redirected to the previous screen with selected database backup file for restore database operation. Press once more on OK button.

SQL Server 2012 database backup file

After the database backup file for restore operation is selected, the Restore Database wizard initial screen is now populated with target database name, database backup time, etc. If there is only one backup set within the backup file, it will be displayed as marked.

At this point, database administrators can also check the files and file groups of the database to be restored by switching the Files and Options tabs. I will not change these settings but if you are restoring database on a productive SQL Server instance, it is better to check especially the files configuration. Take care that the database is not overriding on existing database files.

restore SQL Server database from backup file on new SQL Server 2012 instance

You can immediately run Restore Database task by pressing OK. An other option is to verify the backup file to see if everything is correct with the backup media, etc by pressing the Verify Backup Media button. If database backup file is corrupted verification step will fail.

If database restore operation is completed without any error, you will be informed that the database is restored successfully.

SQL Server database restore from backup file operation is completed successfully

To summarize, I had a SQL Server database backup file which is up and running on a SQL server. I copy the database backup file to another SQL Server 2012 database instance. Using the backup file, a direct Restore Database operation on the new SQL Server instance will create new database and restore backup file at the same time. SQL Server Management Studio Restore Database task wizard is a good tool to restore database from backup file as seen in this tutorial screenshots.