C#数据库备份及还原的实现代码

/*********************************************************** **编 号：SC20090702-001 **作 者：zhpingyun008@qq.com **注释说明：强制中断数据库连接，用于数据库还原操作 ***********************************************************/ use master go create proc killspid (@dbname varchar(20)) as begin declare @sql nvarchar(500),@temp varchar(1000) declare @spid int set @sql='declare getspid cursor for select spid from sysprocesses where dbid=db_id('''+@dbname+''')' exec (@sql) open getspid fetch next from getspid into @spid while @@fetch_status =0 begin set @temp='kill '+rtrim(@spid) exec(@temp) fetch next from getspid into @spid end close getspid deallocate getspid end /*********************************************************** **编 号：CJ20090702-001 **作 者：zhpingyun008@qq.com **注释说明：数据库备份操作 ***********************************************************/ protected void Button1_Click1(object sender, EventArgs e) { SQLDMO.Backup oBackup = new SQLDMO.BackupClass(); SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass(); try { oSQLServer.LoginSecure = false; oSQLServer.Connect(".", "sa", "zhpingyun"); oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database; oBackup.Database = "CRM"; oBackup.Files = @"D:\CRM.bak"; oBackup.BackupSetName = "CRM"; oBackup.BackupSetDescription = "数据库备份"; oBackup.Initialize = true; oBackup.SQLBackup(oSQLServer); Response.Write("<script>alert('数据库备份成功！');</script>"); } catch (Exception ex) { throw ex; Response.Write("<script>alert('数据库备份失败！');</script>"); } finally { oSQLServer.DisConnect(); } } /*********************************************************** **编 号：CJ20090702-002 **作 者：zhpingyun008@qq.com **注释说明：数据库还原操作 ***********************************************************/ protected void Button2_Click(object sender, EventArgs e) { if (exepro() != true)//执行存储过程 { Response.Write("<script>alert('存储过程killspid执行失败！');</script>"); } else { SQLDMO.Restore oRestore = new SQLDMO.RestoreClass(); SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass(); try { oSQLServer.LoginSecure = false; oSQLServer.Connect(".", "sa", "zhpingyun"); oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database; oRestore.Database = "CRM"; oRestore.Files = @"D:\CRM.bak"; oRestore.FileNumber = 1; oRestore.ReplaceDatabase = true; oRestore.SQLRestore(oSQLServer); Response.Write("<script>alert('数据还原成功！');</script>"); } catch (Exception ex) { throw ex; Response.Write("<script>alert('数据还原失败！');</script>"); } finally { oSQLServer.DisConnect(); } } } /*********************************************************** **编 号：CJ20090702-003 **作 者：zhpingyun008@qq.com **注释说明：把正在连接中的用户都中断，然后才能还原 ***********************************************************/ private bool exepro() { SqlConnection conn1 = new SqlConnection("server=.;uid=sa;pwd=zhpingyun;database=master"); SqlCommand cmd = new SqlCommand("killspid", conn1); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@dbname", "CRM"); try { conn1.Open(); cmd.ExecuteNonQuery(); return true; } catch (Exception ex) { return false; } finally { conn1.Close(); } } use master exec killspid 'test'