using System;
using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Text;using System.Windows.Forms;using System.Data.SqlClient;using System.IO;namespace WindowsApplication1{ public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void Form1_Load(object sender, EventArgs e) { this.txtName.Text = "数据库名" + System.DateTime.Now.ToShortDateString(); } //将数据库备份到应用程序的跟目录下 string connectionString = "server=服务器名;database=数据库名;uid=用户名;pwd=密码"; private void btn_beifen_Click(object sender, EventArgs e) { try { string strg = Application.StartupPath.ToString(); strg = strg.Substring(0, strg.LastIndexOf("\\")); strg = strg.Substring(0, strg.LastIndexOf("\\")); strg += @"\Data"; string sqltxt = @"BACKUP DATABASE 数据库名 TO Disk='" + strg + "\\" + txtPath.Text + ".bak" + "'"; SqlConnection con = new SqlConnection(connectionString); con.Open(); SqlCommand cmd = new SqlCommand(sqltxt, con); cmd.ExecuteNonQuery(); con.Close(); if (MessageBox.Show("备份成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation) == DialogResult.OK) { this.Close(); } } catch (Exception ex) { MessageBox.Show(ex.Message.ToString()); } } //选择保存目录事件 private void btn_open_Click(object sender, EventArgs e) { folderBrowserDialog1.Description = "请选择备份文件将要保存到的文件夹，如有必要你可以\n通过单击左下角的‘新建文件夹’按钮新建文件夹"; if (folderBrowserDialog1.ShowDialog() == DialogResult.OK) { txtPath.Text = folderBrowserDialog1.SelectedPath.ToString(); } } //开始备份数据到指定目录下的指定文件名 private void btn_ok_Click(object sender, EventArgs e) { string filepath="D:\\机房设备资源管理系统数据库备份\\"; try { if (txtName.Text != "") { if (txtPath.Text != "") { filepath=txtPath.Text.Trim()+"\\"; }else { if (MessageBox.Show("你确定要要将数据备份到默认位置：" + filepath + "下吗？", "信息提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.No) { MessageBox.Show("请选择保存位置！", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information); btn_open_Click(sender, e); } else { if (!Directory.Exists(filepath)) { Directory.CreateDirectory(filepath); } } } SqlConnection con = new SqlConnection(connectionString); con.Open(); string strBacl = "backup database 数据库名 to disk='" + filepath + txtName.Text.Trim() + ".bak'"; SqlCommand Cmd = new SqlCommand(strBacl, con); if (Cmd.ExecuteNonQuery() != 0) { con.Close(); MessageBox.Show("数据备份成功！", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information); this.Close(); } else { MessageBox.Show("数据备份失败！", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } } else if (txtPath.Text != "" && txtName.Text== "") { MessageBox.Show("备份名称为必填项！你必须填写！：", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information); }// end } catch (Exception ee) { MessageBox.Show(ee.Message.ToString()); } } private void LK_lbl_option_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e) { restore rs = new restore(); rs.Show(); } }}
SQL数据库恢复后台代码：
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace WindowsApplication1
{
public partial class restore : Form
{
public restore()
{
InitializeComponent();
}
string DateStr = "server=服务器名;database=数据库名;uid=用户名;pwd=密码";
private void restore_Load(object sender, EventArgs e)
{
//string strg = Application.StartupPath.ToString();
//strg = strg.Substring(0, strg.LastIndexOf("\\"));
//strg = strg.Substring(0, strg.LastIndexOf("\\"));
//strg += @"\Data";
//textBox1.Text = strg + "\\" + "MySale.bak";
}
/// <summary>
/// 备份数据库按钮事件
/// txtName(文本框)用于获取备份文件名，txtPath(文本框)用于获取备份文件路径
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_ok_Click(object sender, EventArgs e)
{
if (this.txtPath.Text != ""&&this.txtPath.Text != null)
{
SqlConnection conn = new SqlConnection(DateStr);
conn.Open();
//-------------------杀掉所有连接 db_PWMS 数据库的进程--------------
string strSQL = "select spid from master..sysprocesses where dbid=db_id( '数据库名') ";
SqlDataAdapter Da = new SqlDataAdapter(strSQL, conn);
DataTable spidTable = new DataTable();
Da.Fill(spidTable);
SqlCommand Cmd = new SqlCommand();
Cmd.CommandType = CommandType.Text;
Cmd.Connection = conn;
if (spidTable.Rows.Count > 1)
{//强行关闭非本程序使用的所有用户进程
for (int iRow = 0; iRow < spidTable.Rows.Count - 1; iRow++)
{
Cmd.CommandText = "kill " + spidTable.Rows[iRow][0].ToString(); //强行关闭用户进程
Cmd.ExecuteNonQuery();
}
}
conn.Close();
conn.Dispose();
//--------------------------------------------------------------------
try
{
string str = "use master restore database 数据库名 from Disk='" + txtPath.Text.Trim() + "'";
SqlConnection conn1 = new SqlConnection(DateStr);
conn1.Open();
SqlCommand cmd = new SqlCommand(str, conn1);
cmd.ExecuteNonQuery();
if (MessageBox.Show("恢复成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation) == DialogResult.OK)
{
this.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
else {
MessageBox.Show("请选择备份文件位置！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
/// <summary>
/// 保存位置选择按钮事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_open_Click(object sender, EventArgs e)
{
openFileDialog1.FilterIndex = 0;
openFileDialog1.FileName = "";
openFileDialog1.Filter = "txt files (*.bak)|*.bak|All files (*.*)|*.*";
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
txtPath.Text = openFileDialog1.FileName.ToString();
}
}
}
}