string backupstr = "restore database jxcDB from disk='" + path + "' With Replace,no recovery";
BakReductSql(backupstr, false,path);
private void BakReductSql(string cmdText, bool isBak,string path)
{
if (!isBak) //如果是恢复操作
{
string setOffline = "Alter database jxcDB Set Offline With rollback immediate";
string setOnline = "Alter database jxcDB Set Online With Rollback immediate";
cmdBakRst.CommandText = setOffline + cmdText + setOnline;
}
}这是所有用到with的地方,提示
对不起,操作失败,可能原因:System.Data.SqlClient.SqlException: 关键字 'database' 附近有语法错误。关键字 'with' 附近有语法错误。如果此语句是公用表表达式或 xmlnamespaces 子句,那么前一个语句必须以分号结尾。关键字 'with' 附近有语法错误。如果此语句是公用表表达式或 xmlnamespaces 子句,那么前一个语句必须以分号结尾。 'immediate' 附近有语法错误。
BakReductSql(backupstr, false,path);
private void BakReductSql(string cmdText, bool isBak,string path)
{
if (!isBak) //如果是恢复操作
{
string setOffline = "Alter database jxcDB Set Offline With rollback immediate";
string setOnline = "Alter database jxcDB Set Online With Rollback immediate";
cmdBakRst.CommandText = setOffline + cmdText + setOnline;
}
}这是所有用到with的地方,提示
对不起,操作失败,可能原因:System.Data.SqlClient.SqlException: 关键字 'database' 附近有语法错误。关键字 'with' 附近有语法错误。如果此语句是公用表表达式或 xmlnamespaces 子句,那么前一个语句必须以分号结尾。关键字 'with' 附近有语法错误。如果此语句是公用表表达式或 xmlnamespaces 子句,那么前一个语句必须以分号结尾。 'immediate' 附近有语法错误。
解决方案 »
- 过滤Html标签保留<br>和<b></b>标签
- vs2005 中asp.net MVC安装哪个版本比较好?
- 水晶报表如何给列求和?
- 同一数组中的值比较;
- 网站怎样支持简体和繁体中文版?
- -------C#控件"底色"大问题,------------谁知不知道怎么给控件着色,是指那些那没有背景色属性的控件,如mainmenu
- 常常看到进入网页 比如银行 有安全提示。请问这是怎么做的呢?
- HTML框架集的滚动条
- 哪位帮我解释一下,这段代码是什么意思C#
- 将录入时间转化为无符号的字符串以用于文件名如何操作?
- 发布到iis 的edmx无法实时更新
- javascript能否取得GridView中的一行值?
/*
利用bak恢复数据库,强制还原(REPLACE)
STATS = 10 每完成10%显示一条记录
DBTest和DBTest_log是上面g:\back.Bak里的逻辑文件
*/
USE master
RESTORE DATABASE DB
FROM DISK = 'g:\back.Bak'
WITH MOVE 'DBTest' TO 'E:\Program Files\Microsoft SQL Server2005\Data\DB.mdf',
MOVE 'DBTest_log' TO 'E:\Program Files\Microsoft SQL Server2005\Data\DB_log.ldf',
STATS = 10, REPLACE
GO
//数据备份
protected void Button1_Click1(object sender, EventArgs e)
{
Random rd = new Random();
//Company c = new Company();
//c = CompanyManager.getC();
//虚拟路径
string virtualPath = this.Page.Request.ApplicationPath;
//绝对路径
string pathRooted = System.Web.Hosting.HostingEnvironment.MapPath(virtualPath);
string path = pathRooted + @"App_data\backup\jxcDB" + DateTime.Now.ToShortDateString() + "_" + rd.Next(9999999) + ".bak";
string backupstr = "backup database jxcDB to disk='" + path + "'";
BakReductSql(backupstr,true,path);
}//数据恢复
protected void Button2_Click1(object sender, EventArgs e)
{
string backupstr = "use master exec killspid 'jxcDB' restore database jxcDB from disk='" + path + "' With Replace";
BakReductSql(backupstr, false,path);
}
/// <summary>
/// 对数据库的备份和恢复操作,Sql语句实现
/// </summary>
/// <param name="cmdText">实现备份或恢复的Sql语句 </param>
/// <param name="isBak">该操作是否为备份操作,是为true否,为false </param>
private void BakReductSql(string cmdText, bool isBak,string path)
{
Random rd = new Random();
SqlCommand cmdBakRst = new SqlCommand();
SqlConnection conn = new SqlConnection("Data Source=SUWEN-37FCA4626\\SQLEXPRESS;Initial Catalog=jxcDB;uid=sa;pwd=000000;");
try
{
conn.Open();
cmdBakRst.Connection = conn;
cmdBakRst.CommandType = CommandType.Text;
//if (!isBak) //如果是恢复操作
//{
// string setOffline = "Alter database jxcDB Set Offline With rollback immediate";
// string setOnline = "Alter database jxcDB Set Online With Rollback immediate";
// cmdBakRst.CommandText = setOffline + cmdText + setOnline;
//}
//else
//{
cmdBakRst.CommandText = cmdText;
//}
cmdBakRst.ExecuteNonQuery();
if (!isBak)
{
Response.Write("<script>alert('恭喜你,数据成功恢复为所选文档的状态!');window.location='DataBackup.aspx'</script>");
}
else
{
DataBacks db = new DataBacks();
db.DataBackPath = path;
db.DataBackTime = DateTime.Now;
DataBackManager.addDBack(db);
Response.Write("<script>alert('恭喜,你已经成功备份当前数据!');window.location='DataBackup.aspx'</script>");
} }
//catch (SqlException sexc)
//{
// Response.Write("失败,可能是对数据库操作失败,原因:" + sexc+"");
//}
catch (Exception ex)
{
Response.Write("对不起,操作失败,可能原因:" + ex + "");
}
finally
{
cmdBakRst.Dispose();
conn.Close();
conn.Dispose();
} SqlConnection.ClearAllPools();
}在服务器sql数据库master表下建查询
create proc killspid (@dbname varchar(20))
as
begin
declare @sql nvarchar(500)
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 <> -1
begin
exec('kill '+@spid)
fetch next from getspid into @spid
end
close getspid
deallocate getspid
end