How do I release sql express database?

Questions

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

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.