找了好幾個，這個例子還算不錯！
主要在還原數據庫時，有殺死其它連接進程的代碼
1 using System;
2 using System.Collections.Generic;
3 using System.ComponentModel;
4 using System.Data;
5 using System.Drawing;
6 using System.Text;
7 using System.Windows.Forms;
8 namespace Magicbit.Framework
9 {
10 public partial class DBTools : Form
11 {
12 private static DBTools _Instance = null;
13 public static DBTools Instance()
14 {
15 if (_Instance == null)
16 {
17 _Instance = new DBTools();
18 }
19 else
20 {
21 MessageBox.Show("已經有一個實例在運行！");
22 }
23 return _Instance;
24 }
25
26 public DBTools()
27 {
28 InitializeComponent();
29 }
30 private void BackAndRecoverDB_Load(object sender, EventArgs e)
31 {
32 this.txtSavePath.Text = Application.StartupPath;
33 //this.GetSQLServerList();
34 }
35 private void GetSQLServerList()
36 {
37 //get all available SQL Servers
38 SQLDMO._Application sqlApp = new SQLDMO.ApplicationClass();
39 SQLDMO.NameList sqlServers = sqlApp.ListAvailableSQLServers();
40 for (int i = 0; i < sqlServers.Count; i++)
41 {
42 object srv = sqlServers.Item(i + 1);
43 if (srv != null)
44 {
45 this.cboServers.Items.Add(srv);
46 }
47 }
48 if (this.cboServers.Items.Count > 0)
49 this.cboServers.SelectedIndex = 0;
50 else
51 this.cboServers.Text = "<No available SQL Servers>";
52
53 }
54 private void GetBackUpDB()
55 {
56 SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
57 SQLDMO.SQLServer srv = new SQLDMO.SQLServer();
58 srv.Connect(this.cboServers.Text.Trim(),this.txtUserName.Text.Trim(),this.txtPassWord.Text.Trim());
59 foreach (SQLDMO.Database db in srv.Databases)
60 {
61 if (db.Name != null)
62 this.cboDatabase.Items.Add(db.Name);
63 }
64
65 }
66 private void pictureBox1_Click(object sender, EventArgs e)
67 {
68 MessageBox.Show("歡迎使用數據庫備份、還原工具，本工具將協助你備份和還原數據庫，確保數據安全!", "備份您的數據庫");
69 }
70 private void button1_Click(object sender, EventArgs e)
71 {
72 this.GetBackUpDB();
73 }
74 private void BackUpDB()
75 {
76 string selfName = this.txtSavePath.Text.Trim() + @"" + this.cboDatabase.Text.Trim() + "_"+ System.DateTime.Now.ToString("yyyyMMddHHmmss")+".DAT";
77 string deviceName = this.cboDatabase.Text.Trim()+"bak";
78 string remark = "數據備份";
79 //BACKUP DB
80 SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
81 SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
82 oBackup.Action = 0 ;
83 oBackup.Initialize = true ;
84 SQLDMO.BackupSink_PercentCompleteEventHandler pceh = new SQLDMO.BackupSink_PercentCompleteEventHandler(Step);
85 oBackup.PercentComplete += pceh;
86 try
87 {
88 oSQLServer.LoginSecure = false;
89 oSQLServer.Connect(this.cboServers.Text.Trim(),this.txtUserName.Text.Trim(),this.txtPassWord.Text.Trim());
90 oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
91 oBackup.Database = this.cboDatabase.Text.Trim();//數據庫名
92 oBackup.Files = selfName;//文件路徑
93 oBackup.BackupSetName = deviceName;//備份名稱
94 oBackup.BackupSetDescription = remark;//備份描述
95 oBackup.Initialize = true;
96 oBackup.SQLBackup(oSQLServer);
97 }
98 catch(System.Exception ex)
99 {
100 MessageBox.Show("數據備份失敗： " + ex.ToString());
101 }
102 finally
103 {
104 oSQLServer.DisConnect();
105 }
106 }
107 private void Step(string message, int percent)
108 {
109 this.progressBar1.Value = percent;
110 }
111 private void button2_Click(object sender, EventArgs e)
112 {
113 this.Cursor = Cursors.WaitCursor;
114 this.label6.Visible = true;
115 this.progressBar1.Visible = true;
116 this.BackUpDB();
117 this.Cursor = Cursors.Default;
118 this.label6.Text = "備份已完成.";
119 }
120 public void RestoreDB()
121 {
122 string filePath = this.txtBackUpFile.Text.Trim();
123 SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
124 SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
125 oRestore.Action = 0 ;
126 SQLDMO.RestoreSink_PercentCompleteEventHandler pceh = new SQLDMO.RestoreSink_PercentCompleteEventHandler(Step);
127 oRestore.PercentComplete += pceh;
128 try
129 {
130 oSQLServer.Connect(this.cboServers.Text.Trim(),this.txtUserName.Text.Trim(),this.txtPassWord.Text.Trim());
131 SQLDMO.QueryResults qr = oSQLServer.EnumProcesses(-1) ;
132 int iColPIDNum = -1 ;
133 int iColDbName = -1 ;
134 //殺死其它的連接進程
135 for(int i=1;i<=qr.Columns;i++)
136 {
137 string strName = qr.get_ColumnName(i) ;
138 if (strName.ToUpper().Trim() == "SPID")
139 {
140 iColPIDNum = i ;
141 }
142 else if (strName.ToUpper().Trim() == "DBNAME")
143 {
144 iColDbName = i ;
145 }
146 if (iColPIDNum != -1 && iColDbName != -1)
147 break ;
148 }
149 for(int i=1;i<=qr.Rows;i++)
150 {
151 int lPID = qr.GetColumnLong(i,iColPIDNum) ;
152 string strDBName = qr.GetColumnString(i,iColDbName) ;
153 if (strDBName.ToUpper() == "CgRecord".ToUpper())
154 oSQLServer.KillProcess(lPID) ;
155 }
156 oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
157 oRestore.Database = this.cboDBtoBackup.Text;
158 oRestore.Files = filePath;
159 oRestore.FileNumber = 1;
160 oRestore.ReplaceDatabase = true;
161 oRestore.SQLRestore(oSQLServer);
162 }
163 catch(System.Exception ex)
164 {
165 MessageBox.Show("數據還原失敗： " + ex.ToString());
166 }
167 finally
168 {
169 oSQLServer.DisConnect();
170 }
171
172
173 }
174 private void button3_Click(object sender, EventArgs e)
175 {
176 this.folderBrowserDialog1.Description = "請選擇備份文件存放目錄";
177 this.folderBrowserDialog1.ShowNewFolderButton = true;
178 this.folderBrowserDialog1.ShowDialog();
179 this.txtSavePath.Text = this.folderBrowserDialog1.SelectedPath;
180 }
181 private void button4_Click(object sender, EventArgs e)
182 {
183 this.openFileDialog1.DefaultExt = "*.dat";
184 this.openFileDialog1.Title = "請選擇要還原的數據庫備份文件.";
185 this.openFileDialog1.ShowDialog();
186 this.txtBackUpFile.Text = this.openFileDialog1.FileName;
187 }
188 private void button5_Click(object sender, EventArgs e)
189 {
190 this.Cursor = Cursors.WaitCursor;
191 this.label6.Visible = true;
192 this.progressBar1.Visible = true;
193 this.RestoreDB();
194 this.Cursor = Cursors.Default;
195 this.label6.Text = "還原已完成.";
196 }
197 }
198}
