利用C#备份和还原sqlserver数据库时,最好使用master数据库进行操作,以下是备份和还原的代码:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Collections;
namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
// 连接字符串
private string connectionString = "Data Source=DSF-PC;Initial Catalog=master;User ID=sa;Password=123456";
// 构造函数
public Form1()
{
InitializeComponent();
}
// 备份
private void btnBackup_Click(object sender, EventArgs e)
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Title = "备份数据库";
saveFileDialog.Filter = "备份文件(*.bak)|*.bak";
saveFileDialog.RestoreDirectory = true;
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
bool ok = Backup("Test", saveFileDialog.FileName);
if (ok)
{
MessageBox.Show("备份成功");
}
else
{
MessageBox.Show("备份失败");
}
}
}
// 还原
private void btnRestore_Click(object sender, EventArgs e)
{
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.Title = "还原数据库";
openFileDialog.Filter = "备份文件(*.bak)|*.bak";
openFileDialog.RestoreDirectory = true;
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
bool ok = Restore("Test", openFileDialog.FileName);
if (ok)
{
MessageBox.Show("还原成功");
}
else
{
MessageBox.Show("还原失败");
}
}
}
// 备份数据库
private bool Backup(string dbName, string filePath)
{
SqlConnection connection = new SqlConnection();
connection.ConnectionString = connectionString;
connection.Open();
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandText = string.Format("backup database {0} to disk = '{1}'", dbName, filePath);
try
{
command.ExecuteNonQuery();
return true;
}
catch
{
return false;
}
finally
{
connection.Close();
}
}
// 还原数据库
private bool Restore(string dbName, string filePath)
{
SqlConnection connection = new SqlConnection();
connection.ConnectionString = connectionString;
connection.Open();
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandText = string.Format("select spid from sysprocesses,sysdatabases where sysprocesses.dbid=sysdatabases.dbid and sysdatabases.Name='{0}'", dbName);
// 获取当前所有连接进程
List<short> list = new List<short>();
try
{
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
list.Add(reader.GetInt16(0));
}
reader.Close();
}
catch
{
return false;
}
finally
{
connection.Close();
}
// 杀死当前所有连接进程
try
{
for (int i = 0; i < list.Count; i++)
{
connection.Open();
command = new SqlCommand(string.Format("kill {0}", list[i].ToString()), connection);
command.ExecuteNonQuery();
connection.Close();
}
}
catch
{
return false;
}
finally
{
connection.Close();
}
// 还原数据库
connection.Open();
command.CommandText = string.Format("restore database {0} from disk = '{1}' with replace", dbName, filePath);
try
{
command.ExecuteNonQuery();
}
catch
{
return false;
}
finally
{
connection.Close();
}
return true;
}
}
}