How to free up sql express database?
I have a simple application that uses a SQL Express 2005 database. When the user exits the application, I want to provide a backup of the database by making a copy in a different directory. However, when I try to do this, I get "The process cannot access the file" ... \ Pricing.MDF because it is being used by another process. "I closed the connection, established a connection, set it to zero and GC.Collect (), but it doesn't matter. My connection string is "Data source =. \ SQLEXPRESS2005; AttachDbFilename = | DataDirectory | \ Pricing.mdf; Integrated Security = True; Custom Instance = True "and I just keep using the same connection. I haven't seen where I can detach the database to meet the attachment in the connection string.
1 - How can I release a thing? 2 - Is there a better way than just copying the database? The app is only for my husband, so I can handle it if he really needs to be restored from a backup.
Thank!
source to share
You don't want to copy the mdf directly because SQL saves most of the changes in the transaction log, look at the modified time after running some queries, it is not written directly to the file. I noticed this when setting up an rsync job.
Having an SQL-generated backup is much safer and desirable, single-user or multi-user. You can provide a link to a function that calls T-SQL, which you can fully automate down to the source database 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 introduced a different T-SQL syntax to do this because life can't find me. But there are ways to do it via M $$ SQL without having a full blown database server.
source to share