Does anybody know how to backup SQL Server 2005/2008 database with C# and get the database backup progress?
SQL databases can be backed up while in use but it is important to ensure that your backups are capturing all information with a known RPO and RTO in case of problems.
A backup of data in a complete database (a database backup), a partial database (a partial backup), or a set of data files or filegroups (a file backup).
A transaction log (T-log) backup is the most granular backup type in SQL Server because it backs up the transaction log which only contains the modifications made to the SQL Server database since the last transaction log backup. It's effectively an incremental backup.
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.
SMO has a percentcomplete event.
http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.backuprestorebase.percentcomplete.aspx
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With