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:

USE TestDB
GO
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 ‘VPCW2K8DENALI’.  (Microsoft.SqlServer.SmoExtended)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.1103.9+((SQL_PreRelease).100924-2125+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

——————————
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: File ‘E:Program FilesMicrosoft SQL ServerMSSQL11.DENALIMSSQLDATAMyTestDB.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: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.1103.9+((SQL_PreRelease).100924-2125+)&LinkId=20476
——————————
BUTTONS:
OK
——————————

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 FilesMicrosoft SQL ServerMSSQL11.DENALIMSSQLDATAMyTestDB.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 – https://connect.microsoft.com/SQLServer/feedback/details/668566#details

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.