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

 BEGIN

   SET @ErrorMessage = '服务器上不存在指定的盘符:'+upper(substring (@CheckDirectory,1,1)+ CHAR(13) + CHAR(10))

   RAISERROR (@ErrorMessage,16,1) WITH NOWAIT

   SET @Error = @@ERROR

   return

END

--判断是否输入备份数据库名

IF @dbname IS NULL OR @dbname = ''

 BEGIN

   SET @ErrorMessage = '未输 入任何备份数据库名.' + CHAR(13) + CHAR(10)+'备份进程已终止!'

   RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

   SET @Error = @@ERROR

   return

 END

else if (@dbname='tempdb' or @dbname='TEMPDB')

begin

SET @ErrorMessage = '临时数据库不需要备份.' + CHAR(13) + CHAR(10)+'备份进 程已终止!'

   RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

   SET @Error = @@ERROR

   return

end

else if (@dbname  in ( select name from sys.databases where state_desc='OFFLINE' or state_desc='offline'))

begin

SET @ErrorMessage = '脱机的数据库'+@dbname+'不需要备份.' + CHAR(13) + CHAR(10)+'备份进程已终止!'

   RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

   SET @Error = @@ERROR

   return

end

--判断输入类型

if  @backuptype not in ('full','diff','log')

begin

print '#########################严重警告###############严重警告 #################################'

print '不支持类型 '+@backuptype+'!   只能输入(full:完全备份; diff:差异备份; log:日志备 份)                        '

print '有问题请联系ocpyang!'

print '#########################严重警告###############严重警告 ###################################'

return

end

--判断目录是否存在

SET @backupPath2=@backupPath+''+@dbname

INSERT INTO @DirTree(subdirectory, depth)

EXEC master.sys.xp_dirtree @backupPath

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

begin

print '系统将新建目录：'+@backupPath2+' ............'

  EXEC master.dbo.xp_create_subdir @backupPath2

  print '目 录：'+@backupPath2+'新建成功!'

  print '         '

  delete from  @DirTree

end

else

begin

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

print '目录：'+@backupPath2+'已经存在！'

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 '                                                                             '

（编辑：应用网_镇江站长网）

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