How to Restore a Microsoft SQL Server database to native MDF and LDF files and use the Attach Database function in SQL Server Management Studio to completely restore a SQL database

Overview
Instructions:
Confirm the restored files
Attach the Database using SSMS

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 MSSQL Database plugin method of backup, to restore that MSSQL database to an alternate folder location, to then be able to attach that restored SQL native MDF (Master Database File) and LDF (Log Database File) files as a complete database restore to a Microsoft SQL Server in your environment. Keep in mind that this method of SQL database restore does not necessarily require the target server to be the original MSSQL Server, however the SQL version and Service Pack level must match between the version of SQL that the database was originally backed up on and the target server. This method could be utilized for various situations where you had trouble restoring to the original location, the default method of restore, say if you had to build a new server from scratch or perhaps reinstall Microsoft SQL Server application and the restore to original location now does not work for you, as well as you don't want to restore a single MSSQL database item such as one table from the database, which we have a tool for. This method of MSSQL database restore allows for restoring the entire SQL Database to alternate folder using NovaBACKUP and then using Microsoft SQL Server Management Studio to attach those native MDF and LDF files as a MSSQL database to attach (restore) to the Microsoft SQL Server of your choosing. You can read about what an MDF and LDF file is in the third party article here. A caveat to using this method is that the Microsoft SQL Server that you are going to attach the database to has to match the version of Microsoft SQL Server that the MSSQL database was originally hosted under at the time of backup. Another caveat to using this method is that if this same named database already exists in Microsoft SQL Server that you are attempting to utilize the Attach function on to restore the native MDF and LDF files to that same named database will have to be detached first, it is advisable to read the guide by Microsoft that shows those Attach Database prerequisites here; Microsoft's guide on how to detach a database is here. The alternate folder restore method can be utilized to restore a MSSQL Server database that was part of a backup set to a specified folder where the native MSSQL database files can be extracted to. Those native MSSQL 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, this method allows for restoring a complete MSSQL database in to the Microsoft SQL Server of your choosing (as long as the version matches). 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 reliably 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. 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 MSSQL Server version is REQUIRED to be the same version and Service Pack level as when the MSSQL 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 MSSQL database using NovaBACKUP, to be able to use that individual database data in granular fashion directly back to the original or secondary MSSQL 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 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 alternate folder location, you can restore MSSQL Server database backups to original location to be able to restore a complete MSSQL Server database stored in a backup set back to the original MSSQL Server that it originally was backed up from; the guide on restoring MSSQL 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 MSSQL database, please refer to Granular Restore of Single Mailbox Items using the NovaBACKUP Granular Restore application.

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, please continue reading.

Attach the Database using SSMS

How to Attach (restore) the .MDF (Master Database File) and .LDF (Log Database File) files you have restored to an alternate location using SSMS (SQL Server Management Studio). Keep in mind that in order to perform the Attach Database function, the version of Microsoft SQL Server has to match the version that the Database was originally stored under (Edition and Service Pack) at the time the backup took place.

NOTE: A caveat to using this method is if this database already exists in Microsoft SQL Server on the machine you are attaching the database to. You will be required to detach the existing database first, it is advisable to read the guide by Microsoft that shows the Attach Database prerequisites here; Microsoft's guide on how to detach a database is here.

At this point you will want to copy two files from the extracted MSSQL database that you restored using the restore to alternate folder method detailed in this guide.
1. Navigate to the folder that you provided as the Restore To location. You will see the name of your database in this folder tree. Under the named database folder, you should see a bunch of files including one .MDF and one .LDF, those are the only files that need to be copied to the active SQL data you are currently using in SQL.
  - Default SQL Folder locations
    - SQL Server 2016 C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\
    - SQL Server 2017 C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\
  - Default SQL Express Folder locations
    - SQL Express 2016 C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\DATA
    - SQL Express 2017 C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\
  - For the database that you would like to Attach in SQL Management Studio, copy the .MDF and .LDF files from the source folder (alternate location restore) to the destination folder (active SQL data folder) now.
    - In our example we copied the two files to the standard SQL data folder that is utilized by SQL 2016 which is C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\DATA\
    - Example screenshot is shown below, showing where the Test.mdf and Test.ldf files were copied to (active SQL data folder):
To perform the Attach Database action you will load Microsoft SQL Management Studio that matches your version of Microsoft SQL Server.
- NOTE: If SQL Server Management Studio is NOT installed, you will need to install it now, as some versions of SQL Server consider it as an optional component.
Once Microsoft SQL Management Studio is loaded you will log in, then left-click to select "Databases" in the Object Explorer window, then right-click on "Databases" and select "Attach...".
- An example screenshot of SQL Management Studio 2016/2017 is shown below, displaying the steps required:
Prior to attaching the MSSQL database using the Attach method, make sure that the same named database does not currently exist in the "Databases" listing in the Object Explorer window. If it does exist, you will have to delete it by right-clicking and doing a Delete. Otherwise the attach function will not work, as the database already exists as the same named database.
In the "Attach Databases" menu that is displayed you will click the "Add..." button to display the locate database window.
- Here is an example screenshot:
In the "Locate Database Files" dialog box, browse to the .MDF file you copied to the default SQL data folder.
- An example screenshot is below:
You will now be in the "Attach Databases" dialog box and the screen will populate itself with the database that you selected in the prior step, and list the Data (.MDF) and Log (.LDF) file names that you want to attach. When ready to complete the Attach action click the "OK" button.
- An example screenshot is below:
If the SQL database Attach function works successfully you will see it in the list under "Databases" in Object Explorer.
You should now be able to backup this database using NovaBACKUP. You will want to make sure that your backup job still contains the selection for this database that you just attached, close NovaBACKUP if it is currently running and reopen it to see the newly attached database be listed in the "Microsoft SQL Server" plugin section on the "Backup" tab.
Thank you for reading this tutorial.