MS SQL数据库备份和恢复存储过程(加强版本)

相关文章导航
  1. Sql Server2005 Transact-SQL 新兵器学习总结之-总结
  2. Flex,Fms3相关文章索引
  3. FlexAir开源版-全球免费多人视频聊天室,免费网络远程多人视频会议系统((Flex,Fms3联合开发))<视频聊天,会议开发实例8>

 

上次写了MS SQL数据库备份和恢复存储过程
详细情况见(http://www.cnblogs.com/aierong/archive/2004/05/10/8789.aspx)
自感觉功能不太齐全,就重新写了一加强版本,经过测试成功!
先将代码发布出来,大家共享。
如有发现BUG,请大家指教或EMAIL:aierong@vip.sina.com

/*备份数据库*/
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

 

/*创建函数,得到文件得路径*/
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

 




/*恢复数据库*/
CREATE  proc pr_restore_db    
/*
------------------------------------------------
Create Time:    2004-03-20 
Update Time:    2004-03-29 11:05
Author:         aierong
Remark:         恢复数据库
  
------------------------------------------------
*/

/*过程运行的状态标志,是输入参数*/      
@flag 
varchar(20) out,    
/*要恢复的数据名字*/
@restore_db_name 
nvarchar(128), 
/*备份文件存放的路径+备份文件名字*/ 
@filename 
nvarchar(260)         
as
/*返回系统存储过程xp_cmdshell运行结果*/
declare @proc_result tinyint 
/*循环次数*/ 
declare @loop_time smallint  
/*@tem表的ids列最大数*/
declare @max_ids smallint    
/*原数据库存放路径*/
declare @file_bak_path nvarchar(260)  
/*文件存放标志*/
declare @flag_file bit   
/*数据库master文件路径*/
declare @master_path nvarchar(260)  
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
 
/*系统存储过程xp_cmdshell返回代码值:0(成功)或1(失败)*/
 
IF (@proc_result<>0)  
 
begin
  
/*备份文件不存在*/
  
select @flag='not exist'   
  
/*退出过程*/
  
return  
 
end
 
/*创建临时表,保存由备份集内包含的数据库和日志文件列表组成的结果集*/
 
create table #tem(
     
/*文件的逻辑名称*/ 
     LogicalName 
nvarchar(128), 
     
/*文件的物理名称或操作系统名称*/
     PhysicalName 
nvarchar(260) , 
            
/*数据文件 (D) 或日志文件 (L)*/
     Type 
char(1),  
     
/*包含文件的文件组名称*/
     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
 
/*@tem表的ids列最大数*/
 
select @max_ids=max(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)  
           
+'.'
           
/*给文件加入后缀名,mdf or ndf*/ 
           
+right(PhysicalName,3)  
           
+''','  
    
when 'l' then @restore_db_name
           
+'_LOG'
           
/*给文件编号*/ 
           
+convert(sysname,ids)   
           
+'.'
           
/*给文件加入后缀名,mdf or ndf*/ 
           
+right(PhysicalName,3)  
           
+''','  
    
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
 
/*参数@filename输入格式错误*/
 
SELECT @flag='file type error'  
end


GO



/*运行*/

--备份数据库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

 

 

收藏与分享

收藏到QQ书签 添加到百度搜藏 添加到百度搜藏 添加到雅虎收藏 分享到饭否 收藏到就喜欢网络收藏夹

RSS订阅我 什么是RSS?

feedsky    http://wap.feedsky.com/aierongrss    E-mail
订阅到雅蛙       使用RSS邮天下订阅    订阅到有道阅读
订阅到抓虾    鲜果阅读器订阅图标    Add to Google
訂閱 Bloglines    哪吒提醒    Subscribe in NewsGator Online

东莞.net俱乐部

东莞.net俱乐部 欢迎您的加入

posted @ 2004-05-20 13:55  aierong  阅读(8434)  评论(23编辑  收藏  举报