C#.net做sql数据库备份还原。。。
好几天没见大家了，散点小分~~~顺便学习下：
本人新手，使用C#，sql2000，做.net程序。。
大家觉得在.net里有必要做数据库的备份与还原吗？如果做，有哪些思路、方法？请大家多多指教，谢谢！
[解决办法]
在.NET怎么做备份和还原，迷糊
[解决办法]
- C# code
使用.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;
[解决办法]
2楼的做法分别是我以前和现在的做法。应该可用。
[解决办法]
2楼
[解决办法]
借花献佛
- 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.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; }
[解决办法]