This How-to guide covers all that you need to know about restoring a Microsoft SQL Server database backup to original location, to restore a MS SQL Server database to your MS SQL Server so that your SQL Server can utilize the database that was stored inside a backup set. Compared to restoring to alternate location, as detailed in the guide here, this method restores the entire MS SQL Server database, stored in the NovaBACKUP backup set, to restore directly back to the live MS SQL Server that is running on the same Windows Server system that the backup was originally taken on. 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. Please note that in order for the restore to original location to work, 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. 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 original location, you can restore MS SQL Server database backups to an alternate location to be able to restore an individual table in a MS SQL Server database; the guide on restoring MS SQL Server database to an alternate location is here. The Restore to original location is always a Full Database restore – This recovery method is used when the whole storage group (databases and log files) must be restored from a MS SQL Server database backup. If you wish to restore an individual database table, 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 in 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 MS SQL Server database using the plugin method of backup. To do this 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 original location” and “Restore file(s) to alternate 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 original location" method of Restore. In this guide we cover how to restore the Microsoft SQL Server database to the original location. We have another guide here for how to restore the MS SQL Server database to alternate location in order to make use of the native MS SQL Server database files, such as the restored .MDB file. You can utilize the .MDB file with the NovaBACKUP Granular Restore application to restore an individual MS SQL database table item, to restore individual table item(s) back to either the original MS SQL Server or a secondary MS SQL Server that you specify. 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.
- Using this method of restoring the MS SQL database(s) to the original location it is RECOMMENDED that the SQL database that you will restore is marked in MS SQL Server for Offline status, otherwise the restore from NovaBACKUP could fail if it that SQL database is actively being written to and/or accessed by users. It is not 100% necessary to but it is recommended to mark the active MS SQL Server database as offline. To do this run the Microsoft SQL Management Studio application which matches the version of SQL Server that your database is running on to be able to mark the database as Offline. Be careful here with having multiple versions of SQL Server installed on the same system as you will need to run the version of Management Studio that the database is running under, otherwise you will not see the database listed when you go to perform the Offline action. In SQL Management Studio expand the list of Databases, then locate the existing SQL database that you will be restoring and right-click on it, then select Tasks from the menu, and then click on the "Take Offline" menu item. A screenshot example of how this looks in SQL Server Management Studio 2016 or 2017 is shown below:
- You will now see a "Take Database Offline" screen, it may state "Status: Ready" which means that this database is active and running, now enable the checkbox on the "Drop All Active Connections" option, and then click OK to perform the change, to mark this database as Offline. Now repeat these actions again to mark any other MS SQL databases that you will be restoring using the restore to original location method. A screenshot example of how this looks in SQL Server Management Studio 2016 or 2017 is shown below:
- In the list of MS SQL databases you should see that "(Offline)" is now appended to the database name. Once you have confirmed that the MS SQL database(s) are set to Offline status, you can proceed with the restore to original location. Make sure that the (Offline) status after the database name shows like this screenshot below:
- Now you are ready to perform the restore to original location to replace the existing MS SQL database(s) in Microsoft SQL Server. 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. Also, again, as outlined in Step 8, confirm that your MS SQL database is set to Offline status prior to attempting the restore again, if you had an error restoring prior.
- Your Microsoft SQL Server database should have restored successfully at this point, confirmed by the latest [Restore Operation] type of log entry viewable in NovaBACKUP's Log tab screen.
- Please note that NovaBACKUP's restore to original location function will know to set your MS SQL database to Online mode if the restore was successful so it will not require you to manually bring your MS SQL database back online. However, it is a good idea to confirm that the MS SQL Server database(s) that was restored is showing as not in Offline mode. A screenshot example of what it should look like is shown below:
- Thank you for reading this tutorial.
Thanks for your attention and stay tuned for more,
Jon Ferraez / NovaStor