SQL Server 2017 Restore Database Error in SSMS
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.
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:
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:
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:
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:
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.
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 backup file (*.bak) from the location on disk as shown below:
We can see the backup file is now showing in the backup set to restore.
Click on to restore the database and we can see that the database restoration is now completed successfully.
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.
- 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
View all my tips