How do I release sql express database?

Questions

I have a simple app thatuses an SQL Express 2005 database. When the user closes the app, I wantto give the option to back up the database by making a copy in anotherdirectory. However, when I try to do it, I get "The process cannotaccess the file '.../Pricing.MDF' because it is being used by anotherprocess." I closed the connection, disposed the connection, set it tonothing, and GC.Collect(), but it makes no difference. My connectionstring is "DataSource=./SQLEXPRESS2005;AttachDbFilename=|DataDirectory|/Pricing.mdf;IntegratedSecurity=True; User Instance=True" and I just keep using the sameconnection throughout. I didn't see where I could detach the databaseto counter the attach in the connection string.

1 - How do I RELEASE the thing? 2 - Is there a better way than justcopying the database? The app is for my husband only, so I will be ableto 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 ofthe changes in the transaction log, take a look at the modified timeafter running some queries, it doesn't write directly to the file. Inoticed 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 callingthe T-SQL which you can completely automate as far as source db anddestination 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 lifeof me I can't find it. But there are ways to do it through M$$SQLwithout having the full blown database server.