SQLServer 2000 數據庫備份還原存儲過程

--這個存儲過程是在網上找到的，並非原創。

if exists(select * from sysobjects where type='p' and name='usp_backup')
 drop proc usp_backup
go
create proc usp_backup
 @flag int 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
  set @flag=0   /**//*數據庫不存在*/
  return
 end
 else
 begin
  if right(@filename,1)<>'' and charindex('',@filename)<>0
  begin
   set @par='@filename varchar(1000)'
   set @sql='BACKUP DATABASE '+@backup_db_name+' to disk=@filename with init'
   execute sp_executesql @sql,@par,@filename
   set @flag=1
   return
  end
  else
  begin
set @flag=0  /**//*參數@filename輸入格式錯誤*/
   return
  end
 end
go

if exists(select * from sysobjects where type='fn' and name='fn_GetFilePath')
 drop function fn_GetFilePath
go
create function fn_GetFilePath(@filename nvarchar(260))
 returns nvarchar(260) 
as
begin
 declare @file_path nvarchar(260),@filename_reverse nvarchar(260)
 set @filename_reverse=reverse(@filename)
 set @file_path=substring(@filename,1,len(@filename)+1-charindex('',@filename_reverse))
 return @file_path
end
go

if exists(select * from sysobjects where type='p' and name='usp_restore')
 drop proc usp_restore
go
CREATE  proc usp_restore
 @flag int out, /**//*過程運行的狀態標志,是輸入參數*/
 @restore_db_name nvarchar(128),  /**//*要恢復的數據名字*/
 @filename nvarchar(260) /**//*備份文件存放的路徑+備份文件名字*/
as
declare @proc_result tinyint, /**//*返回系統存儲過程xp_cmdshell運行結果*/
   @loop_time smallint, /**//*循環次數*/
   @max_ids smallint, /**//*@tem表的ids列最大數*/
   @file_bak_path nvarchar(260), /**//*原數據庫存放路徑*/
   @flag_file bit, /**//*文件存放標志*/
   @master_path nvarchar(260), /**//*數據庫master文件路徑*/
   @sql nvarchar(4000),
   @par nvarchar(1000),
   @sql_sub nvarchar(4000),
   @sql_cmd nvarchar(100),
   @sql_kill nvarchar(100)
/**//*判斷參數@filename文件格式合法性，以防止用戶輸入類似d: 或者 c:a 等非法文件名參數@filename裡面必須有''並且不以''結尾*/
 if right(@filename,1)<>'' and charindex('',@filename)<>0
 begin
  set @sql_cmd='dir '+@filename
  EXEC @proc_result = master..xp_cmdshell @sql_cmd,no_output
  IF (@proc_result<>0) /**//*系統存儲過程xp_cmdshell返回代碼值:0(成功）或1（失敗）*/
  begin
   set @flag=0   /**//*備份文件不存在*/
   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
  set @loop_time=1
  select @max_ids=max(ids) from @tem /**//*@tem表的ids列最大數*/
  while @loop_time<=@max_ids
  begin
   select @file_bak_path=file_path from @tem where ids=@loop_time
   set @sql_cmd='dir '+@file_bak_path
   EXEC @proc_result = master..xp_cmdshell @sql_cmd,no_output
   IF (@proc_result<>0) /**//*系統存儲過程xp_cmdshell返回代碼值:0(成功）或1（失敗）*/
    set @loop_time=@loop_time+1  
   else
BREAK /**//*沒有找到備份前數據文件原有存放路徑，退出循環*/
  end
  set @master_path=''
  if @loop_time>@max_ids 
   set @flag_file=1   /**//*備份前數據文件原有存放路徑存在*/
  else
  begin
   set @flag_file=0  /**//*備份前數據文件原有存放路徑不存在*/
   select @master_path=dbo.fn_GetFilePath(filename) from master..sysdatabases where name='master'
  end
  /**//*@flag_file=1時新的數據庫文件還是存放在原來路徑，否則存放路徑和master數據庫路徑一樣*/
  set @sql_sub=''
  select @sql_sub=@sql_sub+'move '''+LogicalName+''' to ''' 
  +case type  /**//*type='d'是數據文件,type='l'是日志文件 */
   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)  
    +''','  
  end
  from @tem
  set @sql='RESTORE DATABASE @db_name '+'FROM DISK=@filename with '
  set @sql=@sql+@sql_sub+'replace'
  set @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
   set @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
    set @loop_time=@loop_time+1 
   end
  end 
  drop table #temp
  execute sp_executesql @sql,@par,@db_name=@restore_db_name,@filename=@filename
  set @flag=1  /**//*操作成功*/
 end
 else
 begin
 set @flag=0  /**//*參數@filename輸入格式錯誤*/
 end
GO

 

再加上一個遠程備份的:

CREATE   procedure   usp_copy   
  as   
  begin   
      declare   @filename     nvarchar(200)   
      declare   @eloname     nvarchar(200)   
 declare   @data   datetime   
      declare   @lastday   varchar(100)   
      --建立映射   
      exec   master..xp_cmdshell 'net   use   z:   \192.168.1.2   "*"   /user:192.168.1.2administrator'   
      --設備異地文件夾路徑   
      set       @filename='Z:esysdb'+convert(char(8),getdate(),112)+   convert(varchar(2),getdate(),108)+'.bak'   
      print   'esys備份完成'   
      set       @eloname='Z:elogisdb'+convert(char(8),getdate(),112)+   convert(varchar(2),getdate(),108)+'.bak'   
        print   'elogisdb備份完成'     
      select   @filename   
      --備份開始   
      BACKUP       DATABASE       [sanshu_pf]       TO       DISK       =       @filename       WITH       NOINIT       ,       NOUNLOAD       ,       NAME       =       N'addin       備份',       NOSKIP       ,       STATS       =       10,       NOFORMAT       
      BACKUP       DATABASE       [sanshu_pf]       TO       DISK       =       @eloname       WITH       NOINIT       ,       NOUNLOAD       ,       NAME       =       N'addin       備份',       NOSKIP       ,       STATS       =       10,       NOFORMAT           
  --刪除前六天備份數據庫   
      set   @lastday   =   'del     z:*'+   convert(char(8),getdate()-6,112)+'*.*     /f'   
      exec   master..xp_cmdshell     @lastday   
        --刪除映射       
      exec   master..xp_cmdshell   'net   use   z:   /delete'   
end   
    
    
  GO   

下面是DOS命令NET USE的幫助:

/*

NET USE
[devicename | *] [\\computername\sharename[\volume] [passWord | *]]
        [/USER:[domainname\]username]
        [/USER:[dotted domain name\]username]
        [/USER:[username@dotted domain name]
        [/SMARTCARD]
        [/SAVECRED]
        [[/DELETE] | [/PERSISTENT:{YES | NO}]]

　　DeviceName:指派名稱以便連接到資源或指定斷開的設備。有兩種類型的設備名: 磁盤驅動器號(即 D: 到 Z:} 和打印機(即 LPT1:到 LPT3:}。如果鍵入星號而不是特定設備名，則系統會指派下一個可用的設備名。這個名稱以後可以作為訪問共享資源的名稱進行引用。

　　\\computername:指控制共享資源的計算機的名字。如果計算機名中包含有空字符，就要將雙反斜線 (\\) 和計算機名一起用引號 (" ")括起來。計算機名可以有1 到 15 個 字符。\volume :指定一個服務器上的NetWare卷。用戶必須安裝 Netware 的客戶服務 (Windows 工作站) 或者 Netware 的網關服務(Windows 服務器) 並使之與 NetWare 服務器相連。

　　PassWord:指定訪問共享資源所需的密碼。輸入星號 (*) 產生一個密碼提示在密碼提示行處鍵入密碼時不顯示密碼。

　　/user:在其後指定建立連接時使用的不同於目前登錄用戶的用戶名。

　　DomainName:指定不同於目前登錄域的其他域。如果省略則net use使用當前登錄的域。

　　注意，/user:後的登錄用戶和域可以有三種不同的表示形式，分別為domainname\username，dotted domain name\username和username@dotted domain name，其中dotted domain name提指域名的全稱，如Office.yesky.com，也即域名加域後綴的完全形式。

　　/SAVECRED:指定保留用戶名和密碼。除非命令提示輸入用戶名和密碼。否則此開關被忽略，

　　/SMARTCARD:指定連接使用在智能卡上的憑據。

　　/delete:取消指定的網絡連接。如果使用星號 (*) 指定連接，則所有網絡連接均將取消。

　　/persistent:{yes | no}:控制持久網絡連接的使用。默認值為最後一次使用的設置。非設備連接不會持久。Yes 將按其建立時的原樣保存所有連接，並在下次登錄時還原它們。No 則不保存已建立的連接或後續連接。現存的連接在下一次登錄時還原。使用 /delete 刪除持久連接。

　　Net use命令還有另兩種使用格式，分別如下:

　　NET USE {devicename | *} [passWord | *] /HOME

　　NET USE [/PERSISTENT:{YES | NO}]

　　其中第一種命令格式將用戶連到其域的主目錄並將主目錄映射為設備名DeviceName。後一種格式用來修改持久連接的使用。

　　下面是兩個例子:

　　要連接用戶標識符 Dan 就好像是通過 Accounts 域創建的連接:

　　net use d:\\server\share /user:Accounts\Dan

　　要斷開 \\Financial\Public 目錄:

　　net use f:\\financial\public /delete

*/