sql server 类型自动判断和条件检查的备份脚本

print '                                                                        '+char(13)+'备份运行中 $$$$$$$$$$$$$$$$$$$$$$$$$$$$$'

print '------------------------------- ---------------------------------------- '

delete from  @DirTree

end

--开始完全备份

if @backuptype='full'

begin

print '............................................................................ .'

print '开始完全备份.....请稍等'

print '............................................................................ .'

--隐藏检查目录

set @backupPath3=@backupPath2+''+'full'

INSERT INTO @DirTree(subdirectory, depth)

EXEC master.sys.xp_dirtree @backupPath3

IF NOT EXISTS (SELECT 1 FROM @DirTree WHERE subdirectory = @backupPath3)

EXEC master.dbo.xp_create_subdir @backupPath3

delete from @DirTree

set @FullPath = @backuppath3+''+@dbname+'_'+@backuptype+'_'+replace (replace(replace(convert(varchar,getdate(),20),'-',''),' ',''),':','')+ '.bak'

backup database @dbname to disk=@FullPath

WITH buffercount = 20, maxtransfersize = 2097152 ,

COMPRESSION,RETAINDAYS=15,NOFORMAT,NOINIT,

NAME=N'完整备份 ',SKIP,NOREWIND,

NOUNLOAD,STATS=10

set @backtype='D'

set @backupdesc='完全备份'

set @backupfilename=@FullPath

insert into msdb.dbo.backuphistory

(dbname,backtype,lastbackup,backupdesc,backupfilename)

values(@dbname, @backtype,GETDATE(), @backupdesc,@backupfilename)

   SET @Error = @@ERROR

   if @Error !=0

   begin

   SET @ErrorMessage = '数据库'+@dbname+'完全备份未顺利完成 ！: ' + CHAR(13) + CHAR(10)

   RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

   return

   end

print '                                                                    '

print '------------------------------------------------------- ---------------- '

print @dbname+'完全备份 '+@FullPath+' 已经完 成!'

print '---------------------------------------------------------- ------------- '

return

end

--开始差异备份

else if @backuptype='diff'

begin

print '                                                                             '

print '............................................................................ .'

   print '开始差异备份.....请稍等'

   print '............................................................................ .'

   --检查是否有完全备份并存在

insert into @tmp                        

select top 1  a.backupfilename,

MAX(a.lastbackup)  as backuptime from msdb.dbo.backuphistory a

where a.dbname=@dbname and a.backtype='D'

group by backupfilename

order by a.backupfilename desc

if not exists (select top 1 1 from @tmp )

   begin

   SET @ErrorMessage = '数据库'+@dbname+'没有完全备份历史记录!! ' + CHAR(13) + CHAR(10)

   RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

   return

   end

else

begin  

    select @fullbafile=backupfilename from @tmp

    exec xp_fileexist @fullbafile, @result output

 if (@result=0 )

 begin

 SET @ErrorMessage = '数据库'+@dbname+'完全备份文 件不存在!做差异备份无意义!' + CHAR(13) + CHAR(10)

 RAISERROR (@ErrorMessage,16,1) WITH NOWAIT

 return

 end

    end

   --隐藏检查目录

set @backupPath3=@backupPath2+''+'diff'

INSERT INTO @DirTree(subdirectory, depth)

EXEC master.sys.xp_dirtree @backupPath3

IF NOT EXISTS (SELECT 1 FROM @DirTree WHERE subdirectory = @backupPath3)

EXEC master.dbo.xp_create_subdir @backupPath3

delete from @DirTree

set @FullPath = @backuppath3+''+@dbname+'_'+@backuptype+'_'+replace (replace(replace(convert(varchar,getdate(),20),'-',''),' ',''),':','')+ '.diff'

backup database @dbname to disk=@FullPath

WITH buffercount = 30, maxtransfersize = 2097152 ,

COMPRESSION, DIFFERENTIAL,RETAINDAYS=8,NOFORMAT,NOINIT,

NAME=N'差异备份 ',SKIP,NOREWIND,

NOUNLOAD,STATS=10

set @backtype='I'

set @backupdesc='差异备份'

set @backupfilename=@FullPath

insert into msdb.dbo.backuphistory

(dbname,backtype,lastbackup,backupdesc,backupfilename)

values(@dbname, @backtype,GETDATE(), @backupdesc,@backupfilename)

   SET @Error = @@ERROR

   if @Error !=0

   begin

   SET @ErrorMessage = '数据库'+@dbname+'差异备份未顺利完成！: ' + CHAR (13) + CHAR(10)

   RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

   return

   end

（编辑：常州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!