18

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

0

2 Answers 2

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.

Sign up to request clarification or add additional context in comments.

2 Comments

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?
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").
2

SMO has a percentcomplete event.

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.