Getting Started with Adobe After Effects - Part 6: Motion Blur

Upload Image Close it
Select File

Nakul Vachhrajani is a Technical Specialist & Systems development professional with iGATE. He holds a MCTS (SQL Server 2008: Implementation & Maintenance)
Browse by Tags · View All
#SQLServer 305
SQL Server 304
Administration 252
DBA 241
T-SQL 234
#TSQL 232
Development 226
Tips 216
Guidance 148
Best Practices 119

Archive · View All
April 2011 14
March 2012 11
December 2011 11
March 2011 11
December 2012 10
October 2011 10
January 2011 10
September 2013 9
January 2013 9
November 2012 9

SSMS – Database Restore – Physical File Names should default to Logical File Names – MS Connect Case #668566

Jun 23 2011 12:00AM by Nakul Vachhrajani   

SSMS, or SQL Server Management Studio is a wonderful tool, and those who frequent this blog will notice that I tend to try and make day to day task simpler using the SSMS. In a development environment, databases are created and backups restored often. Most developers have rights to the development instance, and sometimes we run into problems with application developers going out of their comfort zone and attempting to be “accidental DBAs”.

This true not only for software, but for the world around us as well. When we undertake transatlantic journeys, it takes a while for us to get used to the tools and technology available because it’s different. I am quite sure that most have caused the untimely death of a costly electronic device because we failed to realize that the US works on a 110V supply, while the UK on a 220-240V supply.

Similarly, not that there is anything wrong in being an “accidental DBA”, but minor product issues do cause a usability annoyance when somebody who is not familiar with the tool (SSMS) attempts to use it.

The scenario

Let’s create a test database on a given SQL Server instance such that the database has at least two data files, and one log file (you can have more, no issues).


Once the database is created, we will fire the following query to confirm the file names and file paths:

SELECT * FROM sys.sysfiles


Now, let’s take a full backup of the database, and move the backup to another server where another instance of Microsoft SQL Server is running. As mentioned in my previous post earlier this week, check the default location of the data files for that instance.





Now, invoke the Restore wizard to attempt a restore of the database. Select the backup file location and check the appropriate backup set in the “General” tab.


Now, navigate to the “Options” tab and notice the default file names provided as part of the restore.


Simply clicking “OK” without modifying these paths will result in an exception:

TITLE: Microsoft SQL Server Management Studio
Restore failed for Server 'VPCW2K8\DENALI'.  (Microsoft.SqlServer.SmoExtended)
For help, click:

System.Data.SqlClient.SqlError: File 'E:\Program Files\Microsoft SQL Server\MSSQL11.DENALI\MSSQL\DATA\MyTestDB.mdf' is claimed by 'NAV_SECONDARY'(3) and 'NAV_DATA'(1). The WITH MOVE clause can be used to relocate one or more files. (Microsoft.SqlServer.Smo)
For help, click:


This exception is coming because we did not modify the default file names. As you can see in the screen-shot, both logical files – NAV_DATA and NAV_SECONDARY have the same physical file location & name – E:\Program Files\Microsoft SQL Server\MSSQL11.DENALI\MSSQL\DATA\MyTestDB.mdf

Simply changing the name to the logical file names (NAV_DATA.mdf and NAV_SECONDARY.mdf) would rectify the issue and the restore will succeed.

Microsoft Connect Case #668566

I had filed a Microsoft Connect bug report here -

I am pleased to announce that the bug has been fixed by Microsoft and will be available in the next public release of Microsoft SQL Server (code named “Denali”). Thank-you, Microsoft!


Until we meet next time,

Be courteous. Drive responsibly.

Tags: Administration, Tips, SQL Server, #SQLServer, Best Practices, Guidance, Installation, DBA, SSMS, Tools and Utilities, Connect Cases,

Nakul Vachhrajani
4 · 36% · 11648


Your Comment

Sign Up or Login to post a comment.

"SSMS – Database Restore – Physical File Names should default to Logical File Names – MS Connect Case #668566" rated 5 out of 5 by 2 readers
SSMS – Database Restore – Physical File Names should default to Logical File Names – MS Connect Case #668566 , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]