On a recent project, we created a stand-alone application that's primarily meant for one person to use. Since it's a database-driven application, we wanted to add the ability for the user to back up their databases easily. Sounds simple, right? SQL Express databases are just mdf files - simply copy the file to a new location, and it's backed up, right? In theory, yes. In practice, it's not quite that simple. We connect to our database in a user instance, and we auto-attach it, meaning when our connection is closed, it's automatically detached for us - nice and clean. No extra code for us to worry about.

That is, unless you want to back that file up. SQL Server does some things for you that are, under most circumstances, very helpful. For example, connection pooling. You close a connection, but the connection isn't really closed - it's thrown back into the pool to be grabbed later - it definitely improves performance. Luckily, there's a static method on the SqlConnection class that will clear the connections in the pool - SqlConnection.ClearAllPools().

Great! Now we can copy the file, right? Nope. It's in use. As I said earlier, SQL Server Express is kind enough to automatically attach and detach our database for us, but what isn't obvious (but explained here, near the bottom) is that it can take 8-10 minutes for it to be detached - meaning you can't copy it until that time.

To finish this up then, you just clear the pool, detach the database, and back up the file. That's the gist of it, but the details are still gnarly at best. Here's the code we used to get the job done:

string baseConnectionString = "Data Source=.\\SQLExpress;Integrated Security=SSPI;User Instance=true;";

SqlConnection.ClearAllPools();

using (SqlHelper helper = new SqlHelper(baseConnectionString))

{

    string commandText = @"

declare @count int;

select @count = count([name]) from sys.databases where [name] = @DBName;

if @count = 1 

begin

exec sp_detach_db @DBName;

end

";

    List
<SqlParameter> parameters = new List<SqlParameter>();

    parameters.Add(new SqlParameter("@DBName", databaseFile));

    helper.Execute(commandText, CommandType.Text, ref parameters);

}

A few things to note here:

1. When a database is auto-attached to SQL Express, it's name is the file name - path and all. As an aside, since it's a user instance, you can't do much to see what's going on in SQL Management Studio - looking at the list of attached databases doesn't show the auto attached databases, since they are actually attached in a separate user instance.
2. You can't assume the database is attached. The article above says it's released in 8 to 10 minutes, but my experience is that's pretty random - I saw times from one minute up to 10 minutes. Bottom line: check to see if your file is listed in sys.databases, and only try to detach if it is.
3. This is probably the most important one to get this to work. Our connection string doesn't specify a database - just the server information. Then you can detach the database, and your current connection can be held in the (cleared and now) rebuilding connection pool without holding onto the file reference.

Once you've done this, you can just copy the database file to a backup location:

System.IO.FileInfo file = new System.IO.FileInfo(databaseFile);

file.CopyTo(destinationFile, true);

file 
= new System.IO.FileInfo(databaseFilet.Replace(".mdf", "_log.ldf"));

file.CopyTo(destinationFile.Replace(".mdf", "_log.ldf"), true);

We backup the mdf file and the log file, so that's the two lines above.

This is a nice little way for us to backup databases. We also embedded our database in our application, so it can be created on the fly. It worked out well for us, so if you ever need to back up SQL Express databases on the fly, this is how you can do it.