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

时间:2022-05-08 07:51:05
  1. :方法一(不使用SQLDMO):


  2. ///

  3. ///备份方法

  4. ///

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


  6. SqlCommandcmdBK=newSqlCommand();

  7. cmdBK.CommandType=CommandType.Text;

  8. cmdBK.Connection=conn;

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


  10. try

  11. {

  12. conn.Open();

  13. cmdBK.ExecuteNonQuery();

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

  15. }

  16. catch(Exceptionex)

  17. {

  18. MessageBox.Show(ex.Message);

  19. }

  20. finally

  21. {

  22. conn.Close();

  23. conn.Dispose();

  24. }



  25. ///

  26. ///还原方法

  27. ///

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

  29. conn.Open();


  30. //KILLDataBaseProcess

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

  32. SqlDataReaderdr;

  33. dr=cmd.ExecuteReader();

  34. ArrayListlist=newArrayList();

  35. while(dr.Read())

  36. {

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

  38. }

  39. dr.Close();

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

  41. {

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

  43. cmd.ExecuteNonQuery();

  44. }


  45. SqlCommandcmdRT=newSqlCommand();

  46. cmdRT.CommandType=CommandType.Text;

  47. cmdRT.Connection=conn;

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


  49. try

  50. {

  51. cmdRT.ExecuteNonQuery();

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

  53. }

  54. catch(Exceptionex)

  55. {

  56. MessageBox.Show(ex.Message);

  57. }

  58. finally

  59. {

  60. conn.Close();

  61. }

 

 

  1. 方法二(使用SQLDMO):


  2. ///

  3. ///备份方法

  4. ///

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

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

  7. //显示进度条

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

  9. backup.PercentComplete+=progress;


  10. try

  11. {

  12. server.LoginSecure=false;

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

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

  15. backup.Database="test";

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

  17. backup.BackupSetName="test";

  18. backup.BackupSetDescription="Backupthedatabaseoftest";

  19. backup.Initialize=true;

  20. backup.SQLBackup(server);

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

  22. }

  23. catch(Exceptionex)

  24. {

  25. MessageBox.Show(ex.Message);

  26. }

  27. finally

  28. {

  29. server.DisConnect();

  30. }

  31. this.pbDB.Value=0;



  32. ///

  33. ///还原方法

  34. ///

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

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

  37. //显示进度条

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

  39. restore.PercentComplete+=progress;


  40. //KILLDataBaseProcess

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

  42. conn.Open();

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

  44. SqlDataReaderdr;

  45. dr=cmd.ExecuteReader();

  46. ArrayListlist=newArrayList();

  47. while(dr.Read())

  48. {

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

  50. }

  51. dr.Close();

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

  53. {

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

  55. cmd.ExecuteNonQuery();

  56. }

  57. conn.Close();


  58. try

  59. {

  60. server.LoginSecure=false;

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

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

  63. restore.Database="test";

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

  65. restore.FileNumber=1;

  66. restore.ReplaceDatabase=true;

  67. restore.SQLRestore(server);

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

  69. }

  70. catch(Exceptionex)

  71. {

  72. MessageBox.Show(ex.Message);

  73. }

  74. finally

  75. {

  76. server.DisConnect();

  77. }

  78. this.pbDB.Value=0; 





    http://hi.baidu.com/xiangwei01270/item/dd67900dec8c16d175cd3c70