Contents
- Overview
- Instructions:
- We Recommend to Take active Databases Offline before restoring over them
- Restore the Database into MSSQL
Overview
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 MSSQL Server database to your MSSQL 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 MSSQL Server database, stored in the NovaBACKUP backup set, to restore directly back to the live MSSQL 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 MSSQL Server to be in place prior to performing a backup or restore of a MSSQL Server database. Make sure that MSSQL 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 MSSQL Server to be able to see all the release dates and direct downloads to the Service Packs and Cumulative Updates available for MSSQL.
All Microsoft SQL Server versions that are currently supported by NovaBACKUP, can perform a restore of a MSSQL 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 MSSQL Server backup on must match the server name that the backup was originally performed on, as well as the currently installed MSSQL Server version is REQUIRED to be the same version and Service Pack level as when the MSSQL 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 MSSQL 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 MSSQL Server database restore to original location with NovaBACKUP. In addition to the restore to original location, you can restore MSSQL Server database backups to an alternate location to be able to restore an individual table in a MSSQL Server database; the guide on restoring MSSQL 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 MSSQL 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 MSSQL 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 MSSQL 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 to original location". 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 MSSQL Server database to an alternate location.
- A screenshot example is shown below for what this would look like at this stage:
- 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:
- 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.
- Click OK to save the Restore Job options.
We Recommend to Take active Databases Offline before restoring over them
Before starting the Restore using NovaBACKUP, you should confirm some settings for the database you will be replacing.
It is RECOMMENDED that the SQL database that you will restore "over" is set to the Offline status in SQL Server settings, 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 but highly recommended to take the active MSSQL Server database offline.
- To do this, run Microsoft SQL Server Management Studio matching the version of SQL you will be restoring into.
- Expand the list of Databases, then locate the existing SQL database that you will be restoring, right-click-> hover over Tasks -> click on "Take Offline".
- 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" dialog, 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.
- Repeat these actions again to mark any other MSSQL databases that you will be restoring "over".
- A screenshot example of how this looks in SQL Server Management Studio 2016 or 2017 is shown below:
- In the list of MSSQL databases you should see that "(Offline)" is now appended to the database name. Once you have confirmed that the MSSQL database(s) are set to "Offline", you can proceed with the restore using NovaBACKUP.
- Make sure that the (Offline) status after the database name shows like this screenshot below:
Restore the Database into MSSQL
Now you are ready to restore over the existing MSSQL database(s).
- 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.
- Also, confirm that your MSSQL database is taken Offline prior to attempting the restore again, if you had an error restoring while it was active.
- 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.
- Please note that NovaBACKUP's restore to original location function will set your restored MSSQL database Online if the restore was successful so it will not require you to manually bring your MSSQL database Online. However, it is a good idea to confirm that any MSSQL Server database(s) that were restored do not show (Offline).
- 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