Backup and restore of mdf database

Hi,

I've got a following problem: I have a VB 2005 application that uses SQL Server 2005 Express as DB engine. This application has it's own database stored in mdf file, e.g. invoices.mdf with corresponding log file.

I'd like to give users opportunity to backup and restore this DB file from this application (there won't be a system administrator, only end-users). Unfortunatelly after I connect to this database (using user instance of SQL Server), I'm unable to read or copy it, despite the fact, that I close all connections. And I'm sure that all connections are closed.

Database files become accessible for reading after I close the whole application.

I've tried to detach this database using sp_detach_db prcedure, but it can't be done with database I'm connected to.

I will be grateful for any help.

Tomasz

hi Tomasz,

obviously at least 1 connection is still open referencing the database you are dealing with..

try changing the database context to the master system database (that can be done in .Net via the workingConnection.ChangeDatabase("master") statement...)

a workaround to "orphaned" connections could even be an alter database statement to close all pending connections, like

USE master;

GO

ALTER DATABASE dbName

SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

GO-- and the counterpart is

ALTER DATABASE dbName

SET MULTI_USER WITH ROLLBACK IMMEDIATE;

but I'd prefer to find and close all pending connections...

regards

|||

We just had a couple threads about this topic in the forum over the last week. I've summarized how to use SMO to handle Backup and Restore of User Instance database in my blog. Backup should work with connection open but restore is not so forgiving.

Mike

|||

Hi,

thanks for this advice, I will try today to implement backup/restore approach according to the blog.

I'm 100% sure that I close all connections to the DB. I've made a very simple application which just connects to the database and immediatly closes this one an single connection. After that mdf file remains locked until I close the application. It seems, that there is some "stealth" connection, which I can't close directly. You can replicate this behaviour by creating a new application which on start does:

Dim lCon As New SqlClient.SqlConnection(sConnStr)

lCon.Open()

lCon.Close()

lCon.Dispose() 'even that won't help

And then try to copy mdf file to other location while this application is still running.

greetings

Tomasz

|||

Hi,

I've got the same problem as Tomasz. I read all that was written and I was wondering if there isn't a command capable of closing all connection to the database file (.mdf). I just need to upload it to a remote location not restore it.

Thanks in advance