数据库备份及还原

上一篇 / 下一篇  2010-04-30 11:29:25 / 个人分类:数据库

一、数据库备份

第一种方法:利用SQL Server的—数据库维护计划,创建好之后在"作业"列表中,只要SQL代理启动着,它就会按照你的设置定时备份数据库,当然如果你有时在数据库变动很大时想先备份,则可以直接启动作业就达到备份的效果了,这个方法最简单

企业管理器  
  --管理  
  --数据库维护计划  
  --右键  
  --新建维护计划  
  --下一步  
  --选“全部用户数据库”  
  --下一步  
  --下一步  
  --勾上“作为维护计划的一部分来备份数据库”,选择磁盘来存放备份文件,点调度的“更改”来设置每天24:00执行  
  --下一步  
  --指定存放备份文件的系统目录--这里还可以设置文件保存的时间  
  --下一步  
  --下一步  
  --下一步  
  --完成  

sql server2005中建议使用SQL server 维护计划向导,用户尽可能选择Windows 身份验证(比如GISTAR\Administrator)

第二种方法:自己创建存储过程,创建作业,设置相应参数

--以下资源从网上获取的

(1)新建存储过程

--我的第一种处理中用到的备份处理的存储过程  
   
  if   exists   (select   *   from   dbo.sysobjects   where   id   =   object_id(N'[dbo].[p_backupdb]')   and   OBJECTPROPERTY(id,   N'IsProcedure')   =   1)  
  drop   procedure   [dbo].[p_backupdb]  
  GO  
   
  /*--备份所有数据库  
   
  备份的文件名为数据库名+.bak  
  将所有的用户数据库(或指定的数据库列表)  
  备分到指定的目录下.  
   
  --邹建   2003.10(引用请保留此信息)--*/  
   
  /*--调用示例  
   
  --备份所有用户数据库  
  exec   p_backupdb   @bkpath='c:\',@dbname=''  
   
  --备份指定数据库  
  exec   p_backupdb   @bkpath='c:\',@dbname='客户资料,xzkh_new'  
  --*/  
   
  create   proc   p_backupdb  
  @bkpath   nvarchar(260)='', --备份文件的存放目录,不指定则使用SQL默认的备份目录  
  @dbname   nvarchar(4000)='' --要备份的数据库名称列表,不指定则备份所有用户数据库  
  as  
  declare   @sql   varchar(8000)  
   
  --检查参数  
  if   isnull(@bkpath,'')=''  
  begin  
  select   @bkpath=rtrim(reverse(filename))   from   master..sysfiles   where   name='master'  
  select   @bkpath=substring(@bkpath,charindex('\',@bkpath)+1,4000)  
  ,@bkpath=reverse(substring(@bkpath,charindex('\',@bkpath),4000))+'BACKUP\'  
  end  
  else   if   right(@bkpath,1)<>'\'   set   @bkpath=@bkpath+'\'  
   
  --得到要备份的数据库列表  
  if   isnull(@dbname,'')=''  
  declare   tb   cursor   local   for  
  select   name   from   master..sysdatabases   where   name   not   in('master','tempdb','model','msdb')  
  else  
  declare   tb   cursor   local   for  
  select   name   from   master..sysdatabases  
  where   name   not   in('master','tempdb','model','msdb')  
  and(@dbname   like   '%,'+name+',%'   or   @dbname   like   name+',%'   or   @dbname   like   '%,'+name)  
   
  --备份处理  
  open   tb  
  fetch   next   from   tb   into   @dbname  
  while   @@fetch_status=0  
  begin  
  set   @sql='backup   database  '+@dbname  
  +'   to  disk='''+@bkpath+@dbname  
  +rtrim(convert(char,getdate(),112))+'.bak''   with   format'  
  exec(@sql)  
  fetch   next   from   tb   into   @dbname  
  end  
  close   tb  
  deallocate   tb  
  go   
(2)  SQL   Server中创建作业的方法  
   
  企业管理器  
  --管理  
  --SQL   Server代理  
  --右键作业  
  --新建作业  
  --"常规"项中输入作业名称  
  --"步骤"项  
  --新建  
  --"步骤名"中输入步骤名  
  --"类型"中选择"Transact-SQL   脚本(TSQL)"   --这里执行上面那个备份的存储过程  
  --"数据库"选择执行命令的数据库  
  --"命令"中输入要执行的语句  
  --确定  
  --"调度"项  
  --新建调度  
  --"名称"中输入调度名称  
  --"调度类型"中选择你的作业执行安排  
  --如果选择"反复出现"  
  --点"更改"来设置你的时间安排  

二。数据库还原

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_RestoreDb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_RestoreDb]
GO

create proc p_RestoreDb
@bkpath nvarchar(1000)='',        --定义备份文件的存放目录,默认为SQL的备份目录
@bkfile nvarchar(4000)='',        --定义要恢复的备份文件名,不含扩展名
@dbpath nvarchar(260)='',         --恢复后的数据库存放目录,不指定则为SQL的默认数据目录
@overexist bit=1,                 --是否覆盖已经存在的数据库,仅@retype为'DB'/'DBNOR'是有效
@killuser bit=1                   --是否关闭用户使用进程,仅@overexist=1时有效
as
declare @sql varchar(8000),@dbname sysname

if isnull(@bkpath,'')=''
begin
    select @bkpath=rtrim(reverse(filename)) from master..sysfiles where name='master'
    select @bkpath=substring(@bkpath,charindex('\',@bkpath)+1,4000)
        ,@bkpath=reverse(substring(@bkpath,charindex('\',@bkpath),4000))+'BACKUP\'
end
else if right(@bkpath,1)<>'\' set @bkpath=@bkpath+'\'

--得到恢复后的数据库存放目录
if isnull(@dbpath,'')=''
begin
    select @dbpath=rtrim(reverse(filename)) from master..sysfiles where name='master'
    select @dbpath=reverse(substring(@dbpath,charindex('\',@dbpath),4000))
end
else if right(@dbpath,1)<>'\' set @dbpath=@dbpath+'\'

--得到指定目录下的所有备份文件
create table #t(fname varchar(260),depth int,isf bit)
insert into #t exec master..xp_dirtree @bkpath,1,1

if isnull(@bkfile,'')=''
    declare tb cursor local for select fn=left(fname,patindex('%.bak',fname)-1) from #t
        where isf=1 and fname like '%.bak'  --取.bak文件
else
begin
    set @bkfile=','+replace(@bkfile,',','.bak,')+'.bak,'
    declare tb cursor local for select fn=left(fname,patindex('%.bak',fname)-1) from #t
        where isf=1 and fname like '%.bak' and @bkfile like '%,'+fname+',%'
end

--恢复数据库处理
open tb
fetch next from tb into @dbname
while @@fetch_status=0
begin
    --生成数据库恢复语句
    set @sql='restore database ['+@dbname
        +'] fromdisk='''+@bkpath+@dbname+'.bak'''
        +' with RECOVERY'
        +case when @overexist=1 then ',replace' else '' end

    --添加移动逻辑文件的处理
    --从备份文件中获取逻辑文件名
    declare @lfn nvarchar(128),@tp char(1),@i int,@pn nvarchar(128)

    --创建临时表,保存获取的信息
    create table #tb(ln nvarchar(128),pn nvarchar(260),tp char(1),fgn nvarchar(128),sz numeric(20,0),Msz numeric(20,0))
    --从备份文件中获取信息
    insert into #tb exec('restore filelistonly fromdisk='''+@bkpath+@dbname+'.bak''')
    declare #f cursor local for select ln,tp,pn from #tb order by tp
    open #f
    fetch next from #f into @lfn,@tp,@pn
    set @i=0
    while @@fetch_status=0
    begin
        select @sql=@sql+',move'''+@lfn+'''to'''+@dbpath+@dbname+cast(@ias varchar)
            +right(@pn,4)+''''
            ,@i=@i+1
        fetch next from #f into @lfn,@tp,@pn
    end
    close #f
    deallocate #f
    drop table #tb

    --关闭用户进程处理
    if @overexist=1 and @killuser=1
    begin
        declare hCForEach cursor for


TAG:

 

评分:0

我来说两句

Open Toolbar