How to restore a Database from multiple backup (bak) files in SQL Server Management Studio

The restoration of a database tends to be (at least with small databases) pretty easy to do in SQL Server with SSMS. Whatever the current reason is, for example the restoration on a production environment, restoration on a local environment to check the database integrity and so on, you will need to know how to import it, as sometimes this isn't so obvious with the interface. As if this wasn't enough, sometimes during the restoration you will have not a single file to restore but a split backup which generates multiple .bak files.

In this article, we'll explain you how to restore an entire database from multiple bak files using SQL Server Management Studio.

1. Store bak files inside the Backup directory of your SQL Server installation

In case that you are importing the bak files in your local SQL server, you will need to store the files in a specific directory. Search for the installation directory and locate the Backup directory, which is usually C:\Program Files\Microsoft SQL Server\MSSQL<version>.SQLEXPRESS\MSSQL\Backup. Paste the files inside the mentioned directory and proceed with the next step.

2. Connect to your local/remote SQL Server

Open SSMS and access the database engine with the default Windows authentication (or from the connection that you want to access):

SQL Server Management Studio SSMS Login

Click on connect and access your SQL server. Now that the connection is open, you will be able to use SSMS normally.

3. Restoring database

Now that you have access to the server, expand the connection on SSMS and select the Databases node, here do right click and click on Restore Database...:

Restore Database Context Menu SQL Server

This will open the restoration dialog. In this dialog, select the general tab and set the source to Device and click on the ellipsis (...):

Restore Dialog SQL Server

This will open the select backup devices dialog, here you will be able to select the bak files from the directory mentioned on the first step when you click on add:

Add Backup File Restoration SQL Server

This will open the filebrowser of the SQL Server, that usually opens in the Backup directory of the Microsoft SQL Server directory, so you will see the bak files of the first step here. If they don't appear, search for the directory manually and select the files:

SQL Server Filebrowser

This will add the files to the backup devices, select them all and click on OK:

Once you click on OK, the database(s) stored on the bak files will be listed and you will need to select which of them (if there's more than one) you want to restore:

SQL Server Databases to restore

Once you click on OK the restoration process will start and you will found your new databases restored on your server.

Happy coding !

This could interest you

Become a more social person