BUG: Can Restore a Database when INVALID characters specified in file name/path.

Question

I restored a database a few months ago on SQL Server 2008 R2 using the GUI in management Studio, and not thinking correctly I accidentally put a colon (:) in the filename of the database.

The odd thing is that Management Studio happily let me do it.  It restore the database with no complaints, and I was able to use the database without errors.

What I did notice is that when I looked at the files page on the database properties, it cut off the file name at where I specified the colon.
Also, when I attempted to look for the actual database files, I was unable to find them anywhere.

I looked to see if anyone else had encountered this problem and I found the following on MS Connect, however it has been resolved as Not Reproducible with no response back to the person that logged it.
https://connect.microsoft.com/SQLServer/feedback/details/740275/restore-database-with-invalid-characters-in-filename-allowed

Has anyone else encountered this problem???

Answer 1

what if you run sp_helpdb?

I checked this on SQL2012, but there it doesn't allow you to put a colon in the filename when restoring the db.

---

Geert Vanhove DCOD ------ http://geertvanhove.wordpress.com/ ----------- Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

Answer 2

I restored a database a few months ago on SQL Server 2008 R2 using the GUI in management Studio, and not thinking correctly I accidentally put a colon (:) in the filename of the database.

The odd thing is that Management Studio happily let me do it.  It restore the database with no complaints, and I was able to use the database without errors.

What I did notice is that when I looked at the files page on the database properties, it cut off the file name at where I specified the colon.
Also, when I attempted to look for the actual database files, I was unable to find them anywhere.

Hi Shaggee,

I just reproduce the scenario. I restored a database use GUI Management Studio. I add a colon in the middle of the filename of the database. The result is the same as yours.
I think you could restore the database from the latest backup. Use the right filename without the colon. Then you could find the actual database files in the default folder.

TechNet Subscriber Support
If you are TechNet Subscriptionuser and have any feedback on our support quality, please send your feedback here.

Thanks,
Maggie

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

Answer 3

I know that things would be ok if I didn't use the colon (:), but I accidentally did that time.  It was actually just a temporary testing database, so it has long since been deleted, however, the GUI, or any other method (cmd line/sqlcmd/...), should never let anyone do it.

Once I realized what I had done, I was actually afraid that what I had done would corrupt the hard drive since the colon is used to signify the drive letter.
I was thinking that perhaps it would have created the database files in some invalid area of the drive that could potentially cause the system to malfunction and crash.  Since I was never able to find the actual files anywhere, I have no proof that my concerns were not true!!

The strangest thing was that the connect article was closed as being "Not Reproducible", which you have proven is completely incorrect.

I am glad to hear that this is apparently fixed in SQL2012, but what about the other versions?

Thanks,

Mark

Answer 4

Hi Shaggee,

I tested in SQL Server 2012. The result is different with the result in SQL Server 2008 R2. I think SQL Server 2012 has fixed the issue.

I restore a database in SQL Server 2012 Management Studio. In the Destination database, I add a colon (:) in the middle of the name. I could restore the database successfully. After that, I could use the database without error. In addition, the .mdf file and .ldf file are both in the C:\Program Files\ Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA in my server.

You could try to apply the latest SQL Server 2008 R2 Service Pack to see if it could solve the issue.

TechNet Subscriber Support
If you are TechNet Subscriptionuser and have any feedback on our support quality, please send your feedback here.

Thanks,
Maggie

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

Answer 5

It seems odd that you could do it, but Geert Vanhove could not.

It is nice to know that the DB files appear to be placed in the default location instead of some odd unknown location, but really, I don't think it should allow you to continue if anyone does that (put a colon in the name).

I did try with the latest service pack and with the post SP hotfixes that were available at the time.

Since I do not want to even have the potential to cause problems for our servers, I would need to setup a test machine to try the latest hotfixes.

I do not believe that they have released a new service pack for SQL 2008 this year.
Also, from what I can tell, the problem exists in SQL 2005 as well.

I appreciate all the help you have provided in this matter.  I just wish Microsoft would update that connect article to say why it was marked as not reproducible.  Especially since that article has no MS responses whatsoever. (https://connect.microsoft.com/SQLServer/feedback/details/740275/restore-database-with-invalid-characters-in-filename-allowed)

Thanks,
Mark

Answer 6

Hi Shaggee,

Thank you for your post.

For submitting a feedback to Microsoft such issues, You could try to submit it to the Microsoft Connect again at this link https://connect.microsoft.com/SQLServer/Feedback.

This connect site will serve as a connecting point between you and Microsoft, and ultimately the large community for you and Microsoft to interact with. Your feedback enables Microsoft to offer the best software and deliver superior services, meanwhile you can learn more about and contribute to the exciting projects on Microsoft Connect.

Thanks,
Maggie

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

Answer 7

I also would like to know why my connect article was closed as not reproducable, but have not had any response from Microsoft as to why this happened.

---

Seradex

Answer 8

That is unfortunate that I will need to create a new connect article for essentially the same thing as the other user already posted.

Also, if it still lets the user do it in SQL 2012, then I do not agree that this is "fixed", although it may be behaving much better than it did in the past.

Thanks,

Mark.

(I see the original connect poster also wants to know why his connect posting was closed without any comments from MS.)

Answer 9

Any progress?

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 10

Well, at least the Connect item is once again marked "Active". - RLF