MSSQL、C# 、Winform、ASP.NET - 数据库备份与还原模块 - .net

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

数据库备份还原类：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;//应用相应的命名空间
using System.Windows.Forms;
using System.Collections;
using System.IO;
using System.Data;
using System.Data.SqlClient;namespace canyinxt.Command
{
public static class BacupDatabase
{
static string connectionString = "Data Source=(local);Initial Catalog=master;integrated security = true";
static SqlConnection conn = new SqlConnection(connectionString); #region 备份指定的数据库文件
/// <summary>
/// 备份指定的数据库文件
/// </summary>
/// <param name="DBName">备份名称要与数据库中的数据库名称相同</param>
/// <param name="databasename">要还原的数据库(包含要备份的文件名)</param>
/// <returns></returns>
public static bool BackUpDataBase(string DBName, string databasefile)
{
if (!File.Exists(databasefile))
{ }
//还原的数据库MyDataBase
string sql = "BACKUP DATABASE " + "" + DBName + "" + " TO DISK = '" + databasefile + ".bak' ";
conn.Open();
SqlCommand comm = new SqlCommand(sql, conn);
comm.CommandType = CommandType.Text;
try
{
comm.ExecuteNonQuery();
}
catch (Exception err)
{
string str = err.Message;
conn.Close(); return false;
} conn.Close();//关闭数据库连接
return true;
}
#endregion //以下是还原数据库，稍微麻烦些，要关闭所有与当前数据库相连的连接
#region 还原数据库
/// <summary>
/// 还原数据库
/// </summary>
/// <param name="DBName">要还原数据库名称（此名称要和备份时候的相同）</param>
/// <param name="backfile">数据库文件路径（加名称）</param>
/// <returns></returns>
public static bool RestoreDatabase(string DBName, string backfile)
{
///杀死原来所有的数据库连接进程
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Data Source=(local);Initial Catalog=master;integrated security = true";
conn.Open();
string sql = "SELECT spid FROM sysprocesses ,sysdatabases WHERE sysprocesses.dbid=sysdatabases.dbid AND sysdatabases.Name='" + "" + DBName + "" + "'";
SqlCommand cmd1 = new SqlCommand(sql, conn);
SqlDataReader dr;
ArrayList list = new ArrayList();
try
{
dr = cmd1.ExecuteReader();
while (dr.Read())
{
list.Add(dr.GetInt16(0));
}
dr.Close();
}
catch (SqlException eee)
{
MessageBox.Show(eee.ToString());
}
finally
{
conn.Close();
}
//MessageBox.Show(list.Count.ToString());
for (int i = 0; i < list.Count; i++)
{
conn.Open();
cmd1 = new SqlCommand(string.Format("KILL {0}", list[i].ToString()), conn);
cmd1.ExecuteNonQuery();
conn.Close();
// MessageBox.Show("系统已经清除的数据库线程： " + list[i].ToString() + "\r\n正在还原数据库！");
}
MessageBox.Show("系统已经清除的数据库线程： " + list.Count.ToString() + "\r\n正在还原数据库！");
//这里一定要是master数据库，而不能是要还原的数据库，因为这样便变成了有其它进程
//占用了数据库。
string constr = @"Data Source=(local);Initial Catalog=master;integrated security = true";
string database = DBName;
string path = backfile;
string BACKUP = String.Format("RESTORE DATABASE {0} FROM DISK = '{1}' WITH REPLACE", database, path);
SqlConnection con = new SqlConnection(constr);
SqlCommand cmd = new SqlCommand(BACKUP, con);
con.Open();
try
{
cmd.ExecuteNonQuery();
MessageBox.Show("还原成功,点击退出系统，请重新登录！");
Application.Exit();
return true;
}
catch (SqlException ee)
{
//throw(ee); //MessageBox.Show("还原失败"); MessageBox.Show(ee.ToString() + "还原失败！", "还原失败！");
return false; }
finally
{
con.Close();
}
}
#endregion
}
}