C# SQLSERVER2008数据库备份和还原的两种方法 (有进度条)

C# SQLSERVER2008数据库备份和还原的两种方法 (有进度条)

1. :方法一（不使用SQLDMO）：

2.  

3. ///

4. ///备份方法

5. ///

6. SqlConnectionconn=newSqlConnection("Server=.;Database=master;UserID=sa;Password=sa;");

7.  

8. SqlCommandcmdBK=newSqlCommand();

9. cmdBK.CommandType=CommandType.Text;

10. cmdBK.Connection=conn;

11. cmdBK.CommandText=@"backupdatabasetesttodisk='C:/ba'withinit";

12.  

13. try

14. {

15. conn.Open();

16. cmdBK.ExecuteNonQuery();

17. MessageBox.Show("Backupsuccessed.");

18. }

19. catch(Exceptionex)

20. {

21. MessageBox.Show(ex.Message);

22. }

23. finally

24. {

25. conn.Close();

26. conn.Dispose();

27. }

28.  

29.  

30. ///

31. ///还原方法

32. ///

33. SqlConnectionconn=newSqlConnection("Server=.;Database=master;UserID=sa;Password=sa;Trusted_Connection=False");

34. conn.Open();

35.  

36. //KILLDataBaseProcess

37. SqlCommandcmd=newSqlCommand("SELECTspidFROMsysprocesses,sysdatabasesWHEREsysprocesses.dbid=sysdatabases.dbidANDsysdatabases.Name='test'",conn);

38. SqlDataReaderdr;

39. dr=cmd.ExecuteReader();

40. ArrayListlist=newArrayList();

41. while(dr.Read())

42. {

43. list.Add(dr.GetInt16(0));

44. }

45. dr.Close();

46. for(inti=0;i<list.Count;i++)

47. {

48. cmd=newSqlCommand(string.Format("KILL{0}",list),conn);

49. cmd.ExecuteNonQuery();

50. }

51.  

52. SqlCommandcmdRT=newSqlCommand();

53. cmdRT.CommandType=CommandType.Text;

54. cmdRT.Connection=conn;

55. cmdRT.CommandText=@"restoredatabasetestfromdisk='C:/ba'";

56.  

57. try

58. {

59. cmdRT.ExecuteNonQuery();

60. MessageBox.Show("Restoresuccessed.");

61. }

62. catch(Exceptionex)

63. {

64. MessageBox.Show(ex.Message);

65. }

66. finally

67. {

68. conn.Close();

69. }

 

 

1. 方法二（使用SQLDMO）：

2.  

3. ///

4. ///备份方法

5. ///

6. SQLDMO.Backupbackup=newSQLDMO.BackupClass();

7. SQLDMO.SQLServerserver=newSQLDMO.SQLServerClass();

8. //显示进度条

9. SQLDMO.BackupSink_PercentCompleteEventHandlerprogress=newSQLDMO.BackupSink_PercentCompleteEventHandler(Step);

10. backup.PercentComplete+=progress;

11.  

12. try

13. {

14. server.LoginSecure=false;

15. server.Connect(".","sa","sa");

16. backup.Action=SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;

17. backup.Database="test";

18. backup.Files=@"D:/test/myProg/backupTest";

19. backup.BackupSetName="test";

20. backup.BackupSetDescription="Backupthedatabaseoftest";

21. backup.Initialize=true;

22. backup.SQLBackup(server);

23. MessageBox.Show("Backupsuccessed.");

24. }

25. catch(Exceptionex)

26. {

27. MessageBox.Show(ex.Message);

28. }

29. finally

30. {

31. server.DisConnect();

32. }

33. this.pbDB.Value=0;

34.  

35.  

36. ///

37. ///还原方法

38. ///

39. SQLDMO.Restorerestore=newSQLDMO.RestoreClass();

40. SQLDMO.SQLServerserver=newSQLDMO.SQLServerClass();

41. //显示进度条

42. SQLDMO.RestoreSink_PercentCompleteEventHandlerprogress=newSQLDMO.RestoreSink_PercentCompleteEventHandler(Step);

43. restore.PercentComplete+=progress;

44.  

45. //KILLDataBaseProcess

46. SqlConnectionconn=newSqlConnection("Server=.;Database=master;UserID=sa;Password=sa;Trusted_Connection=False");

47. conn.Open();

48. SqlCommandcmd=newSqlCommand("SELECTspidFROMsysprocesses,sysdatabasesWHEREsysprocesses.dbid=sysdatabases.dbidANDsysdatabases.Name='test'",conn);

49. SqlDataReaderdr;

50. dr=cmd.ExecuteReader();

51. ArrayListlist=newArrayList();

52. while(dr.Read())

53. {

54. list.Add(dr.GetInt16(0));

55. }

56. dr.Close();

57. for(inti=0;i<list.Count;i++)

58. {

59. cmd=newSqlCommand(string.Format("KILL{0}",list),conn);

60. cmd.ExecuteNonQuery();

61. }

62. conn.Close();

63.  

64. try

65. {

66. server.LoginSecure=false;

67. server.Connect(".","sa","sa");

68. restore.Action=SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;

69. restore.Database="test";

70. restore.Files=@"D:/test/myProg/backupTest";

71. restore.FileNumber=1;

72. restore.ReplaceDatabase=true;

73. restore.SQLRestore(server);

74. MessageBox.Show("Restoresuccessed.");

75. }

76. catch(Exceptionex)

77. {

78. MessageBox.Show(ex.Message);

79. }

80. finally

81. {

82. server.DisConnect();

83. }

84. this.pbDB.Value=0;