求助C#还原数据库

[longdunjun]

////////数据库换原
private void DataRestore()
{
this.Cursor = Cursors.WaitCursor;
this.lblMessage.Text = " 正在进行数据库的数据还原，这可能需要几秒到几十的时间，请稍候...";
this.lblMessage.Refresh();
this.progressData.Visible = true;
//------------------------------------------------------------------------------------

//string fileName = "Corp_MachineBak";
// string filePath = @System.Windows.Forms.Application.StartupPath+"\\Corp_MachineBak.bak";
string filePath=@"C:\Program Files\Corp_MachineBak.bak";
//string remark = "备份测试";

SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
oRestore.Action = 0 ;
SQLDMO.RestoreSink_PercentCompleteEventHandler pceh = new SQLDMO.RestoreSink_PercentCompleteEventHandler(Step);
oRestore.PercentComplete += pceh;
try
{
oSQLServer.Connect("localhost", "sa", "fly");
SQLDMO.QueryResults qr = oSQLServer.EnumProcesses(-1) ;
int iColPIDNum = -1 ;
int iColDbName = -1 ;

//杀死其它的连接进程
for(int i=1;i<=qr.Columns;i++)
{
string strName = qr.get_ColumnName(i) ;
if (strName.ToUpper().Trim() == "SPID")
{
iColPIDNum = i ;
}
else if (strName.ToUpper().Trim() == "DBNAME")
{
iColDbName = i ;
}
if (iColPIDNum != -1 && iColDbName != -1)
break ;
}

for(int i=1;i<=qr.Rows;i++)
{
int lPID = qr.GetColumnLong(i,iColPIDNum) ;
string strDBName = qr.GetColumnString(i,iColDbName) ;
if (strDBName.ToUpper() == "CgRecord".ToUpper())
oSQLServer.KillProcess(lPID) ;
}


oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
oRestore.Database = "corp_Machine";
oRestore.Files = filePath;
oRestore.FileNumber = 1;
oRestore.ReplaceDatabase = true;
oRestore.SQLRestore(oSQLServer);


}
catch(System.Exception ex)
{
MessageBox.Show("数据还原失败：\n" + ex.ToString());
}
finally
{
oSQLServer.DisConnect();
}

this.Cursor = Cursors.Default;




抛出一个异常，说数据库正在使用，未能获得对数据库的排他访问权``
请问怎么在C#里面控制数据库的排它访问权啊！·

[Knight94]

当有其他程序访问数据库的时候，会出现这种现象，关闭所有访问此数据库的程序，然后再执行你的操作。

[kssys]

杀死连接应该是这样：
1)SELECT spID FROM SysProcesses　　//获取其它连接
2)kill spID　　//遍历以上连接，杀死它们。记得不要把自己也杀死了，还有系统的也要注意

[lw8122]

收藏了

[飓风2000]

好贴

[Cry_Out]

//刚测试了一个代码

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!";


}

[阿牛138588]

楼上的，一个字形容：猛！

[andylauhai]

收藏了

[diasuler]

为什么不用数据库来做呢

[艺文Queen]

///备份
private void Backup(string fileName)
{
try
{
DbParam dp = new DbParam();
dp = FrmDbParam.GetDbParam();
this.ExecCommand("Backup database "+dp.DataBase+" to disk='"+fileName+"'",dp);
MessageBox.Show ("备份完成。\n文件保存在"+fileName,"系统提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
}
catch(Exception ex)
{
MessageBox.Show ("备份失败\n"+ex.Message,"系统提示",MessageBoxButtons.OK,MessageBoxIcon.Hand);
}


}
///
///还原方法
///
public void RestoreDatabase(string backfile,DbParam dp)
{

SqlConnection conn = new SqlConnection("Server="+dp.Server+";Database=master;User ID="+dp.UserID+";Password="+dp.Password+";Trusted_Connection=False");
conn.Open();

//KILL DataBase Process
SqlCommand cmd = new SqlCommand("SELECT spid FROM sysprocesses ,sysdatabases WHERE sysprocesses.dbid=sysdatabases.dbid AND sysdatabases.Name='"+dp.DataBase+"'", conn);
SqlDataReader dr;
dr = cmd.ExecuteReader();
ArrayList list = new ArrayList();
while(dr.Read())
{
list.Add(dr.GetInt16(0));
}
dr.Close();
for(int i = 0; i < list.Count; i++)
{
cmd = new SqlCommand(string.Format("KILL {0}", list[i].ToString()), conn);
cmd.ExecuteNonQuery();
}

SqlCommand cmdRT = new SqlCommand();
cmdRT.CommandType = CommandType.Text;
cmdRT.Connection = conn;
cmdRT.CommandText = @"restore database "+dp.DataBase+" from disk='"+backfile+"'";

try
{
cmdRT.ExecuteNonQuery();
MessageBox.Show("Restore successed.");
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
}
备份没有问题，可是我按上面说的方法还原后，数据没有变化？为什么？