关闭

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

1444人阅读 评论(1) 收藏 举报

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 existsselect * 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

*/
   

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:77313次
    • 积分:1036
    • 等级:
    • 排名:千里之外
    • 原创:20篇
    • 转载:22篇
    • 译文:0篇
    • 评论:15条
    文章存档
    最新评论