SQL Server Restore Database Error in SQL Server Management Studio

SQL Server 2017 Restore Database Error in SSMS

By: Rajendra Gupta | Comments (5) | Related: > 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:

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.

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:

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:

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

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

We will explore more about SQL Server 2017 in future tips.
Explore SQL Server 2017 preview.
Read more about SQL Server vNext Linux Tips.

About the author

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, August 17, 2021 - 2:23:26 AM - Kurt Mühlemann

Hi Amir
Your blog saved me. I had a similar message, but without reference to a DVD drive. I thought about it again and again. Then I asked myself if I had any "fishy" drives. In my network, I have two NAS devices. I access them by Netdrive3 as virtual drives! Once I disconnected the virtual drives, I could restore my database.
After the database was restored, I could reconnect the virtual drives and everything remains fine.
Thanks for taking the effort to write about the issue!

Thursday, February 11, 2021 - 1:07:47 AM - Ganapathy Boopalan	Back To Top (88217)
It's nice !!!!

Friday, August 2, 2019 - 4:19:20 PM - Gabriel Lawrence

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

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

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