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 re-install 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.
- Start NovaBACKUP Business Essentials.
- Perform a backup of a MS SQL Server database using the plugin method of backup. To do this first click on the Backup tab. Locate “Microsoft SQL Server” in the list of items to back up, near the bottom left of the backup tab. Select that item for backup. Make sure the settings for the job has the Open File Backup option enabled, as MS SQL Server requires Open File Backup (VSS). Save the job and click “Backup” to start the MS SQL Server database backup job. Once the job is successfully completed move to the next step.
- Click on NovaBACKUP’s Restore tab and locate the backup that was just completed for MS SQL Server. Expand the backup by clicking on the “+” icon to the left of the backup name. Once expanded you will see more details regarding that completed backup.
- 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 Settings area you will see “Restore to” and the options “Restore file(s) to alternate location” and “Restore file(s) to original location”. The default option here is "Restore to original location", however this option can be forced, in the Default Settings area of NovaBACKUP, to "Restore to alternate location" so we have to point this out as this guide only covers if you use the "Restore to alternate location" method of Restore. In this guide we cover how to restore the Microsoft SQL Server database to alternate location. We have another guide here for how to restore the MS SQL Server database to original location in order to restore the entire MS SQL database back to the original MS SQL Server that it was backed up from. The restore to alternate folder method allows you to utilize the native .MDB file that was stored in the backup set with the NovaBACKUP Granular Restore application to restore for instance an individual MS SQL database table or like items, to restore that individual table item(s) back to either the original MS SQL Server or a secondary MS SQL Server that you specify. In this example we are telling NovaBACKUP to restore a few MS SQL 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 at this stage:
- You will also need to confirm in the Restore Job Settings area that the Run As property is set, for the user to run the restore job as, in some cases Logged In User will not work and you will need to change the Run As property to Run As Specified User and enter an Administrator username and password. If you are restoring the backup from a Network Location (if the backup file itself that you are attempting to restore is stored on a network location), you will be REQUIRED to enter the Network Credentials for the network device/network share that you are restoring from, otherwise the restore will fail because it cannot gain permission to the network path. A screenshot example of the Run As settings for the Restore Job is shown below:
- It is REQUIRED to enter in the Network Credentials if you are restoring the backup from a Network Device or Network Location (even if you have a Network Device created in NovaBACKUP for the same network location, you are required to set the Network Credentials in the Restore Job's Network Credentials tab screen). This is because NovaBACKUP's Restore functions are not aware of your Network Device configuration (configured in NovaBACKUP's "Device" tab screen). Even though your backups work to store to the network location, does NOT mean that they will work to perform a Restore if your backup file is located on that same network location. Fill in the Network Credentials for the credentials that match your network location / network share / NAS device, etc. before attempting a restore like this. An example screenshot is below:
- Click OK to save the Restore Job options.
- Now you are ready to perform the restore to alternate location to extract the native contents of the MS SQL database(s) contained in the backup set. 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 would like.
- If you get any errors during the restore please re-visit steps 4 to 12 to confirm the Restore Job’s Settings for Run As and Network Credentials (if your backup is stored on a network location then you MUST enter the Network Credentials of the network device/network share in to the Network Credentials options for the Restore job for your restore to actually work. And depending on how you are logged in to Windows at the time of attempting this Restore in NovaBACKUP, possibly due to the rights that your current user may or may not have to be able to touch Exchange, you may also need to set the Run As permission settings in the Restore Job’s Settings to a user that does in fact have Administrator rights on the system, instead of allowing the job to run as the “Logged In User”, which is the default.
- 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 screen. You will then navigate to the folder where you told NovaBACKUP to perform the restore to alternate folder to, in order to confirm that various files including the native database .MDB file was restored. That is the primary file that the NovaBACKUP Granular Restore application can utilize to restore individual items inside a MS SQL database.
- At this point now that the MS SQL Server database has restored to alternate folder successfully, in its native MDB format, you could utilize the NovaBACKUP Granular Restore application to open up the .MDB file that is stored in the same folder that you performed the alternate restore to, to restore individual table or other such items from the MS SQL database. The data can be restored directly back to the original MS SQL or secondary MS SQL Server, via the NovaBACKUP Granular Restore application. 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 often, in fact nearly at the time of each new build of NovaBACKUP backup client software we release a new version of NovaBACKUP Granular Restore. 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. Click that menu item and a web page will be displayed which will verify if your NovaBACKUP Business Essentials license still has support for that download and then you will be shown a download link to be able to download the latest version of the Granular Restore application (in .ZIP format). Simply run the .MSI file included in the .ZIP to update or re-install the application to have the latest version in place on all 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 .MDB file that was produced when we performed the restore of the "WideWorldImporters" MS SQL database to alternate location in NovaBACKUP:
- Once the MS SQL database is opened in the NovaBACKUP Granular Restore application, after selecting and opening the MS SQL Server database .MDB file, you will see a list of items inside the database, this is your ability to restore any of the items individually (in granular fashion). In the example we select one function and one table to restore those two items directly back to a live SQL Server in our environment, to a database of our choice. Here is an example screenshot of selecting the individual items in the extracted MS SQL 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 you can restore the items 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 contents for our example, 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 is added as a Connection, in the prior step 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 sure that the items completed the export of data to live SQL Server.
- Thank you for reading this tutorial.
Thanks for your attention and stay tuned for more,
Jon Ferraez / NovaStor