Overview

This How-to guide covers all that you need to know about restoring a Microsoft SQL Server database, located in a backup set produced by NovaBACKUP Business Essentials using the MS SQL Database plugin method of backup, to an alternate folder location. This method of restore allows for restoring individual MS SQL database items, such as individual tables, functions, rules, views, etc. located inside the MS SQL database, and is often combined with use of the NovaBACKUP's Granular Restore application. The alternate folder restore method can be utilized to restore a MS SQL Server database that was part of a backup set to a specified folder where the native MS SQL database files can be extracted to. Those native MS SQL database files could then be utilized by a program like NovaBACKUP Granular Restore, for instance to be utilized to restore an individual table from a database and have that table be imported in to either a live database or a .bak format file. Compared to restoring to original location, as detailed in the guide here, this method allows for restoring a single database item such as a table that is located in the MS SQL database, if that is all that you need and you do not want to restore the entire database over the prior. NovaBACKUP Business Essentials contains native "plugin" capability to perform backups of Microsoft SQL, Microsoft Exchange, Microsoft Hyper-V Virtual Machines and VMware Virtual Machines. NovaBACKUP has requirements for the Microsoft SQL Server version to be up to date, we normally recommend the latest service pack and cumulative update that is available for MS SQL Server to be in place prior to performing a backup or restore of a MS SQL Server database. Make sure that MS SQL Server is up to date with the latest service pack and cumulative updates performed, normally Service Packs must be manually downloaded and applied so keep that in mind. There is a website that we utilize and recommend client’s use at the address here: https://sqlserverbuilds.blogspot.ca/ . This website reliable shows the latest versions of MS SQL Server to be able to see all the release dates and direct downloads to the Service Packs and Cumulative Updates available for MS SQL.

All Microsoft SQL Server versions that are currently supported by NovaBACKUP, can perform a restore of a MS SQL Server database to the original location, as long as the backup utilized the native "plugin" method. Unlike the restore to original location method, the restore to alternate folder method does NOT require that the name of the current machine that you are attempting to restore the MS SQL Server backup on must match the server name that the backup was originally performed on, as well as the currently installed MS SQL Server version is REQUIRED to be the same version and Service Pack level as when the MS SQL Server backup was originally performed. It may however REQUIRE that the version of Microsoft SQL Server is the same as the version that was used at the time of backing up the MS SQL database using NovaBACKUP, to be able to use that individual database data in granular fashion directly back to the original or secondary MS SQL Server. For example, if you performed a backup of a Microsoft SQL Server Database when the Microsoft SQL Server version was SQL Server 2016 SP1 (Service Pack 1) and now you are trying to restore that SQL Server Database to SQL Server 2016 RTM (Release To Manufacturer), the initial release version, say after a hardware failure and rushing to reinstall Windows Server 2016 and SQL Server 2016 and then not running any of the numerous newer patches, the restore to original location will likely fail as the MS SQL Server database stored in the backup set that you are now trying to restore to the older non Service Pack level Microsoft SQL Server version is no longer compatible. In that case you would first be required to install Service Pack 1 for Microsoft SQL 2016 prior to attempting the MS SQL Server database restore to original location with NovaBACKUP. In addition to the restore to alternate folder location, you can restore MS SQL Server database backups to original location to be able to restore a complete MS SQL Server database stored in a backup set back to the original MS SQL Server that it originally was backed up from; the guide on restoring MS SQL Server database to original location is here (in order for this method to work the name of the current computer has to match how it was stored in the backup set). If you wish to restore an individual database table or other such item inside a MS SQL database, please refer to Granular Restore of Single Mailbox Items using the NovaBACKUP Granular Restore application.

NovaBACKUP does not directly support the restore of individual tables or other such related items granularly inside a MS SQL Server database. However, you can use NovaBACKUP in conjunction with the NovaBACKUP Granular Restore application, provided with NovaBACKUP Business Essentials, to perform the restore of individual database tables or other such items stored in the MS SQL Database. If you have not yet registered and installed NovaBACKUP Granular Restore, read the guide here, or watch the video walkthrough on obtaining and using the application to restore an individual MS SQL table item here.

Instructions:

Start NovaBACKUP Business Essentials and navigate to the Restore tab.
Select the backup you wish to restore from and check the box next to the Microsoft SQL Database(s) you are restoring.
Once the proper backup set is selected in the Restore tab, click on the [Settings] button at the bottom of the Restore tab screen to check and verify the settings for this Restore job.
On the Restore tab in the "Properties for Restore Job" dialog:
- The default option here is "Restore files(s) to original location", you need to change it to "Restore file(s) to alternate location".
- We have another guide here for how to restore the MSSQL Server database to the original location it was backed up from.
- The restore to alternate folder method allows you to utilize the native .MDF file that was stored in the backup set with the NovaBACKUP Granular Restore application to restore an individual MSSQL database table or like items back to either the original MSSQL Server or a secondary MSSQL Server you specify.
- In this example we are telling NovaBACKUP to restore a few MSSQL databases stored in a prior backup set to restore to the alternate folder C:\SQLRESTORE.
- A screenshot example is shown below for what this would look like:
On the "Run As" tab:
- Make sure to run the restore job as an Administrator account.
- A screenshot example of the Run As settings for the Restore Job is shown below:
A note about "Network Credentials"
- It is REQUIRED to enter in the Network Credentials if you are restoring the backup from a Network Location. Fill in the Network Credentials that match your network location / network share / NAS device, etc. before attempting a restore from a Network Location.
  - This is true even if you have already created a "Network Device" on the "Device" tab of the software.
- An example screenshot is below:
Click [OK] to save the Restore Job options.
To start the restore click the [Restore] button at the bottom right of the Restore tab screen.
- NOTE: You can also save this job and schedule it for a set day and time if you need.
If you get any errors during the restore please revisit the previous steps to confirm the Restore Job's Settings for Run As and Network Credentials.

Confirm the restored files

Your Microsoft SQL Server database should have restored to alternate folder successfully at this point, confirmed by the latest [Restore Operation] type of log entry viewable in NovaBACKUP's Log tab.
1. You will want to verify that the folder contents that you told NovaBACKUP to restore the SQL Database to alternate folder, contains an .MDF (SQL Database file) and .LDF (SQL Database logs file), otherwise you won't be able to continue.
2. NOTE: If the restored files contain a .VDF file, you will need to extract the .VDF file manually by following the steps in our KB article [SMB] Plugin Backup Restore - How to Extract a VDF file
At this point you have verified that the Microsoft SQL Server database has restored successfully to an alternate folder in its native MDF and LDF file format.
You can now utilize the NovaBACKUP Granular Restore application to restore individual MSSQL Database contents, such as a single database table, as needed.
For those that would like to attempt to restore the entire Microsoft SQL Database, via the files that were restored, to a Microsoft SQL Server instance running the same SQL version / Service Pack as the original, using SQL Server Management Studio, please review our KB article [SMB] Restore MSSQL database to MDF and LDF and use Attach Database in SSMS.

Use NovaStor Granular Restore to restore SQL data

At this point, now that the MSSQL Server database has restored to alternate folder successfully, you can utilize the NovaBACKUP Granular Restore application to open the .MDF file to restore an individual table or other items from the MSSQL database.
Using the NovaStor Granular Restore tool, the data can be restored directly to the original MSSQL Instance or to a secondary MSSQL Server.
If you have not yet registered for and installed the NovaBACKUP Granular Restore application, please read the written guide here, or watch the video walkthrough here regarding registering for, downloading, installing and then using the NovaBACKUP Granular Restore application to restore individual table or other such MS SQL database items.
- NOTE: The NovaBACKUP Granular Restore application is updated with almost every new build of NovaBACKUP.
- If you would like to make sure that your NovaBACKUP Granular Restore application is up to date and running the latest edition, you can Register to download the latest version of the NovaBACKUP Granular Restore tool via NovaBACKUP Business Essentials backup client software -> Help menu -> On The Web -> "Granular Restore for MS SQL and Exchange".
- NOTE: If you do not see that menu item it means that your Business Essentials software is not currently licensed.
- Simply run the .MSI file included in the .ZIP to update or re-install the application to have the latest version in place on any of the machines that you had it installed on prior.
- For help on that subject please read the guide here.
Here we show an example of using NovaBACKUP Granular Restore application to open the .MDF file that was produced when we performed the restore of the "WideWorldImporters" MSSQL database to alternate location in NovaBACKUP:
Once the MSSQL database is opened in the NovaBACKUP Granular Restore application, you will see a list of items inside the database. You can restore any of the items individually (in granular fashion) from here.
- In the example we select one function and one table to restore directly to a database of our choice, in this case a live SQL Server in our environment.
- Here is an example screenshot of selecting the individual items in the extracted MSSQL database, those items being one function and one table:
When you are ready to restore the items, you can restore the items to a SQL script file locally, or directly to a live SQL Server of your choice, either your primary or secondary SQL Server.
- The example screenshot shows restoring the items directly to our primary SQL Server and shows the "Connect to SQL Server" dialog box
  - the first pull-down menu detects all of the MS SQL Servers in your environment by searching the network for them,
  - the SQL Server login prompts allow you to specify the credentials to your MS SQL Server,
  - and the bottom "Database name" selection allows you to browse the databases that are hosted on the SQL Server that you first specified.
- Here is the example screenshot:
Once your SQL Server has been added as a Connection, you are ready to tell the Granular Restore application how you want to restore
- click the appropriate menu item to start the restore.
- In our example we are going to export the data to the live SQL server.
  - There is a large button at the top right of the screen which is the "Export into live SQL server" function, click on that.
- An example screenshot is shown below:
You will see the "Export into live SQL Server" dialog box which shows a summary of what is going to be restored.
Click the Start button once satisfied to start that restore of the data that you selected to export to live SQL Server.
You will see a Progress window pop up and once the restore is completed, it will tell you in the progress window.
Be sure and check the completion status in NovaBACKUP Granular Restore to be certain that the items completed the export of data to live SQL Server.
Thank you for reading this tutorial.