C# SQLSERVER2008数据库备份和还原的两种方法 (有进度条)
C# SQLSERVER2008数据库备份和还原的两种方法 (有进度条):方法一（不使用SQLDMO）：
?
///
///备份方法
///
SqlConnectionconn=newSqlConnection("Server=.;Database=master;UserID=sa;Password=sa;");
?
SqlCommandcmdBK=newSqlCommand();
cmdBK.CommandType=CommandType.Text;
cmdBK.Connection=conn;
cmdBK.CommandText=@"backupdatabasetesttodisk='C:/ba'withinit";
?
try
{
conn.Open();
cmdBK.ExecuteNonQuery();
MessageBox.Show("Backupsuccessed.");
}
catch(Exceptionex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
conn.Dispose();
}
?
?
///
///还原方法
///
SqlConnectionconn=newSqlConnection("Server=.;Database=master;UserID=sa;Password=sa;Trusted_Connection=False");
conn.Open();
?
//KILLDataBaseProcess
SqlCommandcmd=newSqlCommand("SELECTspidFROMsysprocesses,sysdatabasesWHEREsysprocesses.dbid=sysdatabases.dbidANDsysdatabases.Name='test'",conn);
SqlDataReaderdr;
dr=cmd.ExecuteReader();
ArrayListlist=newArrayList();
while(dr.Read())
{
list.Add(dr.GetInt16(0));
}
dr.Close();
for(inti=0;i<list.Count;i++)
{
cmd=newSqlCommand(string.Format("KILL{0}",list),conn);
cmd.ExecuteNonQuery();
}
?
SqlCommandcmdRT=newSqlCommand();
cmdRT.CommandType=CommandType.Text;
cmdRT.Connection=conn;
cmdRT.CommandText=@"restoredatabasetestfromdisk='C:/ba'";
?
try
{
cmdRT.ExecuteNonQuery();
MessageBox.Show("Restoresuccessed.");
}
catch(Exceptionex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
:方法一（不使用SQLDMO）：
?
///
///备份方法
///
SqlConnectionconn=newSqlConnection("Server=.;Database=master;UserID=sa;Password=sa;");
?
SqlCommandcmdBK=newSqlCommand();
cmdBK.CommandType=CommandType.Text;
cmdBK.Connection=conn;
cmdBK.CommandText=@"backupdatabasetesttodisk='C:/ba'withinit";
?
try
{
conn.Open();
cmdBK.ExecuteNonQuery();
MessageBox.Show("Backupsuccessed.");
}
catch(Exceptionex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
conn.Dispose();
}
?
?
///
///还原方法
///
SqlConnectionconn=newSqlConnection("Server=.;Database=master;UserID=sa;Password=sa;Trusted_Connection=False");
conn.Open();
?
//KILLDataBaseProcess
SqlCommandcmd=newSqlCommand("SELECTspidFROMsysprocesses,sysdatabasesWHEREsysprocesses.dbid=sysdatabases.dbidANDsysdatabases.Name='test'",conn);
SqlDataReaderdr;
dr=cmd.ExecuteReader();
ArrayListlist=newArrayList();
while(dr.Read())
{
list.Add(dr.GetInt16(0));
}
dr.Close();
for(inti=0;i<list.Count;i++)
{
cmd=newSqlCommand(string.Format("KILL{0}",list),conn);
cmd.ExecuteNonQuery();
}
?
SqlCommandcmdRT=newSqlCommand();
cmdRT.CommandType=CommandType.Text;
cmdRT.Connection=conn;
cmdRT.CommandText=@"restoredatabasetestfromdisk='C:/ba'";
?
try
{
cmdRT.ExecuteNonQuery();
MessageBox.Show("Restoresuccessed.");
}
catch(Exceptionex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
?
?
方法二（使用SQLDMO）：
?
///
///备份方法
///
SQLDMO.Backupbackup=newSQLDMO.BackupClass();
SQLDMO.SQLServerserver=newSQLDMO.SQLServerClass();
//显示进度条
SQLDMO.BackupSink_PercentCompleteEventHandlerprogress=newSQLDMO.BackupSink_PercentCompleteEventHandler(Step);
backup.PercentComplete+=progress;
?
try
{
server.LoginSecure=false;
server.Connect(".","sa","sa");
backup.Action=SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
backup.Database="test";
backup.Files=@"D:/test/myProg/backupTest";
backup.BackupSetName="test";
backup.BackupSetDescription="Backupthedatabaseoftest";
backup.Initialize=true;
backup.SQLBackup(server);
MessageBox.Show("Backupsuccessed.");
}
catch(Exceptionex)
{
MessageBox.Show(ex.Message);
}
finally
{
server.DisConnect();
}
this.pbDB.Value=0;
?
?
///
///还原方法
///
SQLDMO.Restorerestore=newSQLDMO.RestoreClass();
SQLDMO.SQLServerserver=newSQLDMO.SQLServerClass();
//显示进度条
SQLDMO.RestoreSink_PercentCompleteEventHandlerprogress=newSQLDMO.RestoreSink_PercentCompleteEventHandler(Step);
restore.PercentComplete+=progress;
?
//KILLDataBaseProcess
SqlConnectionconn=newSqlConnection("Server=.;Database=master;UserID=sa;Password=sa;Trusted_Connection=False");
conn.Open();
SqlCommandcmd=newSqlCommand("SELECTspidFROMsysprocesses,sysdatabasesWHEREsysprocesses.dbid=sysdatabases.dbidANDsysdatabases.Name='test'",conn);
SqlDataReaderdr;
dr=cmd.ExecuteReader();
ArrayListlist=newArrayList();
while(dr.Read())
{
list.Add(dr.GetInt16(0));
}
dr.Close();
for(inti=0;i<list.Count;i++)
{
cmd=newSqlCommand(string.Format("KILL{0}",list),conn);
cmd.ExecuteNonQuery();
}
conn.Close();
?
try
{
server.LoginSecure=false;
server.Connect(".","sa","sa");
restore.Action=SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
restore.Database="test";
restore.Files=@"D:/test/myProg/backupTest";
restore.FileNumber=1;
restore.ReplaceDatabase=true;
restore.SQLRestore(server);
MessageBox.Show("Restoresuccessed.");
}
catch(Exceptionex)
{
MessageBox.Show(ex.Message);
}
finally
{
server.DisConnect();
}
this.pbDB.Value=0;?