Friday, January 23, 2009

SQL Server Management Studio: Unable to open database because the physical files have been deleted

I use my laptop for development and I often download sample .NET projects which have database files which need to be loaded into my SQL Server instance. But when I deleted the sample project, I ended up deleting the MDF and LDF files that were part of the database in that project. After that I was unable to browse all the other databases that were on my laptop using SQL Server Management Studio 2008.

The error message that would pop up when I used to connect to my laptop’s SQL Server instance and then browse the database node is shown below:

------------------------------ ------------------------------

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

------------------------------------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------ ------------------------------

Unable to open the physical file "xxxxx.mdf". Operating system error 3: "3(The system cannot find the path specified.)".
Database 'xxxxxxxx.MDF' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
File activation failure. The physical file name "xxxxxxxx.ldf" may be incorrect. (Microsoft SQL Server, Error: 5120)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.1600&EvtSrc=MSSQLServer&EvtID=5120&LinkId=20476

------------------------------ ------------------------------

To get around this problem, do the following:

1. In the connection dialog, click on the “Options” button. This will display tabs at the top.

2. Click on the “Connection Properties” tab.

3. In the drop down “Connect to database”, click on “Browse Server…”

image

4. Select a database that you know exists on your computer.

image

5. Click Ok and the Connect. You should be able to successfully connect to the database and browse its objects.

I think the problem arises from the fact that SQL Server Management Studio remembers the last database that was used for a connection and hence continues to try and connect to that database.

No comments: