C＃操作数据库备份和还原

 

1. C#实现SQLSERVER2000数据库备份还原的两种方法   
2. : 方法一（不使用SQLDMO）：   
3.   
4. ///   
5. ///备份方法   
6. ///   
7. SqlConnection conn = new SqlConnection("Server=.;Database=master;User ID=sa;Password=sa;");   
8.   
9. SqlCommand cmdBK = new SqlCommand();   
10. cmdBK.CommandType = CommandType.Text;   
11. cmdBK.Connection = conn;   
12. cmdBK.CommandText = @"backup database test to disk='C:\ba' with init";   
13.   
14. try  
15. {   
16. conn.Open();   
17. cmdBK.ExecuteNonQuery();   
18. MessageBox.Show("Backup successed.");   
19. }   
20. catch(Exception ex)   
21. {   
22. MessageBox.Show(ex.Message);   
23. }   
24. finally  
25. {   
26. conn.Close();   
27. conn.Dispose();   
28. }   
29.   
30.   
31. ///   
32. ///还原方法   
33. ///   
34. SqlConnection conn = new SqlConnection("Server=.;Database=master;User ID=sa;Password=sa;Trusted_Connection=False");   
35. conn.Open();   
36.   
37. //KILL DataBase Process   
38. SqlCommand cmd = new SqlCommand("SELECT spid FROM sysprocesses ,sysdatabases WHERE sysprocesses.dbid=sysdatabases.dbid AND sysdatabases.Name='test'", conn);   
39. SqlDataReader dr;   
40. dr = cmd.ExecuteReader();   
41. ArrayList list = new ArrayList();   
42. while(dr.Read())   
43. {   
44. list.Add(dr.GetInt16(0));   
45. }   
46. dr.Close();   
47. for(int i = 0; i < list.Count; i++)   
48. {   
49. cmd = new SqlCommand(string.Format("KILL {0}", list), conn);   
50. cmd.ExecuteNonQuery();   
51. }   
52.   
53. SqlCommand cmdRT = new SqlCommand();   
54. cmdRT.CommandType = CommandType.Text;   
55. cmdRT.Connection = conn;   
56. cmdRT.CommandText = @"restore database test from disk='C:\ba'";   
57.   
58. try  
59. {   
60. cmdRT.ExecuteNonQuery();   
61. MessageBox.Show("Restore successed.");   
62. }   
63. catch(Exception ex)   
64. {   
65. MessageBox.Show(ex.Message);   
66. }   
67. finally  
68. {   
69. conn.Close();   
70. }   
71.   
72.   
73.   
74.   
75.   
76. 方法二（使用SQLDMO）：   
77.   
78. ///   
79. ///备份方法   
80. ///   
81. SQLDMO.Backup backup = new SQLDMO.BackupClass();   
82. SQLDMO.SQLServer server = new SQLDMO.SQLServerClass();   
83. //显示进度条   
84. SQLDMO.BackupSink_PercentCompleteEventHandler progress = new SQLDMO.BackupSink_PercentCompleteEventHandler(Step);   
85. backup.PercentComplete += progress;   
86.   
87. try  
88. {   
89. server.LoginSecure = false;   
90. server.Connect(".", "sa", "sa");   
91. backup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;   
92. backup.Database = "test";   
93. backup.Files = @"D:\test\myProg\backupTest";   
94. backup.BackupSetName = "test";   
95. backup.BackupSetDescription = "Backup the database of test";   
96. backup.Initialize = true;   
97. backup.SQLBackup(server);   
98. MessageBox.Show("Backup successed.");   
99. }   
100. catch(Exception ex)   
101. {   
102. MessageBox.Show(ex.Message);   
103. }   
104. finally  
105. {   
106. server.DisConnect();   
107. }   
108. this.pbDB.Value = 0;   
109.   
110.   
111. ///   
112. ///还原方法   
113. ///   
114. SQLDMO.Restore restore = new SQLDMO.RestoreClass();   
115. SQLDMO.SQLServer server = new SQLDMO.SQLServerClass();   
116. //显示进度条   
117. SQLDMO.RestoreSink_PercentCompleteEventHandler progress = new SQLDMO.RestoreSink_PercentCompleteEventHandler(Step);   
118. restore.PercentComplete += progress;   
119.   
120. //KILL DataBase Process   
121. SqlConnection conn = new SqlConnection("Server=.;Database=master;User ID=sa;Password=sa;Trusted_Connection=False");   
122. conn.Open();   
123. SqlCommand cmd = new SqlCommand("SELECT spid FROM sysprocesses ,sysdatabases WHERE sysprocesses.dbid=sysdatabases.dbid AND sysdatabases.Name='test'", conn);   
124. SqlDataReader dr;   
125. dr = cmd.ExecuteReader();   
126. ArrayList list = new ArrayList();   
127. while(dr.Read())   
128. {   
129. list.Add(dr.GetInt16(0));   
130. }   
131. dr.Close();   
132. for(int i = 0; i < list.Count; i++)   
133. {   
134. cmd = new SqlCommand(string.Format("KILL {0}", list), conn);   
135. cmd.ExecuteNonQuery();   
136. }   
137. conn.Close();   
138.   
139. try  
140. {   
141. server.LoginSecure = false;   
142. server.Connect(".", "sa", "sa");   
143. restore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;   
144. restore.Database = "test";   
145. restore.Files = @"D:\test\myProg\backupTest";   
146. restore.FileNumber = 1;   
147. restore.ReplaceDatabase = true;   
148. restore.SQLRestore(server);   
149. MessageBox.Show("Restore successed.");   
150. }   
151. catch(Exception ex)   
152. {   
153. MessageBox.Show(ex.Message);   
154. }   
155. finally  
156. {   
157. server.DisConnect();   
158. }   
159. this.pbDB.Value = 0;  

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 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())
{
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 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())
{
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;