SQLServer 2000 数据库备份还原存储过程

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

*/