请问我是用SQLSERVER2005的数据库,是不是不能用下面的方法还原数据库啊?报错了!~~~~
我在.NET中运行报的错误是:(麻烦高手帮忙看下..)
“/Exam”应用程序中的服务器错误。
--------------------------------------------------------------------------------不支持关键字: “alter database zhszcp set single_user with rollback immediate use master restore database zhszcp from disk”。
说明: 执行当前 Web 请求期间,出现未处理的异常。请检查堆栈跟踪信息,以了解有关该错误以及代码中导致错误的出处的详细信息。 异常详细信息: System.ArgumentException: 不支持关键字: “alter database zhszcp set single_user with rollback immediate use master restore database zhszcp from disk”。源错误:
行 96: Button2.Attributes.Add("onclick", "return confirm('确认要恢复数据吗?')");
行 97: string RecoveryStr = "Alter DATABASE " + DBName + " set single_user with rollback immediate use master RESTORE DATABASE " + DBName + " from disk='" + DBBackupName + "'";
行 98: SqlConnection conn = new SqlConnection(RecoveryStr);
行 99: conn.Open();
行 100: try---------------------------------------------------------------------//恢复(还原)数据库
protected void Button2_Click(object sender, EventArgs e)
{
//数据库服务器名
string ServerName = Txt_ServerName.Text.ToString();
//数据库名
string DBName = Txt_DBName.Text.ToString();
//用户名
string UName = Txt_UserName.Text.ToString();
//密码
string UPassword = Txt_UserPasswrod.Text.ToString();
//备份文件路径+文件名
string DBBackupName = Server.MapPath("~\\Back\\XTSZ\\DataBackup\\") + Txt_FileName.Text.ToString(); Button2.Attributes.Add("onclick", "return confirm('确认要恢复数据吗?')");
string RecoveryStr = "Alter DATABASE " + DBName + " set single_user with rollback immediate use master RESTORE DATABASE " + DBName + " from disk='" + DBBackupName + "'";
SqlConnection conn = new SqlConnection(RecoveryStr);
conn.Open();
try
{
SqlCommand comm1 = new SqlCommand(RecoveryStr, conn);
comm1.ExecuteNonQuery(); //执行远程数据库恢复命令
RecoveryStr = "Alter DATABASE " + DBName + " set multi_user";
SqlCommand comm2 = new SqlCommand(RecoveryStr, conn);
comm2.ExecuteNonQuery();
//使远程数据库转入多用户模式
ClientScript.RegisterClientScriptBlock(this.GetType(), "Error", "alert('数据库还原成功!')", true);
}
catch (Exception ms)
{
ClientScript.RegisterClientScriptBlock(this.GetType(), "Error", "alert('数据库还原失败!')", true);
Response.Write(ms.Message);
}
finally
{
conn.Close();
}
}
我在.NET中运行报的错误是:(麻烦高手帮忙看下..)
“/Exam”应用程序中的服务器错误。
--------------------------------------------------------------------------------不支持关键字: “alter database zhszcp set single_user with rollback immediate use master restore database zhszcp from disk”。
说明: 执行当前 Web 请求期间,出现未处理的异常。请检查堆栈跟踪信息,以了解有关该错误以及代码中导致错误的出处的详细信息。 异常详细信息: System.ArgumentException: 不支持关键字: “alter database zhszcp set single_user with rollback immediate use master restore database zhszcp from disk”。源错误:
行 96: Button2.Attributes.Add("onclick", "return confirm('确认要恢复数据吗?')");
行 97: string RecoveryStr = "Alter DATABASE " + DBName + " set single_user with rollback immediate use master RESTORE DATABASE " + DBName + " from disk='" + DBBackupName + "'";
行 98: SqlConnection conn = new SqlConnection(RecoveryStr);
行 99: conn.Open();
行 100: try---------------------------------------------------------------------//恢复(还原)数据库
protected void Button2_Click(object sender, EventArgs e)
{
//数据库服务器名
string ServerName = Txt_ServerName.Text.ToString();
//数据库名
string DBName = Txt_DBName.Text.ToString();
//用户名
string UName = Txt_UserName.Text.ToString();
//密码
string UPassword = Txt_UserPasswrod.Text.ToString();
//备份文件路径+文件名
string DBBackupName = Server.MapPath("~\\Back\\XTSZ\\DataBackup\\") + Txt_FileName.Text.ToString(); Button2.Attributes.Add("onclick", "return confirm('确认要恢复数据吗?')");
string RecoveryStr = "Alter DATABASE " + DBName + " set single_user with rollback immediate use master RESTORE DATABASE " + DBName + " from disk='" + DBBackupName + "'";
SqlConnection conn = new SqlConnection(RecoveryStr);
conn.Open();
try
{
SqlCommand comm1 = new SqlCommand(RecoveryStr, conn);
comm1.ExecuteNonQuery(); //执行远程数据库恢复命令
RecoveryStr = "Alter DATABASE " + DBName + " set multi_user";
SqlCommand comm2 = new SqlCommand(RecoveryStr, conn);
comm2.ExecuteNonQuery();
//使远程数据库转入多用户模式
ClientScript.RegisterClientScriptBlock(this.GetType(), "Error", "alert('数据库还原成功!')", true);
}
catch (Exception ms)
{
ClientScript.RegisterClientScriptBlock(this.GetType(), "Error", "alert('数据库还原失败!')", true);
Response.Write(ms.Message);
}
finally
{
conn.Close();
}
}
public static string RestoreDatabase(string backfile)
{
///杀死原来所有的数据库连接进程
/// SqlConnection conn=new SqlConnection ();
conn.ConnectionString ="Data Source=.;Initial Catalog=master;User ID=sa;pwd =teny123";
conn.Open ();
string sql="SELECT spid FROM sysprocesses ,sysdatabases WHERE sysprocesses.dbid=sysdatabases.dbid AND sysdatabases.Name='pubs'"; SqlCommand cmd1 = new SqlCommand(sql, conn);
SqlDataReader dr; ArrayList list = new ArrayList(); try
{
dr = cmd1.ExecuteReader();
while(dr.Read())
{
list.Add(dr.GetInt16(0));
}
dr.Close(); }
catch(SqlException eee)
{
MessageBox.Show (eee.ToString ());
}
finally
{
conn.Close ();
} MessageBox.Show (list.Count .ToString ());
for(int i = 0; i < list.Count; i++)
{
cmd1 = new SqlCommand(string.Format("KILL {0}", list[i].ToString ()), conn);
cmd1.ExecuteNonQuery(); MessageBox.Show ("you have killed thread "+list[i].ToString ()); } //这里一定要是master数据库,而不能是要还原的数据库,因为这样便变成了有其它进程
//占用了数据库。
string constr = @"Data Source=.;Initial Catalog=master;User ID=sa;pwd =teny123 ";
string database = "pubs";
string path = @"d:\1.mdf";
//string BACKUP = String.Format("backup database {0} to disk='{1}'", database, path);
string BACKUP = String.Format("RESTORE DATABASE {0} FROM DISK = '{1}'", database, path); SqlConnection con = new SqlConnection(constr);
SqlCommand cmd = new SqlCommand(BACKUP, con);
con.Open();
try
{
cmd.ExecuteNonQuery();
MessageBox.Show("还原成功");
}
catch(SqlException ee)
{
//throw(ee); //MessageBox.Show("还原失败"); MessageBox.Show (ee.ToString ()); }
finally
{
con.Close();
}
return "成功与否字符串"; //return "restore successfully!";
}