Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server 2017 Restore Database Error in SSMS


By:   |   Last Updated: 2017-06-26   |   Comments (3)   |   Related Tips: More > SQL Server 2017

Problem

SQL Server vNext represents a major step towards making SQL Server a platform that gives you choices of development languages, data types, on-premises and in the cloud, and across operating systems by bringing the power of SQL Server to Linux, Linux-based Docker containers, and Windows. In my previous tip we introduced SQL Server 2017.  As a DBA, we restore SQL Server databases with either Management Studio or with T-SQL commands. While restoring a database using Management Studio in SQL Server 2017, I faced a strange error, so in this tip I will explain the root cause and outline my solution.

Solution

Introduction to SQL Server 2017

Microsoft announced the first Community Technology Preview (CTP 1.0) of SQL Server vNext on November 16, 2016, it runs on Windows, Linux (Redhat, SUSE, Ubuntu), Docker and MacOS. SQL Server vNext also includes the features added in SQL Server 2016 Service Pack 1. Currently SQL Server vNext is in preview stage and can be downloaded as a free evaluation version for 180 days from the Microsoft website.

On April 2017, Microsoft released Community Technology Preview (CTP) 2.0 of SQL Server vNext. Microsoft Officially announced that this Community Technology Preview (CTP) 2.0 of SQL Server vNext will be called as SQL Server 2017.

We now have a production-quality preview of SQL Server 2017, and it is available on both Windows and Linux. SQL Server 2017 preview can be downloaded from link.

Restore Database backup using SQL Server Management Studio

To restore a database from a backup file we used to follow the below steps:

  • Connect to the instance of the Microsoft SQL Server database Engine and in Object Explorer, click on the Server Name to expand the Server Tree
  • Right-click the databases, and then click Restore Database
  • Click on the device to select the backup file and then click Add

After I click on the Add button, I received the following error:

SQL Server 2017 Restore Error

This is a strange error because when I look at my computer drives, Drive E is the "DVD - RW Drive" which is not accessible.  SQL Server is generating an error with this drive and I have never faced this issue in previous versions of SQL Server.

SQL Server drives and devices

While investigating this error, I found that in SQL Server 2017 SMO switched to using sys.dm_os_enumerate_fixed_drives instead of the old xp_fixeddrives extended stored procedure to get free space information in this dialog box sequence.

Previously we would get free space from the XP_FIXEDDRIVES extended stored procedure as shown below:

Results from SQL Server XP_FIXEDDRIVES

sys.dm_os_enumerate_fixed_drives

In SQL Server 2017, you can use the new DMV sys.dm_os_enumerate_fixed_drives to identify free disk space. This is the replacement of the xp_fixeddrives extended stored procedure.

So if I run a simple query against this DMV, I get the below output:

SQL Server sys.dm_os_enumerate_fixed_drives DMV output

We get the details about the free space for the drives however the total space is not included. If we go to the Message tab we get the following output:

SQL Server sys.dm_os_enumerate_fixed_drives DMV messages outout

This is the same error which was reported above from SSMS while restoring the database.

To resolve the issue, we need to disable the DVD - RW drive from the Device Manager by navigating to Control panel > Device Manager as shown below:

Disable the DVD - RW Drive

The DVD - RW Drive is now disabled as shown below:

Disable the DVD - RW Drive

Now once we have disabled the DVD - RW drive, let's run a simple query again against the DMV and review the output:

SQL Server sys.dm_os_enumerate_fixed_drives DMV outout

We can see the output is listing another virtual CD drive which is accessible along with the drive details. If we look at the Message tab we don't see any errors reported.

Output from sys.dm_os_enumerate_fixed_drives SQL Server DMV

Now if I work through the steps in SSMS to restore the database again, we do not get an error after clicking on the Add button.

Select the database to restore in SQL Server 2017 Management Studio

Select the backup file (*.bak) from the location on disk as shown below:

Select backup devices in SQL Server 2017 Management Studio

We can see the backup file is now showing in the backup set to restore.

Restore Database in SQL Server 2017 Management Studio

Click on to restore the database and we can see that the database restoration is now completed successfully.

Successful SQL Server Database Restore in Management Studio 2017

Database restoration is now completed in SSMS without any error. This bug should be resolved by Microsoft in a future release of the SQL Server 2017. However we can do the restoration with this workaround without any issues and explore the new SQL Server 2017 with its new and existing features.

Next Steps


Last Updated: 2017-06-26


get scripts

next tip button



About the author
MSSQLTips author Rajendra Gupta Rajendra is a Consultant DBA with 9+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, August 02, 2019 - 4:19:20 PM - Gabriel Lawrence Back To Top

Fantastic!  Thank you for posting.  Mine wasn't showing an error message, it just locked up after the second click in the 'Restore Database' window.  I had no clue it was a ROM drive issue.  Should I be surprised that the engineers at Microsoft missed this?  Should I be even more surprised that it's August 2nd, 2019 and this STILL isn't fixed...in fact, it's worse because it's not showing an error!!

UGH!

Again, thank you for posting this!!


Friday, July 21, 2017 - 8:10:47 AM - rajendra Back To Top

Hi  Amir- You can go through the logs windows , SQL and  also can try to capture more information using the extended events. this will give you some clue where to look at.

 


Tuesday, July 18, 2017 - 11:56:16 AM - Amir Back To Top

Hello Rajendra, I was hvaing a really difficult time using the SSMS v17 due to this issue. But your post helped me great deal and I was able to fix the issue on my machine as it was complaining about the Floppy Drive A:\ . Just wanted to know, how did you even find out where to look at, I mean l would really like to learn to troubleshoot like the way you did.

Any guidance would be greatly appreciated.

Regards

Amir


Learn more about SQL Server tools