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; 

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值