sql server 数据库备份，还原。

还原代码 
  1 void __fastcall TFormMain::btnRestoreClick(TObject *Sender)
  2 {
  3 
  4     try{
  5         String fileName;
  6         bool   needDelete;
  7         needDelete = false;
  8         if (OpenDialog1->Execute()) {
  9             fileName = OpenDialog1->FileName;
 10         }else return;
 11 
 12         pnlTs->Top = gbHy->Top;
 13         pnlTs->Left = gbHy->Left;
 14 
 15 
 16         String sql = "Select * FROM SysDatabases where name = '"+cbbDBHY->Text+"'";
 17 
 18         ADOQ1->Close();
 19         ADOQ1->SQL->Text = sql;
 20         ADOQ1->Open();
 21         sql = "";
 22         if (ADOQ1->RecordCount==0) {
 23             if (!chkCreate->Checked) {
 24                 setStatusBar("数据库不存在，无法还原");
 25                 Application->MessageBox("数据库不存在，无法还原。", "提示", MB_OK
 26                     + MB_ICONINFORMATION);
 27                 return;
 28             }
 29         }
 30         if (chkUnRar->Checked && RightStr(fileName,4)==".rar") {
 31             setStatusBar("正在进行解压，请稍候...");
 32             pnlTs->Caption = "正在进行解压，请稍候...";
 33             needDelete = true;
 34             pnlTs->Visible=true;
 35             VCLUnZip1->ZipName  = fileName;
 36             VCLUnZip1->ReadZip();
 37             VCLUnZip1->DestDir  = ExtractFileDir(Application->ExeName);
 38             VCLUnZip1->DoAll    = true;
 39             VCLUnZip1->OverwriteMode = true;
 40             VCLUnZip1->RecreateDirs = true;
 41             VCLUnZip1->UnZip();
 42             fileName = ExtractFileName(LeftStr(fileName,fileName.Length()-4));
 43             if (!FileExists(ExtractFileDir(Application->ExeName)+"\\"+fileName)) {
 44                 setStatusBar("还原失败。原因【解压出错。】");
 45                 Application->MessageBox("还原失败。原因【解压出错。】", "提示", MB_OK +MB_ICONINFORMATION);
 46                 pnlTs->Visible=false;
 47                 return;
 48             }
 49             fileName =ExtractFileDir(Application->ExeName)+"\\"+fileName;
 50         }
 51 
 52 
 53         if (Application->MessageBox(("确定将数据还原到【"+cbbDBHY->Text+"】吗？").c_str(), "提示", MB_YESNO +
 54             MB_ICONQUESTION + MB_DEFBUTTON2) == IDNO) {
 55             pnlTs->Visible=false;
 56             return;
 57         }
 58         setStatusBar("正在进行还原，请稍候...");
 59         pnlTs->Caption = "正在进行还原，请稍候...";
 60         pnlTs->Visible = true;
 61         Application->ProcessMessages();
 62         if (chkDisconnect->Checked) {
 63 
 64             String kill = "if  exists   (select   *   from   dbo.sysobjects   where   id   =   object_id(N'[dbo].[sp_KillSpid]')   and   OBJECTPROPERTY(id,   N'IsProcedure')   =   1)   "
 65     "  drop   procedure   [dbo].[sp_KillSpid]   ";
 66             ADOQ1->Close();
 67             ADOQ1->SQL->Text = kill;
 68             ADOQ1->ExecSQL();
 69             kill = "  create   proc   sp_KillSpid   "
 70     "  @dbname   sysname        "
 71     "  as       "
 72     "  declare   @s   nvarchar(1000)   "
 73     "  declare   tb   cursor   local    "
 74     "  for   "
 75     "  select   N'kill   '+cast(spid   as   varchar)   "
 76     "  from   master..sysprocesses     "
 77     "  where   dbid=db_id(@dbname)   "
 78     "  open   tb     "
 79     "  fetch   next   from   tb   into   @s   "
 80     "  while   @@fetch_status=0   "
 81     "  begin   "
 82     "  exec(@s) "  
 83     "  fetch   next   from   tb   into   @s   "
 84     "  end   "
 85     "  close   tb   "
 86     "  deallocate   tb   ";
 87             ADOQ1->Close();
 88             ADOQ1->SQL->Text = kill;
 89             ADOQ1->ExecSQL();
 90             kill = "  exec   sp_KillSpid     '"+cbbDBHY->Text+"'";     //强制中断数据库连接
 91              ADOQ1->Close();
 92             ADOQ1->SQL->Text = kill;
 93             ADOQ1->ExecSQL();
 94 
 95         }
 96         sql = "restore filelistonly from disk=N'"+fileName+  "'";   //获取备份数据库名称、
 97          ADOQ1->Close();
 98         ADOQ1->SQL->Text = sql;
 99         ADOQ1->Open();
100         String data,log,all,fileLoad;//数据文件，日志文件，全名，数据文件路径
101          all = ADOQ1->FieldByName("LogicalName")->AsString;
102         data = all.SubString(1,all.Pos("_Data")-1) ;
103         log  = data+"_Log";
104         data = data+"_Data";
105         sql = "Select * FROM SysDatabases where name = 'master'";
106         ADOQ1->Close();
107         ADOQ1->SQL->Text = sql;
108         ADOQ1->Open();
109         if (ADOQ1->RecordCount==0) {
110             Application->MessageBox("还原出错，找不到master数据库。", "提示",
111                 MB_OK + MB_ICONINFORMATION);
112             pnlTs->Visible=false;
113             return;
114         }
115         fileLoad = ADOQ1->FieldByName("filename")->AsString;
116         fileLoad = fileLoad.SubString(1,fileLoad.Pos("master.mdf")-1);
117         sql =                                                                //还原数据库
118       "RESTORE   DATABASE   ["+cbbDBHY->Text+"]   FROM     DISK   =   N'"+fileName+
119       "'   WITH     FILE   =   1,     NOUNLOAD   ,     STATS   =   10,"
120       " move '"+data+"' to '"+fileLoad+cbbDBHY->Text+".mdf' ,"
121       " move '"+log+"' to '"+fileLoad+cbbDBHY->Text+".ldf' ,"
122       " REPLACE";
123         r1->Text = r1->Text +"\r" + sql;
124         ADOQ1->Close();
125         ADOQ1->SQL->Text = sql;
126         ADOQ1->ExecSQL();
127         if (chkPw->Checked) {
128             sql = "update ["+cbbDBHY->Text+"].[dbo].[t_user] set [password]='retow'  "
129                   "update ["+cbbDBHY->Text+"].[dbo].[t_hospital] set name='锐拓软件'";
130             ADOQ1->Close();
131             ADOQ1->SQL->Text = sql;
132             ADOQ1->ExecSQL();
133         }
134         if (needDelete==true && FileExists(fileName)) {
135            DeleteFile(fileName);
136         }
137         setStatusBar("数据还原完成");
138         Application->MessageBox(("数据库【"+cbbDBHY->Text+"】还原完成。").c_str(), "提示", MB_OK
139                 + MB_ICONINFORMATION);
140       }catch(Exception &E){
141           setStatusBar("数据还原失败");
142           Application->MessageBox(("出错【"+E.Message+"】\r操作终止").c_str(), "提示", MB_OK + MB_ICONINFORMATION);
143       }
144       pnlTs->Visible=false;
145 }

 

 

备份代码 
 1     if (SaveDialog1->Execute()) {
 2         try{
 3             pnlTs->Caption = "正在进行备份，请稍候...";
 4             setStatusBar("正在进行备份，请稍候...");
 5             pnlTs->Top = gbBf->Top;
 6             pnlTs->Left = gbBf->Left;
 7             pnlTs->Visible = true;
 8             Application->ProcessMessages();
 9             String NewFile = SaveDialog1->FileName;
10             String sql ="BACKUP DATABASE "+cbbDB->Text+" TO DISK ='"+NewFile+"' with init ";
11             ADOQ1->Close();
12             ADOQ1->SQL->Text = sql;
13             ADOQ1->ExecSQL();
14             if (chkRar->Checked) {
15                 VCLZip1->ZipName  = NewFile +".rar";
16                 VCLZip1->Recurse  = true;
17                 VCLZip1->FilesList->Add(NewFile);
18                 VCLZip1->OverwriteMode = true;
19                 VCLZip1->Zip();
20                 DeleteFile(NewFile);
21                 NewFile = NewFile+".rar";
22             }
23             setStatusBar("数据备份完成");
24             if (Application->MessageBox(("数据库【"+cbbDB->Text+"】备份完成，路径【"+NewFile+"】，是否打开目录？").c_str(), "提示", MB_YESNO +
25                 MB_ICONQUESTION) == IDYES) {
26                 ShellExecute(this->Handle,"open", "","",(ExtractFileDir(NewFile)).c_str(),SW_SHOWDEFAULT);
27             }
28 
29 //            Application->MessageBox(("数据库【"+cbbDB->Text+"】备份完成，路径【"+NewFile+"】").c_str(), "提示", MB_OK
30 //                + MB_ICONINFORMATION);
31         }catch(Exception &E){
32             setStatusBar("数据备份出错");
33             Application->MessageBox(("出错【"+E.Message+"】\r操作终止").c_str(), "提示", MB_OK + MB_ICONINFORMATION);
34         }
35         pnlTs->Visible=false;
36 
37     }
38 
39     //这里指定需要备份数据库的路径和文件名,注意:路径的文件夹是必须已经创建的.文件名可以使用日期来标示

 

posted on 2010-03-15 14:27  yxh  阅读(286)  评论(0)  编辑  收藏  举报