//备份数据
conn.Open();
comm = new SqlCommand("backup database qykhgl to disk= 'c:\\a.bak',conn);
comm.ExecuteNonQuery();
conn.Close();
//恢复数据
conn.Open();
comm = new SqlCommand("restore database qykhgl from disk= 'c:\\a.bak',conn);
comm.ExecuteNonQuery();
conn.Close();
.......
为什么我使用以上的方法备份据库可以实现,但是我恢复备份的时候,就提示:数据库正在使用，无法进行排它访问.请高手指教!多谢!
conn.Open();
comm = new SqlCommand("backup database qykhgl to disk= 'c:\\a.bak',conn);
comm.ExecuteNonQuery();
conn.Close();
//恢复数据
conn.Open();
comm = new SqlCommand("restore database qykhgl from disk= 'c:\\a.bak',conn);
comm.ExecuteNonQuery();
conn.Close();
.......
为什么我使用以上的方法备份据库可以实现,但是我恢复备份的时候,就提示:数据库正在使用，无法进行排它访问.请高手指教!多谢!
你的这个问题是你正在用这个数据库，所以你无法来恢复！
我的解决主案是你应该在master库里面写一个存储过程
CREATE PROCEDURE RestoreDataBase
AS
alter database qykhgl set OFFLINE
restore database qykhgl from disk='c:\a.dat'
alter database qykhgl set ONLINE
GO
这样才能备份你的数据库！
{
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
this.Cursor = Cursors.WaitCursor;
this.label1.Text = "正在进行数据备份,这可能需要几秒或更长时间,请稍候...";
this.label1.Visible = true;
this.label1.Refresh();
this.pBar1.Visible = true;
//------------------------------------------------------------------------------------
string selfName = saveFileDialog1.FileName.Trim();
//string deviceName = "bozhaobeifen";
//string re = "备份测试";
//◆数据备份：
SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
oBackup.Action = 0 ;
oBackup.Initialize = true ;
SQLDMO.BackupSink_PercentCompleteEventHandler pceh = new SQLDMO.BackupSink_PercentCompleteEventHandler(Step);
oBackup.PercentComplete += pceh; try
{
oSQLServer.LoginSecure = false;
oSQLServer.Connect(gb.GetJQName(), "sa", "sa");
oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
oBackup.Database = strDbName;//数据库名
oBackup.Files = selfName;//文件路径
//oBackup.BackupSetName = deviceName;//备份名称
//oBackup.BackupSetDescription = re;//备份描述
oBackup.Initialize = true;
oBackup.SQLBackup(oSQLServer);
MessageBox.Show("数据库备份成功!","系统提示！",MessageBoxButtons.OK,MessageBoxIcon.Warning);
}
catch(System.Exception ex)
{
MessageBox.Show("数据库备份失败!请不要选择备份到桌面!\n\n"+ ex.ToString());
}
finally
{
oSQLServer.DisConnect();
} //------------------------------------------------------------------------------------
this.label1.Text = "";
this.label1.Visible = false;
this.pBar1.Visible = false;
this.Cursor = Cursors.Default;
}
} /// <summary>
/// 显示进度条
/// </summary>
private void Step(string message,int percent)
{
this.pBar1.Value = percent ;
}
public void RestoreDB(string strDbName)//数据恢复
{
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
this.label1.Visible = true;
this.label1.Refresh();
this.pBar1.Visible = true;
this.label1.Text = "进行数据库恢复,这可能需要几秒或更长时间,请稍候...";
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass() ;
try
{
svr.Connect(gb.GetJQName(),"sa","sa") ;
//杀死所有有关的进程
SQLDMO.QueryResults qr = svr.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() == strDbName.ToUpper())
svr.KillProcess(lPID) ;
} SQLDMO.Restore res = new SQLDMO.RestoreClass() ;
res.Action = 0 ;
SQLDMO.RestoreSink_PercentCompleteEventHandler pceh = new SQLDMO.RestoreSink_PercentCompleteEventHandler(Step);
res.PercentComplete += pceh;
res.Files = openFileDialog1.FileName.Trim() ;
res.Database = strDbName ;
res.ReplaceDatabase = true ;
res.SQLRestore(svr) ;
MessageBox.Show("数据库恢复成功!","系统提示！",MessageBoxButtons.OK,MessageBoxIcon.Warning);
}
catch(Exception err)
{
throw(new Exception("恢复数据库失败,请关闭所有和该数据库连接的程序!\n如果在桌面进行恢复请改变路径!\n\n"+err.Message)) ;
}
finally
{
svr.DisConnect() ;
}
this.label1.Visible = false;
this.pBar1.Visible = false;
}