Tuesday, November 27, 2012

How to backup a LocalDB database under an MVC 4 sytem

UPDATE: EntityFramework 6 has lots of Resiliency enhancements, but one of the side effects is that this needs a tweak to keep EF from spinning up a transaction around the SQL statement.  Essentially, you have to call ExecuteSqlCommand and request that it does NOT ensure the a transaction exists with the first parameter of TransactionalBehavior.DoNotEnsureTransaction.  If still running EF 5 or below, omit that argument. 

I have a smallish MVC 4 site with a database. In this case it isn't worthy of a dedicated SQL Server, so I decided to try out the new LocalDb database feature.

While the database isn't particularly mission critical, I would like to be able to easily back it up on demand to allow some level of disaster recovery.

So, without further adéu, I give you:

namespace SomeSimpleProject.Controllers
{
    [Authorize(Roles="Admin")]
    public class BackupController : Controller
    {
        public ActionResult BackupDatabase()
        {
            var dbPath = Server.MapPath("~/App_Data/DBBackup.bak");
            using (var db = new DbContext())
            {
                var cmd = String.Format("BACKUP DATABASE {0} TO DISK='{1}' WITH FORMAT, MEDIANAME='DbBackups', MEDIADESCRIPTION='Media set for {0} database';"
                    , "YourDB", dbPath);
                db.Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction, cmd);
            }
       
            return new FilePathResult(dbPath, "application/octet-stream");
        }
    }
}