Restore SQL Server 2014 Backup to 2017 – Step by Step
Problem
I am not a SQL DBA, but, I am in a situation, where my requirement is to restore some crucial tables to another server. I have to restore SQL Server 2014 backup to 2017 database as it will be our new report server. But, extracting selected tables from the .bak file directly is not an option available in SQL server. I was told by my DBA friend that I must restore the database first and then move Tables to new reporting Server database. But, it will take so much time to restore a large database and then move the tables.
Hope someone have gone through the same situation. So, please suggest me a method through which I can restore SQL Server 2014 backup to 2017 database in a small downtime.
Solution
The problem in above mentioned situation is, the restore time is very large for huge database. So, before processing the restore operation manually, make sure you have enough amount of time. Because it may lead to substantial delays in getting those critical database tables.
Finally, there is a new concept called SysTools SQL Backup Recovery Tool, which is fully functional to support SQL .bak file of all SQL Server. The software allows users to restore SQL Server 2014 backup to 2017 Server with all or selective components of database. Moreover, it will provide the functionality to restore the selective components of .bak file to an existing database. This will save the user precious time and effort too.
Steps to Restore SQL Server 2014 Backup to 2017
- Download, Install and launch the software on your windows machine
- Click on open button to Add .bak file to the software
- An add file window will be appear on your screen. Click on Browse to select the .bak file for restore.
- After providing the .bak file to the software, it will ask you to select SQL Server version of your .bak file.
In case, you don’t know the SQL Server version of .bak file, then choose “Auto detect SQL Server file (.bak) version” option then click on OK.
Or if you know the .bak file version then select the SQL server version accordingly and proceed with OK.
- Software has a functionality to restore multiple .bak file of a database at a time.
- Next, a new window will appear with .bak file details. Select the backup to recover i.e. Full Backup, then click on Recover.
- Software will scan the .bak file and provide you the scanning report
- After scanning the .bak file, the software will provide you a preview of your data available in .bak file. Click on a specific table to preview its data.
- Click on Export button to restore the database to destination Server.
- The export window will appear, that lets you restore the .bak file direct to SQL Server or export as .sql script files.
- To restore backup on SQL server 2017, you need to select SQL Server database option and provide the server details like Server Name, Username and Password.
- Again, the software will ask whether you want to restore the .bak file as Create new database or Export to existing database
- By choosing Export to existing database, the software will allow us to select the database on which we can restore the backup file.
- Select the database from the drop down list on which you want to restore.
- Now select the database components like Tables, stored procedures, functions, views etc., that you want to restore
- Next, if you want only schema of database objects then choose with only schema or if you want to restore both schema and data the choose with schema & data. Click on Export.
- The restoration of SQL Server 2014 backup to 2017 database has been done successfully.
Conclusion
This was just a scenario, in which we explained the alternate option to restore SQL Server 2014 backup to 2017 in a small downtime. In case, when you have to restore the database from a corrupt .bak file, then the tool always helps you and provide the expected result. The reason why I prefer this type of solution is not only because of time saving, but, also it has a simple interface for a great user experience.