错误在于你写还原路径中运用了变量,在SQL语句中from后是不能用变量的,要写实际表明 C#数据库还原怎么实现 首先,要建立存储过程killspid:create proc killspid (@dbname varchar(20))asbegindeclare @sql nvarchar(500)declare @spid intset @sql='declare getspid cursor forselect spid from sysprocesses where dbid=db_id(exec (@sql)open getspidfetch next from getspid into @spidwhile @@fetch_status<>-1beginexec('kill )fetch next from getspid into @spidendclose getspiddeallocate getspidendGO然后,还原数据库需要执行的操作:SqlConnection conn = new SqlConnection("Server=.;Database=master;User id=sa;Password=sa"); //注意要连接master数据库conn.Open();SqlCommand cmd1 = new SqlCommand("killspid", conn);cmd1.CommandType = CommandType.StoredProcedure;cmd1.Parameters.Add(new SqlParameter("@dbname", "你的数据库名"));cmd1.ExecuteNonQuery(); //先杀死数据库的进程string sql = "Restore Database ljl_loftex From DISK='备份的路径'";SqlCommand cmd2 = new SqlCommand(sql, conn);cmd2.ExecuteNonQuery();
首先,要建立存储过程killspid: create proc killspid (@dbname varchar(20)) as begin declare @sql nvarchar(500) declare @spid int set @sql='declare getspid cursor forselect spid from sysprocesses where dbid=db_id exec (@sql) open getspidfetch next from getspid into @spidwhile @@fetch_status<>-1 begin exec('kill ) fetch next from getspid into @spid end close getspid deallocate getspid end GO 然后还原数据库需要执行的操作 SqlConnection conn = new SqlConnection("Server=.;Database=master;User id=sa;Password=sa"); //注意要连接master数据库 conn.Open(); SqlCommand cmd1 = new SqlCommand("killspid", conn); cmd1.CommandType = CommandType.StoredProcedure; cmd1.Parameters.Add(new SqlParameter("@dbname", "你的数据库名")); cmd1.ExecuteNonQuery(); //先杀死数据库的进程 string sql = "Restore Database ljl_loftex From DISK='备份的路径'";SqlCommand cmd2 = new SqlCommand(sql, conn); cmd2.ExecuteNonQuery();
exec(strsql)的
/// <summary>
/// 获取当前数据库名字
/// </summary>
private string dbName
{
get
{
string sql = "Select Name From Master..SysDataBases Where DbId=(Select Dbid From Master..SysProcesses Where Spid = @@spid)";
return dosoft.DAL.DbHelperSQL.GetSingle(sql).ToString();
}
}备份代码
string dbN = dbName;
string filename = TextBox1.Text.Trim() == "" ? NowTime : TextBox1.Text;
string path = Server.MapPath("./DataBackUp/" + filename + ".bak");
string cmdstr = "backup database " + dbN + " to disk = '" + path + "' ";
try
{
dosoft.DAL.DbHelperSQL.GetSingle(cmdstr);
dosoft.Common.JsHelper.alert(this, "数据库备份成功!");
}
catch (Exception ex)
{ msg.Text = ex.Message;
}
GetDBBak();
还原代码
string dbN = dbName; if (ASPxListBox1.SelectedItem == null)
{
dosoft.Common.JsHelper.alert(this, "请选择要还原的数据库!");
return;
}
string path = Server.MapPath("./DataBackUp/" + ASPxListBox1.SelectedItem.Value.ToString());
string cmdstr = "Alter database " + dbN + " Set offline WITH ROLLBACK IMMEDIATE ";
cmdstr += " Restore database " + dbN + " from disk ='" + path + "' with REPLACE";
cmdstr += " Alter database " + dbN + " set online WITH ROLLBACK IMMEDIATE"; try
{
dosoft.DAL.DbHelperSQL.GetSingle(cmdstr);
dosoft.Common.JsHelper.alert(this, "数据库还原成功!");
}
catch (Exception ex)
{
msg.Text = ex.Message;
}
用了好多年 没任何问题.
C#数据库还原怎么实现
首先,要建立存储过程killspid:create proc killspid (@dbname varchar(20))asbegindeclare @sql nvarchar(500)declare @spid intset @sql='declare getspid cursor forselect spid from sysprocesses where dbid=db_id(exec (@sql)open getspidfetch next from getspid into @spidwhile @@fetch_status<>-1beginexec('kill )fetch next from getspid into @spidendclose getspiddeallocate getspidendGO然后,还原数据库需要执行的操作:SqlConnection conn = new SqlConnection("Server=.;Database=master;User id=sa;Password=sa"); //注意要连接master数据库conn.Open();SqlCommand cmd1 = new SqlCommand("killspid", conn);cmd1.CommandType = CommandType.StoredProcedure;cmd1.Parameters.Add(new SqlParameter("@dbname", "你的数据库名"));cmd1.ExecuteNonQuery(); //先杀死数据库的进程string sql = "Restore Database ljl_loftex From DISK='备份的路径'";SqlCommand cmd2 = new SqlCommand(sql, conn);cmd2.ExecuteNonQuery();
create proc killspid (@dbname varchar(20))
as
begin
declare @sql nvarchar(500)
declare @spid int
set @sql='declare getspid cursor forselect spid from sysprocesses
where dbid=db_id
exec (@sql)
open getspidfetch next from getspid into @spidwhile @@fetch_status<>-1
begin
exec('kill )
fetch next from getspid into @spid
end
close getspid
deallocate getspid
end
GO
然后还原数据库需要执行的操作
SqlConnection conn = new SqlConnection("Server=.;Database=master;User id=sa;Password=sa");
//注意要连接master数据库
conn.Open();
SqlCommand cmd1 = new SqlCommand("killspid", conn);
cmd1.CommandType = CommandType.StoredProcedure;
cmd1.Parameters.Add(new SqlParameter("@dbname", "你的数据库名"));
cmd1.ExecuteNonQuery(); //先杀死数据库的进程
string sql = "Restore Database ljl_loftex From DISK='备份的路径'";SqlCommand cmd2 = new SqlCommand(sql, conn);
cmd2.ExecuteNonQuery();