/// <summary>
/// database backup
/// </summary>
/// <returns>Backup is successful</returns>
public bool DbBackup()
{
CreatePath();
SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
try
{
oSQLServer.LoginSecure = false;
oSQLServer.Connect(server, uid, pwd);
oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
SQLDMO.BackupSink_PercentCompleteEventHandler pceh = new SQLDMO.BackupSink_PercentCompleteEventHandler(Step);
oBackup.PercentComplete += pceh;
oBackup.Database = database;
oBackup.Files = backPath;
oBackup.BackupSetName = database;
oBackup.BackupSetDescription = "Database Backup";
oBackup.Initialize = true;
oBackup.SQLBackup(oSQLServer);
return(true);
}
catch (Exception ex)
{
return(false);
throw ex;
}
finally
{
oSQLServer.DisConnect();
}
}
private void DBForm_Load(object sender, EventArgs e)
{
backupProgress = new SQLDMO.BackupSink_PercentCompleteEventHandler(Backup_PercentComplete);
restoreProgress = new SQLDMO.RestoreSink_PercentCompleteEventHandler(Restore_PercentComplete);
DataTable dt = BackupRestore.LoadBackupHistory();
dataGridView1.DataSource = dt;
}
public static void CancelDbBackup(SQLDMO.Backup oBackup, SQLDMO.BackupSink_PercentCompleteEventHandler Backup_PercentComplete)
{
try
{
oBackup.Abort();
oBackup.PercentComplete -= Backup_PercentComplete;
}
catch (Exception e)
{
TopFashion.WriteLog.CreateLog("数据库操作", "CancelDbBackup", "error", "取消数据库备份失败：" + e.Message);
}
}
public static void CancelDbBackup(SQLDMO.Backup oBackup, SQLDMO.BackupSink_PercentCompleteEventHandler Backup_PercentComplete)
{
try
{
oBackup.Abort();
oBackup.PercentComplete -= Backup_PercentComplete;
}
catch (Exception e)
{
MessageBox.Show("取消数据库备份失败：" + e.Message);
}
}
private void btnBackUp_Click(object sender, EventArgs e)
{
if (textBoxLJBK.Text == "")
{
MessageBox.Show("请确定数据库备份地址", "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
try
{
oSQLServer.LoginSecure = false;
//下面设置登录sql服务器的ip,登录名,登录密码
oSQLServer.Connect(strDataBaseAddr, strDataBaseUser, strDataBasePass);
oBackup.Action = 0;
//下面两句是显示进度条的状态
SQLDMO.BackupSink_PercentCompleteEventHandler pceh = new SQLDMO.BackupSink_PercentCompleteEventHandler(Step2);
oBackup.PercentComplete += pceh;
//数据库名称:
oBackup.Database = strDataBaseName;
//备份的路径
oBackup.Files = textBoxLJBK.Text;
//备份的文件名
oBackup.BackupSetName = "BUSINESS";
oBackup.BackupSetDescription = "数据库备份";
oBackup.Initialize = true;
oBackup.SQLBackup(oSQLServer);
MessageBox.Show("备份成功！", "提示");
}
catch
{
MessageBox.Show("备份失败！", "提示");
}
finally
{
oSQLServer.DisConnect();
toolStripProgressBar1.Value = 0;
}
}
/// <summary>
/// SQL数据库备份
/// </summary>
/// <param name="path">备份到的路径< /param>
/// <param name="Backup_PercentComplete">进度</param>
/// <param name="oBackup">数据库备份服务对象</param>
/// <param name="remark">备份备注</param>
public static bool SQLDbBackup(string path, SQLDMO.BackupSink_PercentCompleteEventHandler Backup_PercentComplete, out SQLDMO.Backup oBackup, string remark = null)
{
SqlConnectionStringBuilder connStrBuilder = new SqlConnectionStringBuilder(TopFashion.Configs.ConnString);
string ServerIP = connStrBuilder.DataSource;
string LoginUserName = connStrBuilder.UserID;
string LoginPass = connStrBuilder.Password;
string DBName = connStrBuilder.InitialCatalog;
string dir = path + "\\" + DBName;
dir = dir.Replace("\\\\", "\\");
if (!Directory.Exists(dir))
{
Directory.CreateDirectory(dir);
}
string locale = string.Empty;
string username = string.Empty;
string password = string.Empty;
string authority = string.Empty;
string RemoteAuth = TopFashion.Configs.RemoteAuth;
if (!string.IsNullOrEmpty(RemoteAuth))
{
string[] ra = RemoteAuth.Split("|".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
if (ra.Length == 4)
{
locale = ra[0];
username = ra[1];
password = ra[2];
authority = ra[3];
}
}
System.Management.ManagementScope scope = new System.Management.ManagementScope("\\\\" + ServerIP + "\\root\\cimv2", new System.Management.ConnectionOptions(locale, username, password, authority, System.Management.ImpersonationLevel.Impersonate, System.Management.AuthenticationLevel.Default, true, null, TimeSpan.MaxValue));
if (ServerIP == "." || ServerIP == "(local)" || ServerIP == "127.0.0.1")
{
}
else
{
int i = WmiShareRemote.CreateRemoteDirectory(scope, Path.GetDirectoryName(dir), Directory.GetParent(dir).FullName);
}
string DBFile = DBName + DateTime.Now.ToString("yyyyMMddHHmm");
string filename = dir + "\\" + DBFile + ".bak";
if (!File.Exists(filename))
{
try
{
FileStream fs = File.Create(filename);
fs.Close();
}
catch (Exception e)
{
TopFashion.WriteLog.CreateLog("数据库操作", "SQLDbBackup", "error", "无法创建 [" + filename + "] 数据库备份文件！" + Environment.NewLine + e.Message);
}
}
if (ServerIP == "." || ServerIP == "(local)" || ServerIP == "127.0.0.1")
{
}
else
{
bool flag = WmiShareRemote.WmiFileCopyToRemote(ServerIP, username, password, dir, "DatabaseBackup", "数据库备份集", null, new string[] { filename }, 0);
}
oBackup = new SQLDMO.BackupClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
try
{
oSQLServer.LoginSecure = false;
oSQLServer.Connect(ServerIP, LoginUserName, LoginPass);
oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
oBackup.PercentComplete += Backup_PercentComplete;
oBackup.Database = DBName;
oBackup.Files = @"" + string.Format("[{0}]", filename) + "";
oBackup.BackupSetName = DBName;
oBackup.BackupSetDescription = "备份集" + DBName;
oBackup.Initialize = true;
oBackup.SQLBackup(oSQLServer);//这里可能存在问题：比如备份远程数据库的时候，选的路径path却是本地的，恰好是远程服务器上不存在的目录
TopFashion.SQLDBHelper SqlHelper = new TopFashion.SQLDBHelper();
SqlHelper.ExecuteSql("insert into Record (DB, Path, Remark) values ('" + DBName + "', '" + filename + "', '" + remark + "')", true);
return(true);
}
catch (Exception e)
{
TopFashion.WriteLog.CreateLog("数据库操作", "SQLDbBackup", "error", "备份时出错：" + e.Message);
}
finally
{
oSQLServer.DisConnect();
}
return(false);
}
/// <summary>
/// 备份指定数据库文件
/// </summary>
/// <param name="strDbName">数据库名称</param>
/// <param name="strFileName">备份数据库的路径</param>
/// <param name="strServerName">服务器名称</param>
/// <param name="strUserName">用户名</param>
/// <param name="strPassword">密码</param>
/// <param name="prosBar">进度条</param>
/// <returns></returns>
public static bool BackUPDB(string strDbName, string strFileName, string strServerName, string strUserName, string strPassword, ProgressBar bar)
{
#region [ 判断磁盘剩余空间是否可进行备份 ]
DBAcess dba = new DBAcess();
DataSet tmpds = dba.GetDataSet("exec sp_spaceused");
if (tmpds.Tables.Count > 0 && tmpds.Tables[0] != null)
{
// 得到数据库大小
string tmpStr = tmpds.Tables[0].Rows[0]["database_size"].ToString();
int dbSize = Convert.ToInt32(tmpStr.Substring(0, tmpStr.LastIndexOf(".")));
// 获取磁盘剩余空间大小
try
{
DriveInfo d = new DriveInfo(strFileName.Substring(0, 1));
int diskSize = Convert.ToInt32(d.AvailableFreeSpace / 1024 / 1024);
if (diskSize < dbSize)
{
MessageBox.Show("备份数据库所需空间不能小于" + dbSize + "M\r\n磁盘[" + d.Name + "]存储空间过小,无法备份");
return false;
}
}
catch (Exception)
{
MessageBox.Show("路径不正确");
return false;
//throw;
}
}
#endregion
pBar = bar;
string strTmp = "";
string tmpPath = strFileName.Substring(0, strFileName.LastIndexOf("\\")).ToString();
int isEmpty = tmpPath.IndexOf(" ");
SQLDMO.SQLServer svr = null;
try
{
svr = new SQLDMO.SQLServerClass();
// 连接到数据库
svr.Connect(strServerName, strUserName, strPassword);
SQLDMO.Backup bak = new SQLDMO.BackupClass();
bak.Action = 0;
bak.Initialize = true;
#region 进度条处理
if (pBar != null)
{
pBar.Visible = true;
SQLDMO.BackupSink_PercentCompleteEventHandler pceh = new SQLDMO.BackupSink_PercentCompleteEventHandler(Step);
bak.PercentComplete += pceh;
}
#endregion
#region [ 文件夹名称中有空格: 备份前的处理 ]
// 文件夹不存在时自动创建
if (!Directory.Exists(tmpPath))
{
Directory.CreateDirectory(tmpPath);
}
// 文件夹名称 中有空格 备份文件路径设置为根目录的临时文件夹tmpBackup中
if (isEmpty > 1 && strFileName.Substring(4).LastIndexOf("\\") > 1)
{
strTmp = strFileName.Substring(0, 1).ToString() + ":\\tmp_backup.kj";
}
else
{
strTmp = strFileName;
}
#endregion
// 数据库的备份的名称及文件存放位置
bak.Files = strTmp;
bak.Database = strDbName;
// 备份
bak.SQLBackup(svr);
}
catch (Exception err)
{
if (SqlErrRepair(err.Message.ToString()))
{
BackUPDB(strDbName, strFileName, strServerName, strUserName, strPassword, pBar);
return true;
}
return false;
//MessageBox.Show("备份数据库失败");
}
finally
{
if (svr != null)
{
svr.DisConnect();
}
#region [ 文件夹名称中有空格: 备份完成后的处理 ]
// 文件夹名称 中有空格 将备份的文件移动到用户指定的文件夹并将临时目录删除
if (isEmpty > 1 && strFileName.Substring(4).LastIndexOf("\\") > 1)
{
// 文件存在则替换
if (File.Exists(strFileName.Substring(strFileName.LastIndexOf("\\") + 2)))
{
File.Delete(strFileName.Substring(strFileName.LastIndexOf("\\") + 2));
}
File.Move(strTmp, strFileName);
}
#endregion
}
return true;
}
/// <summary>
/// 备份指定数据库文件
/// </summary>
/// <param name="strDbName">数据库名称</param>
/// <param name="strFileName">备份数据库的路径</param>
/// <param name="strServerName">服务器名称</param>
/// <param name="strUserName">用户名</param>
/// <param name="strPassword">密码</param>
/// <param name="prosBar">进度条</param>
/// <returns></returns>
public static bool BackUPDB(string strDbName, string strFileName, string strServerName, string strUserName, string strPassword, ProgressBar bar)
{
#region [ 判断磁盘剩余空间是否可进行备份 ]
DBAcess dba = new DBAcess();
DataSet tmpds = dba.GetDataSet("exec sp_spaceused");
if (tmpds.Tables.Count > 0 && tmpds.Tables[0] != null)
{
// 得到数据库大小
string tmpStr = tmpds.Tables[0].Rows[0]["database_size"].ToString();
int dbSize = Convert.ToInt32(tmpStr.Substring(0, tmpStr.LastIndexOf(".")));
// 获取磁盘剩余空间大小
try
{
DriveInfo d = new DriveInfo(strFileName.Substring(0, 1));
int diskSize = Convert.ToInt32(d.AvailableFreeSpace / 1024 / 1024);
if (diskSize < dbSize)
{
MessageBox.Show("备份数据库所需空间不能小于" + dbSize + "M\r\n磁盘[" + d.Name + "]存储空间过小,无法备份");
return(false);
}
}
catch (Exception)
{
MessageBox.Show("路径不正确");
return(false);
//throw;
}
}
#endregion
pBar = bar;
string strTmp = "";
string tmpPath = strFileName.Substring(0, strFileName.LastIndexOf("\\")).ToString();
int isEmpty = tmpPath.IndexOf(" ");
SQLDMO.SQLServer svr = null;
try
{
svr = new SQLDMO.SQLServerClass();
// 连接到数据库
svr.Connect(strServerName, strUserName, strPassword);
SQLDMO.Backup bak = new SQLDMO.BackupClass();
bak.Action = 0;
bak.Initialize = true;
#region 进度条处理
if (pBar != null)
{
pBar.Visible = true;
SQLDMO.BackupSink_PercentCompleteEventHandler pceh = new SQLDMO.BackupSink_PercentCompleteEventHandler(Step);
bak.PercentComplete += pceh;
}
#endregion
#region [ 文件夹名称中有空格: 备份前的处理 ]
// 文件夹不存在时自动创建
if (!Directory.Exists(tmpPath))
{
Directory.CreateDirectory(tmpPath);
}
// 文件夹名称 中有空格 备份文件路径设置为根目录的临时文件夹tmpBackup中
if (isEmpty > 1 && strFileName.Substring(4).LastIndexOf("\\") > 1)
{
strTmp = strFileName.Substring(0, 1).ToString() + ":\\tmp_backup.kj";
}
else
{
strTmp = strFileName;
}
#endregion
// 数据库的备份的名称及文件存放位置
bak.Files = strTmp;
bak.Database = strDbName;
// 备份
bak.SQLBackup(svr);
}
catch (Exception err)
{
if (SqlErrRepair(err.Message.ToString()))
{
BackUPDB(strDbName, strFileName, strServerName, strUserName, strPassword, pBar);
return(true);
}
return(false);
//MessageBox.Show("备份数据库失败");
}
finally
{
if (svr != null)
{
svr.DisConnect();
}
#region [ 文件夹名称中有空格: 备份完成后的处理 ]
// 文件夹名称 中有空格 将备份的文件移动到用户指定的文件夹并将临时目录删除
if (isEmpty > 1 && strFileName.Substring(4).LastIndexOf("\\") > 1)
{
// 文件存在则替换
if (File.Exists(strFileName.Substring(strFileName.LastIndexOf("\\") + 2)))
{
File.Delete(strFileName.Substring(strFileName.LastIndexOf("\\") + 2));
}
File.Move(strTmp, strFileName);
}
#endregion
}
return(true);
}
/// <summary>
/// SQL数据库备份
/// </summary>
/// <param name="filename">要备份到的文件全路径</param>
/// <param name="connStrBuilder">连接字符串构造器</param>
/// <param name="Backup_PercentComplete">进度</param>
/// <param name="oBackup">数据库备份服务对象</param>
/// <param name="remark">备份备注</param>
public bool SQLDbBackup(string filename, SqlConnectionStringBuilder connStrBuilder, SQLDMO.BackupSink_PercentCompleteEventHandler Backup_PercentComplete, out SQLDMO.Backup oBackup, string remark)
{
string ServerIP = connStrBuilder.DataSource;
string LoginUserName = connStrBuilder.UserID;
string LoginPass = connStrBuilder.Password;
string DBName = connStrBuilder.InitialCatalog;
FileInfo fi = new FileInfo(filename);
string DBFile = fi.Name.Substring(0, fi.Name.Length - fi.Extension.Length);
oBackup = new SQLDMO.BackupClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
try
{
oSQLServer.LoginSecure = false;
oSQLServer.Connect(ServerIP, LoginUserName, LoginPass);
oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
oBackup.PercentComplete += Backup_PercentComplete;
oBackup.Database = DBName;
oBackup.Files = @"" + string.Format("[{0}]", filename) + "";
oBackup.BackupSetName = DBFile;
oBackup.BackupSetDescription = "备份集" + DBFile;
oBackup.Initialize = true;
oBackup.SQLBackup(oSQLServer);
return(true);
}
catch (Exception e)
{
MessageBox.Show("数据库备份失败：" + e.Message);
return(false);
}
finally
{
oSQLServer.DisConnect();
}
}
