利用C#实现SQL Server数据库备份/恢复一般有以下几种方法:
一、不使用SQLDMO:
Code
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**////
///备份方法
///
try
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/ContractedBlock.gif)
{
using(SqlConnection conn = new SqlConnection("Server=.;Database=master;User ID=sa;Password=sa;"))
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
SqlCommand cmdBK = new SqlCommand();
cmdBK.CommandType = CommandType.Text;
cmdBK.Connection = conn;
cmdBK.CommandText = @"BACKUP DATABASE test TO DISK='C:\ba' WITH INIT";
conn.Open();
cmdBK.ExecuteNonQuery();
MessageBox.Show("Backup successed.");
}
}
catch(Exception ex)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/ContractedBlock.gif)
{
MessageBox.Show(ex.Message);
}
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**////
///还原方法
///
try
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/ContractedBlock.gif)
{
SqlConnection conn = new SqlConnection("Server=.;Database=master;User ID=sa;Password=sa;Trusted_Connection=False");
conn.Open();
![](/Images/OutliningIndicators/InBlock.gif)
//KILL DataBase Process
SqlCommand cmd = new SqlCommand("SELECT spid FROM sysprocesses ,sysdatabases WHERE sysprocesses.dbid=sysdatabases.dbid AND sysdatabases.Name='test'", conn);
SqlDataReader dr;
dr = cmd.ExecuteReader();
ArrayList list = new ArrayList();
while(dr.Read())
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
list.Add(dr.GetInt16(0));
}
dr.Close();
for(int i = 0; i < list.Count; i++)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
cmd = new SqlCommand(string.Format("KILL {0}", list[i]), conn);
cmd.ExecuteNonQuery();
}
![](/Images/OutliningIndicators/InBlock.gif)
SqlCommand cmdRT = new SqlCommand();
cmdRT.CommandType = CommandType.Text;
cmdRT.Connection = conn;
cmdRT.CommandText = @"restore database test from disk='C:\ba'";
cmdRT.ExecuteNonQuery();
MessageBox.Show("Restore successed.");
}
catch(Exception ex)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/ContractedBlock.gif)
{
MessageBox.Show(ex.Message);
}
这种方法相对较为通用。
二、使用SQLDMO:
Code
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**////
///备份方法
///
SQLDMO.Backup backup = new SQLDMO.BackupClass();
SQLDMO.SQLServer server = new SQLDMO.SQLServerClass();
//显示进度条
SQLDMO.BackupSink_PercentCompleteEventHandler progress = new SQLDMO.BackupSink_PercentCompleteEventHandler(Step);
backup.PercentComplete += progress;
![](/Images/OutliningIndicators/None.gif)
try
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/ContractedBlock.gif)
{
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 = "Backup the database of test";
backup.Initialize = true;
backup.SQLBackup(server);
MessageBox.Show("Backup successed.");
}
catch(Exception ex)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/ContractedBlock.gif)
{
MessageBox.Show(ex.Message);
}
finally
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/ContractedBlock.gif)
{
server.DisConnect();
}
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**////
///还原方法
///
SQLDMO.Restore restore = new SQLDMO.RestoreClass();
SQLDMO.SQLServer server = new SQLDMO.SQLServerClass();
//显示进度条
SQLDMO.RestoreSink_PercentCompleteEventHandler progress = new SQLDMO.RestoreSink_PercentCompleteEventHandler(Step);
restore.PercentComplete += progress;
![](/Images/OutliningIndicators/None.gif)
//KILL DataBase Process
SqlConnection conn = new SqlConnection("Server=.;Database=master;User ID=sa;Password=sa;Trusted_Connection=False");
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT spid FROM sysprocesses ,sysdatabases WHERE sysprocesses.dbid=sysdatabases.dbid AND sysdatabases.Name='test'", conn);
SqlDataReader dr;
dr = cmd.ExecuteReader();
ArrayList list = new ArrayList();
while(dr.Read())
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/ContractedBlock.gif)
{
list.Add(dr.GetInt16(0));
}
dr.Close();
for(int i = 0; i < list.Count; i++)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/ContractedBlock.gif)
{
cmd = new SqlCommand(string.Format("KILL {0}", list[i]), conn);
cmd.ExecuteNonQuery();
}
conn.Close();
![](/Images/OutliningIndicators/None.gif)
try
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/ContractedBlock.gif)
{
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("Restore successed.");
}
catch(Exception ex)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/ContractedBlock.gif)
{
MessageBox.Show(ex.Message);
}
finally
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/ContractedBlock.gif)
{
server.DisConnect();
}