CODEWITHSUNDEEP
c#sqlsql-serverlocaldb
Clint
Clint

Reputation: 2793

Cannot find file specified when dropping LocalDb database if .mdf was deleted

I am trying to write an application that will create a local database if it's not found in the application's folder. I run this query after deleting the .mdf

IF EXISTS (SELECT * FROM sys.databases WHERE name = N'Test_db')
BEGIN 
     DROP DATABASE Test_db 
END

CREATE DATABASE Test_db 
ON PRIMARY (NAME=Test_db, FILENAME='...\Test_db.mdf')

My command.ExecuteNonQuery() throws an exception, even though it drops the database and creates a new one. The error comes from the DROP DATABASE part of the command.

Additional information: Unable to open the physical file "...\Test_db.mdf". Operating system error 2: "2 (The system cannot find the file specified.)".

File activation failure. The physical file name "...\Test_db_log.ldf" may be incorrect.

I found this question, but it has no solution to the problem.

Upvotes: 2

Views: 1003

Answers (2)

Clint
Clint

Reputation: 2793

The solution to the problem was to sp_detach_db because it removes the database from the server without deleting files from the file system

EXEC sp_detach_db 'Test_db'

Upvotes: 2

Peter Bill
Peter Bill

Reputation: 528

If you are worried about the file being deleted, try File.Exists

if (File.Exists(pathname))
{
    // Execute your SQL
}
else
{
    // Error processing
}

Upvotes: 0

Related Questions