public static bool BackupDatabases(out string logText, string sqlServer, params string[] databaseNames) { StringBuilder log = new StringBuilder(); int success = 0, failed = 0; if (databaseNames.Length > 0) { string originalServer = SqlServerHelper.ConnectionString.Server; SqlServerHelper.ConnectionString.Server = sqlServer; for (int i = 0; i < databaseNames.Length; i++) { try { if (SqlServerHelper.BackupDatabase(databaseNames[i])) { log.AppendLine(@"Backup database {0}\{1} successfully.".FormatWith(sqlServer, databaseNames[i])); success++; } else { log.AppendLine(@"Backup database {0}\{1} failed!".FormatWith(sqlServer, databaseNames[i])); failed++; } } catch (Exception ex) { ExceptionHelper.CentralProcess(ex); log.AppendLine(@"Backup database {0}\{1} failed!".FormatWith(sqlServer, databaseNames[i])); log.AppendLine(ExceptionHelper.ExceptionLog(ex)); continue; } } SqlServerHelper.ConnectionString.Server = originalServer; } log.AppendLine("Total databases to be backed up on server '{4}': {0}, Succeeded: {1}, Failed: {2}, Aborted: {3}".FormatWith(databaseNames.Length, success, failed, databaseNames.Length - success - failed, sqlServer)); logText = log.ToString(); return(success == databaseNames.Length); }
/// <summary> /// Backups the databases and disable constraints. /// </summary> /// <exception cref="System.Exception">Backup database failed. Importing stopped.</exception> private void BackupDatabasesAndDisableConstraints(bool isAContinousRun = false) { foreach (string databaseName in targetDatabases) { string[] a = databaseName.Split(new char[] { '\\' }, StringSplitOptions.RemoveEmptyEntries); string dbName = databaseName; string originalServer = SqlServerHelper.ConnectionString.Server; if (a.Length >= 2) { dbName = a[1]; SqlServerHelper.ConnectionString.Server = a[0]; } try { Log.Info("Backup databases {0} on {1}...", dbName, SqlServerHelper.ConnectionString.Server); if (SqlServerHelper.DoesDatabaseExist(dbName)) { if (!isAContinousRun) { if (!SqlServerHelper.BackupDatabase(dbName)) { throw new Exception("Backup database {0} on {1} failed. Importing stopped.".FormatWith(dbName, SqlServerHelper.ConnectionString.Server)); } else { Log.Info("Backuped databases {0} on {1}.", dbName, SqlServerHelper.ConnectionString.Server); } } // Disable all constraints on all tables in the database Log.Info("Disable all constraints on all tables in the database {0} on {1}...".FormatWith(a[1], SqlServerHelper.ConnectionString.Server)); DataTable dt = SqlServerHelper.GetAllUserTableNamesOfDatabase(dbName); for (int i = 0; i < dt.Rows.Count; i++) { string tableName = dt.Rows[i][0].ToString(); string cmdText = "ALTER TABLE {0}.{1}.{2} NOCHECK CONSTRAINT ALL".FormatWith(dbName, "dbo", tableName); try { SqlServerHelper.Execute(cmdText); Log.Info("Disabled {0} constraint(s) on table {1} in the database {2} on {3}.".FormatWith("all", tableName, a[1], SqlServerHelper.ConnectionString.Server)); } catch (SqlServerHelperException ex) { if (ex.InnerException != null && ex.InnerException is System.Data.SqlClient.SqlException && ex.InnerException.Message.StartsWith("Schema change failed on object", StringComparison.InvariantCultureIgnoreCase)) { // try again System.Threading.Thread.Sleep(1000); SqlServerHelper.Execute(cmdText); } else { throw ex; } } } Log.Info("Disabled all constraints on all tables in the database {0} on {1}...".FormatWith(a[1], SqlServerHelper.ConnectionString.Server)); } else { throw new Exception("The database {0} does not exist on {1}!".FormatWith(dbName, SqlServerHelper.ConnectionString.Server)); } } catch { throw; } finally { if (a.Length >= 2) { SqlServerHelper.ConnectionString.Server = originalServer; } } } }