SQL备份与恢复

string path = System.AppDomain.CurrentDomain.BaseDirectory + \"BackupDB\";

if(!System.IO .Directory.Exists(path)) {

System.IO.Directory.CreateDirectory(path); }

path = @path + @\"\\\" + DateTime.Now.ToString(\"yyyyMMddHHmmss\") + \".bak\";

string sql = \"backup database ScaleCoalTaxManage TO Disk='\" + path + \"'\";

if(this._db .ExcuteSql(sql)) {

MessageBox.Show(\"数据库已经备份到:\"+path); } }

private void brbarRecove_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e) {

string path = System.AppDomain.CurrentDomain.BaseDirectory + \"BackupDB\";

if (!System.IO.Directory.Exists(path)) {

System.IO.Directory.CreateDirectory(path); }

OpenFileDialog open = new OpenFileDialog(); open.InitialDirectory = path;

open.Filter = \"bak files (*.bak)|*.bak\"; open.FilterIndex = 2;

open.RestoreDirectory = true;

if (open.ShowDialog() == DialogResult.OK) {

if (DialogResult.Yes == MessageBox.Show(\"此操作有可能使数据库部分数据丢失，是否继续?\提示信息\ {

#region

string restorepath = open.FileName;

DBInfo dbinfo = new DBInfo();

string strConn = \"Server=\" + dbinfo.Server + \";User Id=\" + dbinfo.User + \";Password=\" + dbinfo.Pwd + \";DataBase=master\";

using (SqlConnection conn = new SqlConnection(strConn)) {

if (conn.State == ConnectionState.Closed) {

conn.Open(); }

bool res = Create(conn); if (res) {

string sql = \"RESTORE DATABASE ScaleCoalTaxManage FROM Disk='\" + restorepath + \"'\";

SqlCommand cmd = new SqlCommand(); cmd.Connection = conn;

try {

cmd.CommandText = \"exec p_killspid 'ScaleCoalTaxManage' \" + sql;

cmd.ExecuteNonQuery();

MessageBox.Show(\"恢复成功\"); }

catch (System.Exception ex) {

MessageBox.Show(ex.Message); } } else {

MessageBox.Show(\"断开所有连接失败\"); }

}

#endregion } }

}

public bool Create(SqlConnection conn) {

SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; try {

cmd.CommandText = \"use master if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[p_killspid]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[p_killspid]\"; cmd.ExecuteNonQuery();

cmd.CommandText = \"create proc p_killspid(@dbname varchar(20)) as declare @s nvarchar(1000) declare tb cursor local for select s='kill '+cast(spid as varchar) from master..sysprocesses where dbid=db_id(@dbname) open tb fetch next from tb into @s while @@fetch_status=0 begin exec(@s) fetch next from tb into @s end close tb deallocate tb\";

cmd.ExecuteNonQuery(); return true; }

catch (System.Exception ex) {

return false; }

}