c# - Backup SQL Server Database with progress

Question

Does anybody know how to backup SQL Server 2005/2008 database with C# and get the database backup progress?

Answer 1

Here's a pure ADO.NET solution, if you are having difficulty installing SMO/SQLDMO on the target machine (it's a pain in the behind, best avoided if you can).

public void BackupDatabase(SqlConnection con, string databaseName, string backupName, string backupDescription, string backupFilename) {
    con.FireInfoMessageEventOnUserErrors = true;
    con.InfoMessage += OnInfoMessage;
    con.Open();
    using(var cmd = new SqlCommand(string.Format(
        "backup database {0} to disk = {1} with description = {2}, name = {3}, stats = 1",
        QuoteIdentifier(databaseName),
        QuoteString(backupFilename),
        QuoteString(backupDescription),
        QuoteString(backupName)), con)) {
        cmd.ExecuteNonQuery();
    }
    con.Close();
    con.InfoMessage -= OnInfoMessage;
    con.FireInfoMessageEventOnUserErrors = false;
}

private void OnInfoMessage(object sender, SqlInfoMessageEventArgs e) {
    foreach(SqlError info in e.Errors) {
        if(info.Class > 10) {
            // TODO: treat this as a genuine error
        } else {
            // TODO: treat this as a progress message
        }
    }
}

private string QuoteIdentifier(string name) {
    return "[" + name.Replace("]", "]]") + "]";
}

private string QuoteString(string text) {
    return "'" + text.Replace("'", "''") + "'";
}

The stats = 1 clause tells SQL Server to emit severity 0 messages at the specified percentage interval (in this case 1%). The FireInfoMessageEventOnUserErrors property and InfoMessage event ensure that the C# code captures these messages during execution rather than only at the end.