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
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):
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...:
This will open the restoration dialog. In this dialog, select the general tab and set the source to Device and click on the ellipsis (...):
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:
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:
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:
Once you click on OK the restoration process will start and you will found your new databases restored on your server.
Happy coding !