只能程序与数据库同在一台机器可用,除了备份完可以下载备份文件暂外无其他办法。还有数据还原时杀掉链接,还原时提示链接失败,第二次执行正常,暂时用两次操作解决。
一、ASP.NET
string backaway = ConfigurationManager.AppSettings["DataPath"].ToString();
public struct sFileInfo
{
public string FileName;
public DateTime FileCreateTime;
}
#region 获取指定目录下的某后缀名的所有文件,按时间顺序排列,将文件名及创建时间存入DataTable中
private DataTable GetFileTable()
{
DirectoryInfo d = new DirectoryInfo(backaway);
//根椐指定文件夹下的jpg文件数目,获取数组的长度 n
int n = 0;
foreach (FileInfo _f in d.GetFiles())
{
if (_f.Name.Substring(_f.Name.Length - 3, 3) == "bak")
//注意:这里只是简单的判断名称的后三位,也就是说文件的扩展名是区分大小写
{
n++;
}
}
//定义数组 并对数组进行赋值
sFileInfo[] ArrFiles = new sFileInfo[n];
int i = 0;
foreach (FileInfo _f in d.GetFiles())
{
if (_f.Name.Substring(_f.Name.Length - 3, 3) == "bak")
{
ArrFiles[i].FileName = _f.Name;
ArrFiles[i].FileCreateTime = _f.CreationTime;
i++;
}
}
//对数组根椐文件创建时间进行冒泡排序
sFileInfo FileInfoTemp;
for (int k = 0; k < n; k++)
{
for (int j = k + 1; j < n; j++)
{
if (DateTime.Compare(ArrFiles[k].FileCreateTime, ArrFiles[j].FileCreateTime) < 0)
{
FileInfoTemp = ArrFiles[k];
ArrFiles[k] = ArrFiles[j];
ArrFiles[j] = FileInfoTemp;
}
}
}
DataTable table = new DataTable();
table.Columns.Add("data_path");
table.Columns.Add("data_date");
DataRow row;
for (int j = 0; j < ArrFiles.Length; j++)
{
row = table.NewRow();
row["data_path"] = ArrFiles[j].FileName;
row["data_date"] = ArrFiles[j].FileCreateTime.ToString("yyyy-MM-dd HH:mm:ss");
table.Rows.Add(row);
}
return table;
}
#endregion
#region 数据备份
private void DataBackups()
{
SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
try
{
oSQLServer.LoginSecure = false;
//下面设置登录sql服务器的ip,登录名,登录密码
string[] connStr = ConfigurationManager.AppSettings["ConnectionString"].ToString().Split(';');
string servername = connStr[0].Substring(7, connStr[0].ToString().Length - 7);
string username = connStr[1].Substring(4, connStr[1].ToString().Length - 4);
string userpass = connStr[2].Substring(4, connStr[2].ToString().Length - 4);
oSQLServer.Connect(servername, username, userpass);
oBackup.Action = 0;
//数据库名称:
oBackup.Database = "HotMeter";//PrisonCars
//备份的路径
string name = "HotMeter" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".bak";
string filename = @backaway + name;
oBackup.Files = filename;
//备份的文件名
oBackup.BackupSetName = "HotMeter.bak";
oBackup.BackupSetDescription = "数据库备份";
oBackup.Initialize = true;
oBackup.SQLBackup(oSQLServer);
HotMeter.Web.LogCommon.AddLog("创建数据库备份-" + name);
Response.Write("<script>alert('备份数据库成功!备份文件为:" + name + "');window.location.href=window.location.href</script>");
}
catch
{
//MessageBox.Show("备份失败!", "提示");
}
finally
{
oSQLServer.DisConnect();
}
}
#endregion
#region 数据还原
private void DataRestore(string file)
{
for (int i = 0; i < 2; i++)//执行两次,解决杀掉进程后,主机强迫关闭连接问题。
{
//string dbaway = ConfigurationManager.AppSettings["DataPath"].ToString();
string dbaway = file;
SQLDMO.Restore restore = new SQLDMO.RestoreClass();
SQLDMO.SQLServer server = new SQLDMO.SQLServerClass();
string[] connStr = ConfigurationManager.AppSettings["ConnectionString"].ToString().Split(';');
string servername = connStr[0].Substring(7, connStr[0].ToString().Length - 7);
string username = connStr[1].Substring(4, connStr[1].ToString().Length - 4);
string userpass = connStr[2].Substring(4, connStr[2].ToString().Length - 4);
server.Connect(servername, username, userpass);
//KILL DataBase Process
SqlConnection myCon = new SqlConnection("server=" + servername + ";uid=" + username + ";pwd=" + userpass + ";database=master");
myCon.Open();//k
SqlCommand cmd = new SqlCommand("use master Select spid FROM sysprocesses ,sysdatabases Where sysprocesses.dbid=sysdatabases.dbid AND sysdatabases.Name='HotMeter'", myCon);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
server.KillProcess(Convert.ToInt32(dr[0].ToString()));
}
dr.Close();
myCon.Close();//g
try
{
restore.Action = 0;
restore.Database = "HotMeter";
restore.Files = @dbaway;
restore.ReplaceDatabase = true;
if (myCon.State != ConnectionState.Open)
{
myCon.Open();
}
restore.SQLRestore(server);
if (i == 1)
{
Response.Write("<script>alert('还原数据库成功!还原文件为:" + file + "');window.location.href=window.location.href</script>");
}
}
catch (Exception ex)
{
//MessageBox.Show(ex.Message);
}
finally
{
server.DisConnect();
}
}
}
#endregion
#region 备份删除
protected void btnDelete_Click(object sender, EventArgs e)
{
string idlist = GetSelIDlist();
if (idlist.Trim().Length == 0)
return;
string[] s = idlist.Split(',');
for (int i = 0; i < s.Length; i++)
{
string filename = s[i].ToString().Replace("'", "");
string path = backaway + filename;
if (File.Exists(@path))
{
File.Delete(@path);
HotMeter.Web.LogCommon.AddLog("删除数据库备份文件-" + filename);
}
}
Response.Write("<script>alert('删除成功!');window.location.href=window.location.href</script>");
}
#endregion
二、Winform
namespace PrisonCarsManage
{
public partial class BackUpAndRestore : Form
{
public BackUpAndRestore()
{
InitializeComponent();
}
private void Step2(string message, int percent)
{//还原进度条
progressBar2.Value = percent;
while (progressBar2.Value < progressBar2.Maximum)
{
progressBar2.PerformStep();
}
}
private void Step(string message, int percent)
{//备份进度条
progressBar1.Value = percent;
while (progressBar1.Value < progressBar2.Maximum)
{
progressBar1.PerformStep();
}
}
private void button1_Click(object sender, EventArgs e)
{
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
if (!File.Exists(saveFileDialog1.FileName.ToString()))
{
textBox1.Text = saveFileDialog1.FileName.ToString();
}
else
{
MessageBox.Show("请重新命名!");
}
}
}
private void button4_Click(object sender, EventArgs e)
{
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
textBox2.Text = openFileDialog1.FileName.ToString();
}
}
private void button2_Click(object sender, EventArgs e)
{
string backaway = textBox1.Text.Trim();
SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
try
{
oSQLServer.LoginSecure = false;
//下面设置登录sql服务器的ip,登录名,登录密码
string[] connStr = DAL.DbHelperSQL.connectionString.ToString().Split(';');
string servername = connStr[0].Substring(7, connStr[0].ToString().Length - 7);
string username = connStr[1].Substring(4, connStr[1].ToString().Length - 4);
string userpass = connStr[2].Substring(4, connStr[2].ToString().Length - 4);
oSQLServer.Connect(servername, username, userpass);
oBackup.Action = 0;
//下面两句是显示进度条的状态
SQLDMO.BackupSink_PercentCompleteEventHandler pceh = new SQLDMO.BackupSink_PercentCompleteEventHandler(Step);
oBackup.PercentComplete += pceh;
//数据库名称:
oBackup.Database = "PrisonCars";//PrisonCars
//备份的路径
oBackup.Files = @backaway;
//备份的文件名
oBackup.BackupSetName = "PrisonCars";
oBackup.BackupSetDescription = "数据库备份";
oBackup.Initialize = true;
oBackup.SQLBackup(oSQLServer);
Common.SysLog.AddSysLog(Common.CurrentUser.UserId, Common.CurrentUser.UserName, "备份数据库。");
MessageBox.Show("备份成功!", "提示");
}
catch
{
MessageBox.Show("备份失败!", "提示");
}
finally
{
oSQLServer.DisConnect();
}
}
private void button3_Click(object sender, EventArgs e)
{
for (int i = 0; i < 2; i++)//执行两次,解决杀掉进程后,主机强迫关闭连接问题。
{
string dbaway = textBox2.Text.Trim();
SQLDMO.Restore restore = new SQLDMO.RestoreClass();
SQLDMO.SQLServer server = new SQLDMO.SQLServerClass();
string[] connStr = DAL.DbHelperSQL.connectionString.ToString().Split(';');
string servername = connStr[0].Substring(7, connStr[0].ToString().Length - 7);
string username = connStr[1].Substring(4, connStr[1].ToString().Length - 4);
string userpass = connStr[2].Substring(4, connStr[2].ToString().Length - 4);
server.Connect(servername, username, userpass);
//KILL DataBase Process
SqlConnection myCon = new SqlConnection("server=" + servername + ";uid=" + username + ";pwd=" + userpass + ";database=master");
myCon.Open();//k
SqlCommand cmd = new SqlCommand("use master Select spid FROM sysprocesses ,sysdatabases Where sysprocesses.dbid=sysdatabases.dbid AND sysdatabases.Name='PrisonCars'", myCon);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
server.KillProcess(Convert.ToInt32(dr[0].ToString()));
}
dr.Close();
myCon.Close();//g
try
{
restore.Action = 0;
SQLDMO.RestoreSink_PercentCompleteEventHandler pceh = new SQLDMO.RestoreSink_PercentCompleteEventHandler(Step2);
restore.PercentComplete += pceh;
restore.Database = "PrisonCars";
restore.Files = @dbaway;
restore.ReplaceDatabase = true;
if (myCon.State != ConnectionState.Open)
{
myCon.Open();
}
restore.SQLRestore(server);
Common.SysLog.AddSysLog(Common.CurrentUser.UserId, Common.CurrentUser.UserName, "恢复数据库。");
MessageBox.Show("数据库恢复成功!");
}
catch (Exception ex)
{
//MessageBox.Show(ex.Message);
}
finally
{
server.DisConnect();
}
}
}
}
}