Restore SQL Server Database from Multiple Backup Files: Learn the Best Practices

SQL Server can come across errors at any time. To tackle any severe situation, it is always standard practice to backup SQL database. The backup of primary and secondary database files, as well as Transaction log files, are saved as .bak files. In case the database encounters some irreparable errors or there are some hardware issues, users have no other option but to restore SQL database backup files to the Server.

Backup restoration is not a tough task to perform if you have proper knowledge about the process. But the thing gets critical when one has to restore multiple backup files. Even SQL DBAs and other experts cannot restore multiple backup files to SQL Server database easily. To help them, we will describe some of the most used methods of restore SQL Server database from multiple backup files. Depending on the requirement, they need to pick the right solution. But first, we will check out related user queries.

User Query 1: “SQL Server is not permitting me to restore multiple .bak files in a new server when I attempt to restore all at once. When I try to add multiple backup files to the backup software, it accepts only the first one from the list. Before I try any manual technique, looking for your opinion whether this is feasible or not.”

User Query 2: “Hello all. I am looking for an approach to restore all the SQL backup databases on the SQL server. The main issue here is that I have a large number of backup files and I want to restore those all in one go. Please tell me is it even possible or not? If Yes, then tell me an easy solution.”

How to Restore SQL Server Database from Multiple Backup Files

When it comes to restoring multiple BAK files to a SQL Server, users can take the help of manual as well as automated method. In this section, we will check out two manual techniques for multiple backup file restoration and then we will learn about a tool that can also conduct the restoration process.

Restore Multiple SQL BAK Files SQL Server Management Studio Manually

With the help of SSMS, it is possible to restore multiple backup files. But first, split backup files need to get created. Here is how to do this:

  • Open Object Explorer and expand Databases
  • Select the database you need to backup and right-click on it. From the list of options, choose Tasks >&ft; Back up…
  • On the next window that appears, select the General tab. Choose the source database and type of backup. Click on Add button to determine multiple backup paths.
  • To add each path, either type the path manually, or Browse to the folder where the backup file will be saved. When done, click on OK. Repeat the process for all the destinations.
  • When all the paths get added and displayed on the window, click OK to begin backup process. You will get a message when it is done.

Now comes the process of restoring these split backup files:

  • Go to the Object Explorer of SQL Server and right click on Databases on the left panel. Choose Restore Database…. Option.
  • In the General tab of the next window, enable the Device radio button. Click on the Browse button to select all the backup files.
  • Click on Add button from the Select backup devices window to add all the backup file locations.
  • Either type the full path address or navigate to the backup file through the folder tree structure. Repeat this for all the locations. When the listing is done, click OK.
  • All the paths will get loaded and then destination database name along with restoration type will be displayed. Finally, click OK to finish the restoration process.

Manually Restore Multiple SQL Server Database BAK File Using T-SQL

These TSQL commands can be used to restore multiple backup files to SQL Server database:

Restore One Full Backup File and One Transaction Log Backup

Here, the first command will restore the database and leave it in a restoring state. The second command, however, restores the log file backup and put the database in useable condition.

restore

Restore Full Backup and Two Transaction Log Backups

These scripts will restore the first two backups (one full backup and one log backup) with NORECOVERY and then for the last log file with recovery.

restore bak

Restore Full Backup, Latest Differential and Two Transaction Log Backups

In order to restore three initial backups with NORECOVERY and the last one RECOVERY, use the script.

restore bak file

Restore SQL Server Database from Multiple Backup Files Using Application

If you are looking for an easy technique to restore more that one SQL BAK file at once, you can choose SQL Backup Recovery. This application supports restoration of any number of backup files to all the latest SQL Server versions including 2017, 2016, and 2014. This tool also has to file size limitation for BAK files. Users can export any number of MDF, NDF and LDF file backup using this tool.

Conclusion

If you are facing the need to restore SQL Server database from multiple backup files, you can follow the methods provided here. To perform the manual methods, you can take the help of step-by-step instruction shared here. Else, you can use the cited software for smooth and hassle-free restoration.

About Andrew Jackson

I am SQL DBA and SQL Server blogger too. I like to share about SQL Server and the problems related to it as well as their solution and also I do handle database related user queries, server or database maintenance, database management etc.
This entry was posted in Sql Server, T-SQL. Bookmark the permalink.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s