使用.net备份和还原数据库C#实现SQLSERVER2000数据库备份还原的两种方法: 方法一（不使用SQLDMO）：//////备份方法///SqlConnection conn = new SqlConnection("Server=.;Database=master;User ID=sa;Password=sa;");SqlCommand cmdBK = new SqlCommand();cmdBK.CommandType = CommandType.Text;cmdBK.Connection = conn;cmdBK.CommandText = @"backup database test to disk='C:\ba' with init";try{conn.Open();cmdBK.ExecuteNonQuery();MessageBox.Show("Backup successed.");}catch(Exception ex){MessageBox.Show(ex.Message);}finally{conn.Close();conn.Dispose();}//////还原方法///SqlConnection conn = new SqlConnection("Server=.;Database=master;User ID=sa;Password=sa;Trusted_Connection=False");conn.Open();//KILL DataBase ProcessSqlCommand 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()){list.Add(dr.GetInt16(0));}dr.Close();for(int i = 0; i < list.Count; i++){cmd = new SqlCommand(string.Format("KILL {0}", list), conn);cmd.ExecuteNonQuery();}SqlCommand cmdRT = new SqlCommand();cmdRT.CommandType = CommandType.Text;cmdRT.Connection = conn;cmdRT.CommandText = @"restore database test from disk='C:\ba'";try{cmdRT.ExecuteNonQuery();MessageBox.Show("Restore successed.");}catch(Exception ex){MessageBox.Show(ex.Message);}finally{conn.Close();}方法二（使用SQLDMO）：//////备份方法///SQLDMO.Backup backup = new SQLDMO.BackupClass();SQLDMO.SQLServer server = new SQLDMO.SQLServerClass();//显示进度条SQLDMO.BackupSink_PercentCompleteEventHandler progress = new SQLDMO.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 = "Backup the database of test";backup.Initialize = true;backup.SQLBackup(server);MessageBox.Show("Backup successed.");}catch(Exception ex){MessageBox.Show(ex.Message);}finally{server.DisConnect();}this.pbDB.Value = 0;//////还原方法///SQLDMO.Restore restore = new SQLDMO.RestoreClass();SQLDMO.SQLServer server = new SQLDMO.SQLServerClass();//显示进度条SQLDMO.RestoreSink_PercentCompleteEventHandler progress = new SQLDMO.RestoreSink_PercentCompleteEventHandler(Step);restore.PercentComplete += progress;//KILL DataBase ProcessSqlConnection 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()){list.Add(dr.GetInt16(0));}dr.Close();for(int i = 0; i < list.Count; i++){cmd = new SqlCommand(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("Restore successed.");}catch(Exception ex){MessageBox.Show(ex.Message);}finally{server.DisConnect();}this.pbDB.Value = 0; （ 用SQL语句实现对数据库备份还原操作 备份SqlServer数据库: backup database 数据库名 to disk (备份文件存放路径+文件名).bak 还原SqlServer数据库: string path = this.FileUpload1.PostedFile.FileName; //获得备份路径及数据库名称 use master restore database 数据库名 from disk='" + path + "'"; ） using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Windows.Forms; using System.Collections; using System.Data.Sql; using System.IO; namespace 数据库备份 { public partial class Form1 : Form { //需要添加一个引用C:\Program Files\Microsoft SQL Server\80\Tools\Binn下的SQLDMO.DLL； //SQL默认安装下是在以上路径 //备份的文件放在C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP下 string ServerName = ""; string UserName = "sa";//暂时锁定，可以根据需要自己设置 string Password = "sa"; public Form1() { InitializeComponent(); } //获取服务器列表 public ArrayList GetServerList() { ArrayList alServers = new ArrayList(); SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass(); try { SQLDMO.NameList serverList = sqlApp.ListAvailableSQLServers(); for (int i = 1; i <= serverList.Count; i++) { alServers.Add(serverList.Item(i)); //comboBox1.Items.Add(serverList.Item(i)); listBox1.Items.Add(serverList.Item(i)); } } catch (Exception e) { throw (new Exception("取数据库服务器列表出错：" + e.Message)); } finally { sqlApp.Quit(); } return alServers; } //获取数据库列表 public ArrayList GetDbList(string strServerName, string strUserName, string strPwd) { string ServerName = strServerName; string UserName = strUserName; string Password = strPwd; ArrayList alDbs = new ArrayList(); SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass(); SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass(); try { svr.Connect(ServerName, UserName, Password); foreach (SQLDMO.Database db in svr.Databases) { if (db.Name != null) alDbs.Add(db.Name); listBox2.Items.Add(db.Name); } } catch (Exception e) { MessageBox.Show("连接数据库出错：" + e.Message); } finally { svr.DisConnect(); sqlApp.Quit(); } return alDbs; } //备份数据 public bool BackUPDB(string strDbName, string strFileName, ProgressBar pgbMain) { ProgressBar PBar = pgbMain; SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass(); try { svr.Connect(ServerName, UserName, Password); SQLDMO.Backup bak = new SQLDMO.BackupClass(); bak.Action = 0; bak.Initialize = true; SQLDMO.BackupSink_PercentCompleteEventHandler pceh = new SQLDMO.BackupSink_PercentCompleteEventHandler(Step); bak.PercentComplete += pceh; bak.Files = strFileName;//这里可以写成路径+文件名形式，自己写！ bak.Database = strDbName; bak.SQLBackup(svr); return true; } catch (Exception err) { throw (new Exception("备份数据库失败" + err.Message)); } finally { svr.DisConnect(); } } //进度条 private void Step(string message, int percent) { PBar.Visible = true; PBar.Value = percent; } 借花献佛 C# code（ 用SQL语句实现对数据库备份还原操作 备份SqlServer数据库: backup database 数据库名 to disk (备份文件存放路径+文件名).bak 还原SqlServer数据库: string path = this.FileUpload1.PostedFile.FileName; //获得备份路径及数据库名称 use master restore database 数据库名 from disk='" + path + "'"; ） using System; using System.Collections.…接上C# code //还原数据 public bool RestoreDB(string strDbName, string strFileName, ProgressBar pgbMain) { ProgressBar PBar = pgbMain; SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass(); try { svr.Connect(ServerName, UserName, Password); 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 = strFileName;//可以写成路径+文件名 res.Database = strDbName; res.ReplaceDatabase = true; res.SQLRestore(svr); return true; } catch (Exception err) { throw (new Exception("恢复数据库失败,请关闭所有和该数据库连接的程序！" + err.Message)); } finally { svr.DisConnect(); } } private void Form1_Load(object sender, EventArgs e) { GetServerList(); string ServerName = "(local)";//这里根据需要自己可以设置成动态的 GetDbList(ServerName, UserName, Password); } //根据选择的服务器，列出数据库 private void listBox1_SelectedIndexChanged(object sender, EventArgs e) { //这里可以动态设置服务器名，角色名，密码 //string ServerName = listBox1.SelectedItem.ToString(); //GetDbList(ServerName, UserName, Password); } private void SelectPath_Click(object sender, EventArgs e) { //选择保存路径 //if (folderBrowserDialog1.ShowDialog(this) == DialogResult.OK) //{ // string path = folderBrowserDialog1.SelectedPath + "\\"; // txtPath.Text = path; //} //else //{ // folderBrowserDialog1.Dispose(); //} } //备份按钮 private void btnSave_Click(object sender, EventArgs e) { string strDbName = "db_bookmanage"; txtSaveName.Text = DateTime.Now.ToString("yyyyMMddhhmmss"); string strFileName = txtSaveName.Text.ToString().Trim(); if (BackUPDB(strDbName, strFileName, PBar)) { PBar.Visible = false; MessageBox.Show("备份完成！"); } } //查看历史备份按钮 private void button2_Click(object sender, EventArgs e) { listBox3.Items.Clear(); string[] files = Directory.GetFiles(@"C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP"); foreach (string file in files) listBox3.Items.Add(file.Substring(file.LastIndexOf("\\") + 1, file.Length - file.LastIndexOf("\\") - 1)); } //还原数据按钮 private void button1_Click(object sender, EventArgs e) { PBar.Visible = true; string strDbName = "db_bookmanage"; //txtSaveName.Text = DateTime.Now.ToString("yyyyMMddhhmmss"); if (txtReName.Text == "") { MessageBox.Show("请选择一个要还原的文件！"); return; } string strFileName = txtReName.Text.ToString().Trim(); if (RestoreDB(strDbName, strFileName, PBar)) { PBar.Visible = false; MessageBox.Show("还原完成！"); } } //历史备份文件名 private void listBox3_SelectedIndexChanged(object sender, EventArgs e) { txtReName.Text = listBox3.SelectedItem.ToString(); } }}引用Interop.SQLDMO.dll后的注意事项。 SQLDMO.dll是个好东西，ASP.NET利用它可以实现在线备份、还原数据库等各种功能。近日有客户要求为其在后台添加一个管理数据库的功能。于是就出现了这篇文章。 由于客户的数据库和WEB服务不再同一台服务器，当我们把网站部署在服务器上以后，运行程序，提示如下错误：Retrieving the COM class factory for component with CLSID {10020200-E260-11CF-AE68-00AA004A34D5} failed due to the following error: 80040154. 而客户又不想在这台电脑安装MSSQL，所以我们只需要在没有安装MSSQL的电脑上注册SQLDMO.DLL组件。第一步：首先将msvcr71.dll, SQLDMO.DLL, Resources\2052\sqldmo.rll,Resources\1033\sqldmo.rll 拷贝到C:\Program Files\Microsoft SQL Server\80\Tools\Binn目录。下载SQLDMO文件 第二步：打开开始，在运行中输入 regsvr32 "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\sqldmo.dll" 注册sqldmo.dll。正常情况下，经过以上两个步骤，网页就应该可以访问了的。 但是我们经过以上两次操作后，访问网页依然提示如下错误：Retrieving the COM class factory for component with CLSID {10020200-E260-11CF-AE68-00AA004A34D5} failed due to the following error: 80070005.后经过一段时间的检查，我们发现C:\Program Files\文件夹仅有Administrator和System的控制权限，而没有其他任何用户的权限，因此我们为Microsoft SQL Server文件夹增加上Network Service 的读取权限。至此，问题得到解决！
