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 restore the complete SQL database
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 restore that MS SQL database to an alternate folder location, to then be able to attach that restored SQL database's 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 MS SQL 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 re-install 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 MS SQL database item such as one table from the database, which we have a tool for. This method of MS SQL 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 MS SQL 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 MS SQL 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 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 complete MS SQL 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 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.
- 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 .MDF and .LDF file was restored. 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.
- At this point you have verified that the MS SQL Server database has restored successfully to alternate folder using NovaBACKUP's Restore to alternate folder method, which outputs the MS SQL Database in its native MDF and LDF file format to then be able to restore that MS SQL Database in its entirety back to a Microsoft SQL Server running the same version and Service Pack level Microsoft SQL Server as the database was originally backed up as. Alternatively you could at this point utilize the NovaBACKUP Granular Restore application to restore individual MS SQL Database contents, such as a single database table, in granular fashion using the alternate guide here. For those that would like to attempt to restore the entire MS SQL Database via the files that were restored to alternate folder, please continue reading.
- If you would like to attempt to Attach Database (restore) the entire SQL Database which was been restored to alternate folder using NovaBACKUP which produced native MDF and LDF files and Attach those native MS SQL database .MDF (Master Database File) and .LDF (Log Database File) files for the MS SQL Database that you are attempting to restore by utilizing the "Attach Database" function in Microsoft SQL Management Studio, you can follow the next steps. Keep in mind that in order to perform the Attach Database function the version of Microsoft SQL Server has to match the version of Microsoft SQL Server that the MS SQL Database was originally stored under.
- NOTE: A caveat to using this Attach Database 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 MS SQL database was originally hosted under at the time of backup. Another caveat to using this method is that if this database already exists in Microsoft SQL Server that you are attempting to utilize the Attach Database function on you will be required to first detach the existing database 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.
- At this point you will want to copy two files from the extracted MS SQL database that you restored using the restore to alternate folder method detailed in this guide. First, navigate to the folder where you told NovaBACKUP to restore the MS SQL Server database backup to the alternate folder. 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 would need to be copied to the SQL data folder that your installed Microsoft SQL Server version is set up to utilize as the data folder. For example, if using SQL Server 2016 the default data folder for where these files would need to be copied to is "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\". If using SQL Server 2017 the default data folder where the files would need to be copied to is: "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\". If you are using SQL Express 2016 the folder would be "C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\DATA". If you are using SQL Express 2017 the folder would be "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 to the destination folder now. If you do not copy the files first then when you go to use the Attach feature the database will utilize the files in the folder that NovaBACKUP restored to alternate folder and that would not be optimal. So at this point make sure that you have copied the .MDF and .LDF files from the folder where you restored to alternate folder, to the destination SQL data folder, normally that being the default SQL data folder but that choice is up to you. 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 the Test.mdf and Test.ldf files copied to that folder where we are then going to use the Attach function in SQL Management Studio to attach that Test database to attempt to get the full database imported:
- To perform the Attach Database action you will load Microsoft SQL Management Studio that matches your version of Microsoft SQL Server. If that part of SQL Server is NOT installed you will need to install it now, as some versions of SQL Server consider it as an 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:
- NOTE: Prior to attaching the MS SQL database using the Attach method be 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 that is displayed you will browse to the .MDF file, that you copied to the default SQL data folder which your Microsoft SQL Server version utilizes, as mentioned in the prior step. 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 back up this database using NovaBACKUP, make sure that your backup job still contains the selection for this database that you just attached, you would have to exit out of NovaBACKUP if it is currently running to see the newly attached database be listed in the "Microsoft SQL Server" plugin section in the "Backup" tab screen.
- Thank you for reading this tutorial.
Thanks for your attention and stay tuned for more,
Jon Ferraez / NovaStor