18

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
RPS
  • 807
  • 2
  • 13
  • 20

2 Answers2

33

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.

Christian Hayter
  • 30,581
  • 6
  • 72
  • 99
  • I've been looking for something like this for quite a while... is the Restore version of this much different? What part of the info message says what the actual progress percentage is? – Patrick Sep 19 '11 at 15:52
  • 2
    The restore command is very similar, e.g. `"restore database {0} from disk = {1} with replace, stats = 1"`. As for the percentage value, personally I just use a regex on the message, e.g. `Regex.Match(e.Message, "(\d{1,3}) percent")`. – Christian Hayter Sep 20 '11 at 07:21
2

SMO has a percentcomplete event.

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.backuprestorebase.percentcomplete.aspx

Gratzy
  • 9,164
  • 4
  • 30
  • 45