Does anybody know how to backup SQL Server 2005/2008 database with C# and get the database backup progress?
2 Answers
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.
2 Comments
Patrick
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?
Christian Hayter
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").SMO has a percentcomplete event.