How do I release sql express database?

Question

I have a simple app that uses an SQL Express 2005 database. When the user closes the app, I want to give the option to back up the database by making a copy in another directory. However, when I try to do it, I get "The process cannot access the file '...\Pricing.MDF' because it is being used by another process." I closed the connection, disposed the connection, set it to nothing, and GC.Collect(), but it makes no difference. My connection string is "Data Source=.\SQLEXPRESS2005;AttachDbFilename=|DataDirectory|\Pricing.mdf;Integrated Security=True; User Instance=True" and I just keep using the same connection throughout. I didn't see where I could detach the database to counter the attach in the connection string.

1 - How do I RELEASE the thing? 2 - Is there a better way than just copying the database? The app is for my husband only, so I will be able to handle it if he actually does need to restore from backup.

Thanks!

Answer 1

There's an example in this post that shows how to back up a SQL Express database within a program.

Answer 2

If this is a single user or application or read-only database, don't use SQL Server Express. This is what SQL Server Compact is for. This will solve your backup problem and simplify deployment as well.

Answer 3

You don't want to copy the mdf directly because SQL keeps most of the changes in the transaction log, take a look at the modified time after running some queries, it doesn't write directly to the file. I noticed this while setting up an rsync job.

Having SQL generate the backup is much safer and more desirable, single-user or multi-user. You can provide a link to a function calling the T-SQL which you can completely automate as far as source db and destination folder:

BACKUP DATABASE [mydatabasename]
TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Scheduled Task Backups\mydatabasename-backup' WITH NOFORMAT, NOINIT,  NAME = N'mydatabasename-Full Data

SQL 2005 had introduced another T-SQL syntax to do this, for the life of me I can't find it. But there are ways to do it through M$$SQL without having the full blown database server.