SQL备份与恢复.doc

SQL备份与恢复private void btbarBackup_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); 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(此操作有可能使数据库部分数据丢失，是否继续?, 提示信息, MessageBoxButtons.YesNo) #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(Ndbo.p_killspid) and OBJECTPROPERTY(id, NIsProcedure) = 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 fro