在VFP裡操作和管理SQL SERVER(介紹SQL-DMO)轉帖 SQL SERVER2000Database Backup和恢複預存程序

在論壇混了這麼多年， 看到的一個很明顯的趨勢是， 用VFP和SQL SERVER來做系統的網友越來越多。怎麼從VFP裡來操作或者管理SQL SERVER也成為很多人關心的問題。 對SQL SERVER的資料操作，可以用視圖，SPT或者ADO來做，但對伺服器本身的管理，似乎只有用SPT來發送一些命令了。其實， 微軟為用戶端操作SQL SERVER開發了一套完整的工具， 這就是SQL - DMO (Distributed Management Objects). 這是一套COM組件，可以在各種語言裡使用， VFP裡自然也可以用了。
我準備花點時間，寫些這方面的介紹文章，然後做個執行個體，算是我通過MCDBA後，給大家的禮物吧。下面的介紹和例子，是針對SQL SERVER 2000和VFP7的。

一. 用SQL -DMO的必要性

大部分VFPER開發的程式，都是中小規模的系統，因為這是VFP最適用的範圍。而這樣的系統，很多情況下是，用戶端沒有專業IT人士維護，更不要說專門的DBA了。對於使用SQL SERVER的系統，就帶來了一些問題。 SQL SERVER是一套比較大的資料庫專業軟體， 是經常需要維護和清理的，而我們這些系統開發人員， 總不能老跑去使用者那裡做維護工作吧，尤其對商業軟體來說，這是很大的成本了。如果可以把對SQL SERVER的維護工作放到自己的程式裡，甚至把SQL SERVER的安裝無縫併入到自己的程式的安裝包裡，相信大家都會很樂意的。 DMO的功能就能滿足我們的這些需要。通過它，可以從程式裡用命令方式來操作SQL SERVER：停止/啟動服務，建立資料庫和表，添加使用者和許可權，備份/恢複資料庫，資料匯入/匯出/發布...  DMO的作用就是把我們能在Enterprise Manager裡完成的功能，完全用代碼來做.

二. 微軟的案頭資料引擎 (Desktop Database Engine)

我們都知道SQL SERVER 2000有四個版本: 個人版，開發人員版，標準班和企業版. 其實，還有一個版本，就是案頭資料引擎(DDE)。 DDE幾乎具有
SQL SERVER標準版和企業版的所有功能，不能支援的只是少數幾個不常用的功能。而它和其它版本的區別是， DDE沒有任何圖形管理介面，沒有ENTERPRISE MANAGER，沒有查詢分析器等等工具。 對它的管理，主要是通過DMO或者專門的API來進行，它的另一個優點是， 提供了安裝程式，可以直接加到第3方軟體裡進行安裝。每個版本的SQL SERVER CD裡都帶有DDE， 在MSDE目錄下，運行SETUP程式就可以安裝。至於怎麼把它加到自己的程式裡安裝，大家查一下SQL SERVER的協助檔案就可以找到。
DDE的限制是： 只適宜中小型系統(比如沒有5個以上的使用者同時串連和運行大型SQL命令)， 如果同時訪問的使用者很多， DDE就會比正常的SQL SERVER慢了。

這樣， DDE+DMO就可以實現我們上面所要求的功能了。這種做法甚至可以不讓使用者知道你是在使用SQL SERVER做為資料庫。當然， DMO不是只能操作DDE，它可以操作所有版本的SQL SERVER。

三. DMO的安裝

其實DMO主要就是一個DLL檔案而已: SQLDMO.DLL。具體位置在C:/Program Files/Microsoft SQL Server/80/Tools/Binn目錄裡, 在同一目錄裡還有個協助檔案Sqldmo80.hlp. 如果你在程式裡不能使用DMO， 找到這個DLL檔案，註冊一下，就可以了。另外在C:/Program Files/Microsoft SQL Server/80/Tools/ Devtools/Samples/Sqldmo 目錄下，有SQLDMO的例子，但都是VB和VC++的。VB的例子和VFP很接近的.

四. 初步接觸DMO

在這裡寫幾個簡單的句子，來看看DMO的強大. 這些命令可以直接在命令視窗裡一行一行測試, 只是用它們來顯示資料庫的資訊。至於添加資料庫，資料表，備份和恢複等比較複雜的功能，得在執行個體裡做。

oServer=CreateObject("SQLDMO.SQLServer") &&建立SERVER對象
oServer.Connect("Snoopy","sa","778899") &&串連到你的伺服器
? oServer.Databases.Count  &&顯示伺服器上的資料庫總數
? oServer.Databases.Item(1).Name  &&顯示第一個資料庫的名字

oDB=oServer.Databases("PUBS") &&建立資料庫物件
? oDB.Tables.Count  &&顯示庫裡表的數目
oTable=oDB.Tables("Titles")    &&建立表對象
? oTable.Rows  &&顯示表裡的記錄數
? oTable.Columns.Count &&顯示表的列數

**顯示列的屬性
? oTable.Columns(1).Name
? oTable.Columns(1).type
? oTable.Columns(1).Datatype
? oTable.Columns(1).Identity
? oTable.Columns(1).InPrimaryKey

例子：

Backup和Restore資料庫

oServer=CreateObject("SQLDMO.SQLServer") &&建立SERVER對象
oServer.Connect("Snoopy","sa","778899") &&串連到你的伺服器

**備份
oBackup=CreateObject("SQLDMO.Backup") &&建立備份對象
oBackup.Database="PUBS" &&指定備份資料庫
oBackup.Password="8899" &&給備份檔案加密碼
oBackup.Files="D:/Temp/PUBBack.Dat" &&指定目標檔案
oBackup.SQLBackup(oServer) &&運行備份命令，速度很快的

**備份恢複
oRestore=CreateObject("SQLDMO.Restore")
oRestore.Database="PUBS"
oRestore.Files="D:/Temp/PUBBack.Dat"
oRestore.SQLRestore
oRestore.SQLRestore(oServer) &&先試不加密碼，備份失敗
oRestore.Password="8899"
oRestore.SQLRestore(oServer)

SQL SERVER提供幾種Database Backup方式，
一種是完整備份 (Full Backup), 第二種是差異備份(Differential Backup)， 第三種是記錄備份， 第四種是檔案備份

完整備份是把整個資料庫做個備份，差異備份只是備份進行了完整備份後資料庫裡的新變化。當資料庫很大時， 完整備份很費空間和時間，可以根據情況定期做，比如每周或者每月做一次完整備份。 其它時間可以進行差異備份，比如每天一次，或者半天一次， 也可以結合進行記錄備份。檔案備份是直接備份資料庫的資料和記錄檔。和在作業系統裡做備份一樣.

在恢複的時候，只要先恢複完整備份，然後恢複最後一個差異備份就可以。如果有記錄備份，還需要恢複差異備份後所做的記錄備份。

備份種類通過 備份對象的Action屬性來決定.
oBackup.Action=0 && 參數： 0 - 完整備份,1- 差異備份,  2- 檔案備份, 3 -記錄備份

 

Server對象的一些屬性和方法

屬性
AutoReConnect: 當串連斷開，後是否自動重連
ConnectionID: 本串連的ID
HostName: 本地機器的名字
Isdbcreator, Isdiskadmin, Isprocessadmin, Issecurityadmin, Isserveradmin, Issetupadmin, Issysadmin: 返回當前串連使用者是否屬於某類SERVER ROLE，比如是不是SA

LoginTimeout: 決定連線逾時時間，預設為60秒
SaLogin: 當前串連是不是SA
Status：伺服器目前狀態
 VersionMajor,VersionMinor: SQL SERVER的版本號碼

方法:

AttachDB, DetachDB: 添加或者剝離資料庫
Connect, Disconnect, Close: 串連， 斷開，或者關閉當前伺服器對象(oServer)
VerifyConnection: 測試當前串連是否斷開
Reconnect: 重新串連
Start, Stop, Shutdown: 用來啟動，停止或者關閉伺服器
BeginTransaction, RollbackTransaction, CommitTransaction: 操作事務
EnumServerAttributes: 返回伺服器的大部分設定參數
EnumLocks: 返回伺服器上所有的加鎖進程
KillProcess: 強行中斷一個進程

 

在VFP裡來操作和管理SQL SERVER (2) (介紹SQL-DMO)
用DMO來建立任務，並讓它定時運行. 執行環境必須是NT或者WINDOWS 2000， 因為需要運行SQL AGENT

***建立任務(JOB)
oJob=CreateObject("SQLDMO.Job") &&建立任務對象
ojob.Name="Pubs_Daily_Backup" &&任務名稱
oServer.JobServer.Jobs.Add(oJob) &&加到SQL SERVER的任務裡
oJob.BeginAlter &&開始定義任務
oJobStep=CreateObject("SQLDMO.JobStep") &&建立任務步驟對象
oJobStep.Name = "Step_1" &&步驟名稱

oJobStep.StepID = 1 &&步驟號碼
oJobStep.DatabaseName="Pubs" &&資料庫名稱
oJobStep.SubSystem= "TSQL" &&任務類型，可以是TSQL或者作業系統命令(CmdExec), 或者ActiveScripting

cFName="PubBack"+Dtoc(Date(),1)+".dat" &&備份目標檔案名
oJobStep.Command ="Backup database Pubs to Disk='D:/Temp/"+cFname+"' With Password='7788'" &&執行備份的TSQL命令
oJobStep.OnFailAction = 2  && 如果任務失敗退出。也可以設成發送EMAIL或者用NT裡的NETSEND發資訊
oJobStep.OnSuccessAction= 1  && 如果任務成功，退出。
oJob.JobSteps.Add(oJobStep) &&新增工作步驟
oJob.StartStepID = 1 &&從第一步開始執行，對多步驟的任務起作用
oJob.DoAlter &&儲存修改

***把任務加到SCHEDULER裡

oJobSchedule =CreateObject("SQLDMO.JobSchedule") &&建立Schedule對象
oJob = oServer.JobServer.Jobs("Pubs_Daily_Backup")&&建立任務對象
oJobSchedule.Name = "Daily_Execution" &&名稱
oJobSchedule.Schedule.FrequencyType= 4  && 運行頻率， 4是每日運行
oJobSchedule.Schedule.FrequencyInterval = 1 &&運行間隔， 1是每天
oJobSchedule.Schedule.ActiveStartDate =Dtoc(Date(),1) &&開始日期(今天)， 必須是yyyymmdd格式
oJobSchedule.Schedule.ActiveStartTimeOfDay = "233000" &&開始時間(晚上11點30)， 必須是 hhmmss格式
**下面2句設定是使任務永不到期
oJobSchedule.Schedule.ActiveEndDate=99991231  &&no end date
oJobSchedule.Schedule.ActiveEndTimeOfDay =235959 &&No end time

新增工作到SCHEDULE裡
oJob.BeginAlter
oJob.JobSchedules.Add(oJobSchedule)
oJob.DoAlter

上述命令我在VFP7裡都測試成功，在SQL SERVER裡也看到了新添加的命令和回合設定，但因為我是用的WINDOWS ME，沒法測試工作是否真的運行了。哪位有環境，可以幫我試試

下面這個文章中的預存程序比較麻煩，不過調用起來比較簡單。

------------------------------------------------

SQL SERVER2000Database Backup和恢複預存程序

 

我自己寫的2個過程和一個函數,用於SQL SERVER2000Database Backup和恢複
拿出來和大家交流一下,過程和函數的詳細說明在代碼中
謝謝

 

/*備份資料庫的過程*/
if exists(
select * from sysobjects
  where name='pr_backup_db' and xtype='p'
          )
begin
drop proc pr_backup_db
end
go

 

create proc pr_backup_db
@flag varchar(20) out,
@backup_db_name varchar(128),
@filename varchar(1000)  --路徑＋檔案名稱字
as
declare @sql nvarchar(4000),@par nvarchar(1000)
if not exists(
select * from master..sysdatabases
  where name=@backup_db_name
  )
begin
select @flag='db not exist'  /*資料庫不存在*/
return
end
else
begin
if right(@filename,1)<>'/' and charindex('/',@filename)<>0
begin
  select @par='@filename varchar(1000)'
  select @sql='BACKUP DATABASE '+@backup_db_name+' to disk=@filename with init'
  execute sp_executesql @sql,@par,@filename
  select @flag='ok'
  return
end
else
begin
  select @flag='file type error'  /*參數@filename輸入格式錯誤*/
  return
end
end

 

GO

 

說明:pr_backup_db過程是備份你的資料庫

 

 

/*建立函數,得到檔案得路徑*/
if exists(
select * from sysobjects
  where name='fn_GetFilePath' and xtype='fn'
        )
begin
drop function fn_GetFilePath
end
go

 

create function fn_GetFilePath(@filename nvarchar(260))
returns nvarchar(260)  
as
begin
declare @file_path nvarchar(260)
declare @filename_reverse nvarchar(260)
select @filename_reverse=reverse(@filename)
select @file_path=substring(@filename,1,len(@filename)+1-charindex('/',@filename_reverse))
return @file_path
end

 

GO

 

/*恢複資料庫的過程*/
if exists(
select * from sysobjects
  where name='pr_restore_db' and xtype='p'
          )
begin
drop proc pr_restore_db
end
go

 

CREATE  proc pr_restore_db   
@flag varchar(20) out,    /*過程啟動並執行狀態標誌,是輸入參數*/     
@restore_db_name nvarchar(128),  /*要恢複的資料名字*/
@filename nvarchar(260)         /*備份檔案存放的路徑+備份檔案名字*/
as
declare @proc_result tinyint  /*返回系統預存程序xp_cmdshell運行結果*/
declare @loop_time smallint  /*迴圈次數*/
declare @max_ids smallint    /*@tem表的ids列最大數*/
declare @file_bak_path nvarchar(260)  /*原資料庫存放路徑*/
declare @flag_file bit   /*檔案存放標誌*/
declare @master_path nvarchar(260)  /*資料庫master檔案路徑*/
declare @sql nvarchar(4000),@par nvarchar(1000)
declare @sql_sub nvarchar(4000)
declare @sql_cmd nvarchar(100)
declare @sql_kill nvarchar(100)
/*
判斷參數@filename檔案格式合法性，以防止使用者輸入類似d: 或者 c:/a/ 等非法檔案名稱
參數@filename裡面必須有'/'並且不以'/'結尾
*/
if right(@filename,1)<>'/' and charindex('/',@filename)<>0
begin
select @sql_cmd='dir '+@filename
EXEC @proc_result = master..xp_cmdshell @sql_cmd,no_output
IF (@proc_result<>0)  /*系統預存程序xp_cmdshell傳回碼值:0(成功）或1（失敗）*/
begin
  select @flag='not exist'   /*備份檔案不存在*/
  return  /*退出過程*/
end
/*建立暫存資料表,儲存由備份組內自主資料庫和記錄檔列表組成的結果集*/
create table #tem(
     LogicalName nvarchar(128), /*檔案的邏輯名稱*/
     PhysicalName nvarchar(260) , /*檔案的實體名稱或作業系統名稱*/
     Type char(1),  /*資料檔案 (D) 或記錄檔 (L)*/
     FileGroupName nvarchar(128), /*包含檔案的檔案組名稱*/
     [Size] numeric(20,0),  /*當前大小（以位元組為單位）*/
     [MaxSize] numeric(20,0)  /*允許的最大大小（以位元組為單位）*/
   )
/*
建立表變數，表結構與暫存資料表基本一樣
就是多了兩列,
列ids（自增編號列）,
列file_path,存放檔案的路徑
*/
declare @tem table(      
     ids smallint identity,  /*自增編號列*/
     LogicalName nvarchar(128),
     PhysicalName nvarchar(260),
     File_path nvarchar(260),
     Type char(1), 
     FileGroupName nvarchar(128)
   )
insert into #tem
  execute('restore filelistonly from disk='''+@filename+'''')
/*將暫存資料表匯入表變數中,並且計算出相應得路徑*/
insert into @tem(LogicalName,PhysicalName,File_path,Type,FileGroupName) 
  select LogicalName,PhysicalName,dbo.fn_GetFilePath(PhysicalName),Type,FileGroupName
   from #tem
if @@rowcount>0
begin
  drop table #tem
end
select @loop_time=1
select @max_ids=max(ids)  /*@tem表的ids列最大數*/
  from @tem
while @loop_time<=@max_ids
begin
  select @file_bak_path=file_path
   from @tem where ids=@loop_time
  select @sql_cmd='dir '+@file_bak_path
  EXEC @proc_result = master..xp_cmdshell @sql_cmd,no_output
  /*系統預存程序xp_cmdshell傳回碼值:0(成功）或1（失敗）*/
  IF (@proc_result<>0)
   select @loop_time=@loop_time+1 
  else
   BREAK /*沒有找到備份前資料檔案原有存放路徑，退出迴圈*/
end
select @master_path=''
if @loop_time>@max_ids
  select @flag_file=1   /*備份前資料檔案原有存放路徑存在*/
else
begin
  select @flag_file=0  /*備份前資料檔案原有存放路徑不存在*/
  select @master_path=dbo.fn_GetFilePath(filename)
   from master..sysdatabases
   where name='master'
end
select @sql_sub=''
/*type='d'是資料檔案,type='l'是記錄檔 */
/*@flag_file=1時新的資料庫檔案還是存放在原來路徑，否則存放路徑和master資料庫路徑一樣*/
select @sql_sub=@sql_sub+'move '''+LogicalName+''' to '''
   +case type
         when 'd' then case @flag_file
             when 1 then  File_path
      else @master_path
          end   
         when 'l' then case  @flag_file
      when 1 then  File_path
      else @master_path
          end   
   end
   +case type
    when 'd' then @restore_db_name
           +'_DATA'
           +convert(sysname,ids)  /*給檔案編號*/
           +'.'
           +right(PhysicalName,3)  /*給檔案加入尾碼名,mdf or ndf*/
           +''',' 
    when 'l' then @restore_db_name
           +'_LOG'
           +convert(sysname,ids)   /*給檔案編號*/
           +'.'
           +right(PhysicalName,3)  /*給檔案加入尾碼名,mdf or ndf*/
           +''',' 
    end
   from @tem
select @sql='RESTORE DATABASE @db_name FROM DISK=@filename with '
select @sql=@sql+@sql_sub+'replace'
select @par='@db_name nvarchar(128),@filename nvarchar(260)'
/*關閉相關進程，把相應進程狀況匯入暫存資料表中*/
select identity(int,1,1) ids, spid
  into #temp
  from master..sysprocesses
  where dbid=db_id(@restore_db_name)
if @@rowcount>0 --找到相應進程
begin  
  select @max_ids=max(ids)
   from #temp
  select @loop_time=1
  while @loop_time<=@max_ids
  begin
   select @sql_kill='kill '+convert(nvarchar(20),spid)
    from #temp
    where ids=@loop_time
   execute sp_executesql @sql_kill
   select @loop_time=@loop_time+1
  end
end
drop table #temp
execute sp_executesql @sql,@par,@db_name=@restore_db_name,@filename=@filename
select @flag='ok'   /*操作成功*/
end
else
begin
SELECT @flag='file type error'  /*參數@filename輸入格式錯誤*/
end

 

GO

 

 

 

 

--run

 

--備份資料庫test_database
declare @fl varchar(10)
execute pr_backup_db @fl out,'test_database','c:/test_database.bak'
select @fl

 

--恢複資料庫,輸入的參數錯誤
declare @fl varchar(20)
exec pr_restore_db @fl out,'sa','c:/'
select @fl

 

--恢複資料庫,即建立資料庫test_database的複本test_db
declare @fl varchar(20)
exec pr_restore_db @fl out,'test_db','c:/test_database.bak'
select @fl

 

以上過程和函數在MS SQL2000運行成功,由於MS SQL7不支援使用者自訂函數和表變數,要在MS SQL7下使用可以把函數fn_GetFilePath改寫成過

 

程,把過程pr_restore_db中的表變數改寫為暫存資料表即可運行,有興趣的朋友可以試試!

1.資料備份:
Close All
Clear
mypath_old=Sys(5)+Sys(2003)
mypath_new=Alltrim(Alltrim(mypath_old)+'/MYDBCBF')
If !Directory("&MYPATH_new.")
Md &mypath_new.
Endif
Set Default To &mypath_new.
mydbf_old=Getfile('bak','','',1,'請輸入或選定備份檔案名')
If Len(mydbf_old)=0
=Messagebox('你剛才沒有錄入任何檔案名稱，備份沒有成功!!',32,'提示資訊')
Set Default To &mypath_old.
Else
mydbf_path=Left(mydbf_old,At('/',mydbf_old,Occurs('/',mydbf_old))-1)
If !Directory("&MYDBF_path.")
Md &mydbf_path.
ENDIF
ERASE &MYDBF_old.
handle=SQLExec(con,"backup database mydbc to disk=?MYDBF_old")
Set Default To &mypath_old.
If handle>0
=Messagebox('(^_^)資料成功備份到【 '+mydbf_path+' 】檔案夾下!  ',64,'Database Backup')
Else
=Messagebox(Message(),16,'Database Backup')
Endif
Endif
二.恢複:
Close All
Clear
mypath_old=Sys(5)+Sys(2003)
mypath_new=Alltrim(Alltrim(mypath_old)+'/MYDBCBF')
Set Default To &mypath_new.
mydbf=Getfile('bak','','',1,'請選定要恢複的檔案名稱')
Restore From mypath_old+'/myurl/url' Additive
sql_server=Alltrim(my_server)
sql_path=Alltrim(Right(sql_server,Len(sql_server)-Atc('/',sql_server)))
If Len(sql_path)=0
sql_restore_mdf='C:/Program Files/Microsoft SQL Server/MSSQL/Data/mydbc.MDF'
sql_restore_ldf='C:/Program Files/Microsoft SQL Server/MSSQL/Data/mydbc_log.ldf'
Else
sql_restore_mdf='C:/Program Files/Microsoft SQL Server/MSSQL$'+sql_path+'/Data/mydbc.MDF'
sql_restore_ldf='C:/Program Files/Microsoft SQL Server/MSSQL$'+sql_path+'/Data/mydbc_log.ldf'
Endif
*now_timer=1
Try
=SQLDisconnect(con)
Catch
Finally
Endtry
=SQLSetprop(0,"displogin",3)  &&永不顯示信任連接提示
myurl="driver=sql server;server="+Alltrim(my_server)+;
";uid=sa;pwd="+Alltrim(my_pwd)+;
";database=MASTER"
con_master=Sqlstringconnect(myurl)
If con_master<0
=Messagebox('<@_@>本機已與伺服器失去串連,您將退出本系統!',16,'系統資訊')
Else
handle=SQLExec(con_master,"RESTORE DATABASE mydbc FROM DISK=?MYDBF With Replace;
    ,Move 'MYDBC_DATA' To ?SQL_RESTORE_mdf';
,Move 'MYDBC_LOG' To ?SQL_RESTORE_LDF'")
If handle>0
=MESSAGEBOX('^_^資料恢複成功!',32,'資料恢複')
Try
=SQLDisconnect(con_master)
Catch
Finally
Endtry
=SQLSetprop(0,"displogin",3)  &&永不顯示信任連接提示
myurl="driver=sql server;server="+Alltrim(my_server)+;
";uid=sa;pwd="+Alltrim(my_pwd)+;
";database=mydbc"
con=Sqlstringconnect(myurl)
If con<0
=Messagebox('<@_@>本機已與伺服器失去串連,您將退出本系統!',16,'系統資訊')
Else
now_timer=0
Endif
Else
=Messagebox(Message(),16,'資料恢複')
Endif
Endif
Set Default To &mypath_old.

資料備份不用多作解釋.對於資料恢複:
1.要注意在選定恢複檔案時可能會改變當前路徑.要在恢複資料後還原當前路徑.
2.測試與資料來源的串連.因為SQL2000的資料庫MASTER等是內建的,只要測試能否串連上其中一個就行.
  因為在恢複時可能找不到你要恢複的自訂資料庫,所以要測試SQL2000的資料表.
3.SQL2000的安裝可能有自訂執行個體這個也要考慮.比方說你的服務執行個體名是SERVER那SQL2000產生的檔案夾是:MSSQL$SERVER而不是MSSQL
4.在恢複資料時要斷開與當前資料庫的串連(與上面所述,用SQL2000的內建資料庫作為當前串連),否則會產生異常.
5.資料恢複成功後,要恢複與自訂資料庫的串連.