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

代码

  1 /*备份数据库*/
  2 create proc pr_backup_db
  3 @flag varchar(20) out,
  4 @backup_db_name varchar(128),
  5 @filename varchar(1000)  --路径＋文件名字
  6 as
  7 declare @sql nvarchar(4000),@par nvarchar(1000)
  8 if not exists(
  9  select * from master..sysdatabases
 10   where name=@backup_db_name
 11   )
 12 begin
 13  select @flag='db not exist'  /**//*数据库不存在*/
 14  return
 15 end
 16 else
 17 begin
 18  if right(@filename,1)<>'' and charindex('',@filename)<>0
 19  begin
 20   select @par='@filename varchar(1000)'
 21   select @sql='BACKUP DATABASE '+@backup_db_name
 22                          +' to disk=@filename with init'
 23   execute sp_executesql @sql,@par,@filename
 24   select @flag='ok' 
 25   return
 26  end
 27  else
 28  begin
 29   select @flag='file type error'  /**//*参数@filename输入格式错误*/
 30   return
 31  end
 32 end
 33 
 34 GO
 35 
 36  
 37 /**//*创建函数,得到文件得路径*/
 38 create function fn_GetFilePath(@filename nvarchar(260))
 39 returns nvarchar(260)   
 40 as
 41 begin
 42  declare @file_path nvarchar(260)
 43  declare @filename_reverse nvarchar(260)
 44  select @filename_reverse=reverse(@filename)
 45  select @file_path=substring(@filename,1,len(@filename)+1-charindex('',@filename_reverse))
 46  return @file_path
 47 end
 48 
 49 
 50 GO
 51 
 52  
 53 
 54 
 55 
 56 /**//*恢复数据库*/
 57 CREATE  proc pr_restore_db    
 58 /**//*
 59 ------------------------------------------------
 60 Create Time:    2004-03-20 
 61 Update Time:    2004-03-29 11:05
 62 Author:         aierong
 63 Remark:         恢复数据库
 64   
 65 ------------------------------------------------
 66 */
 67 /**//*过程运行的状态标志,是输入参数*/      
 68 @flag varchar(20) out,    
 69 /**//*要恢复的数据名字*/
 70 @restore_db_name nvarchar(128), 
 71 /**//*备份文件存放的路径+备份文件名字*/ 
 72 @filename nvarchar(260)         
 73 as
 74 /**//*返回系统存储过程xp_cmdshell运行结果*/
 75 declare @proc_result tinyint 
 76 /**//*循环次数*/ 
 77 declare @loop_time smallint  
 78 /**//*@tem表的ids列最大数*/
 79 declare @max_ids smallint    
 80 /**//*原数据库存放路径*/
 81 declare @file_bak_path nvarchar(260)  
 82 /**//*文件存放标志*/
 83 declare @flag_file bit   
 84 /**//*数据库master文件路径*/
 85 declare @master_path nvarchar(260)  
 86 declare @sql nvarchar(4000),@par nvarchar(1000)
 87 declare @sql_sub nvarchar(4000)
 88 declare @sql_cmd nvarchar(100) 
 89 declare @sql_kill nvarchar(100) 
 90 /**//*
 91 判断参数@filename文件格式合法性，以防止用户输入类似d: 或者 c:a 等非法文件名
 92 参数@filename里面必须有''并且不以''结尾
 93 */
 94 if right(@filename,1)<>'' and charindex('',@filename)<>0
 95 begin 
 96  select @sql_cmd='dir '+@filename
 97  EXEC @proc_result = master..xp_cmdshell @sql_cmd,no_output
 98  /**//*系统存储过程xp_cmdshell返回代码值:0(成功）或1（失败）*/
 99  IF (@proc_result<>0)  
100  begin
101   /**//*备份文件不存在*/
102   select @flag='not exist'   
103   /**//*退出过程*/
104   return  
105  end
106  /**//*创建临时表,保存由备份集内包含的数据库和日志文件列表组成的结果集*/
107  create table #tem(
108      /**//*文件的逻辑名称*/ 
109      LogicalName nvarchar(128), 
110      /**//*文件的物理名称或操作系统名称*/
111      PhysicalName nvarchar(260) , 
112             /**//*数据文件 (D) 或日志文件 (L)*/
113      Type char(1),  
114      /**//*包含文件的文件组名称*/
115      FileGroupName nvarchar(128), 
116                           /**//*当前大小（以字节为单位）*/
117      [Size] numeric(20,0),  
118      /**//*允许的最大大小（以字节为单位）*/
119      [MaxSize] numeric(20,0)  
120    )
121  /**//*
122  创建表变量，表结构与临时表基本一样
123  就是多了两列,
124  列ids（自增编号列）,
125  列file_path,存放文件的路径
126  */
127  declare @tem table(       
128      /**//*自增编号列*/
129      ids smallint identity,  
130      LogicalName nvarchar(128), 
131      PhysicalName nvarchar(260), 
132      File_path nvarchar(260), 
133      Type char(1),  
134      FileGroupName nvarchar(128)
135    )
136  insert into #tem 
137   execute('restore filelistonly from disk='''+@filename+'''')
138  /**//*将临时表导入表变量中,并且计算出相应得路径*/
139  insert into @tem(LogicalName,PhysicalName,File_path,Type,FileGroupName)  
140   select LogicalName,PhysicalName,dbo.fn_GetFilePath(PhysicalName),Type,FileGroupName 
141    from #tem
142  if @@rowcount>0 
143  begin
144 

转载于:https://www.cnblogs.com/wxinmylife/archive/2010/05/06/1728628.html