sql server 类型自动判断和条件检查的备份脚本
|
根据自己环境自定义了一个备份脚本！
说明: 使用方法:exec master.dbo.fullbackup1 's:backup','suzhou','full' 0.备份类型只能是full、diff或log，数据库名不能为空 1.通过新建一个历史表记 录每次备份内容 2.检查数据库版本是否为2005以上 3.检查当前用户是否有权 限完成备份 4.会自动检查指定盘符是否存在 5.检查指定格式是否为s: 6.如果指定的备份目录不存在proc将自动新建，根据备份类型为full、diff或 log 7.检查备份数据库名suzhou是否存在并联机 8.检查备份数据库名suzhou不 能是临时数据库 9.如果备份类型为差异备份则检查是否有完全备份存在并且备份的这 个文件存在于指定的目录下， 如果备份历史表有记录但是该备份文件不存在将终止备份 10.如果备份类型为日志备份，先检查数据库恢复模式是否为完整，否则将停止备 份; 将进一步检查备份历史表和备份文件，必须存在完全备份或差异备份,否则终止备份 USE [msdb] GO if OBJECT_ID('backuphistory')is not null drop table backuphistory go CREATE TABLE [dbo].[backuphistory]( [sid] [int] IDENTITY(1,1) NOT NULL primary key, [dbname] [sysname] NOT NULL, [backtype] [char](2) NOT NULL, [lastbackup] [datetime] NOT NULL, [backupdesc] [varchar](20) NOT NULL, [backupfilename] [nvarchar](max) NULL, ) GO use master go create PROCEDURE [dbo].[fullbackup1] ( @backupPath varchar(500), @dbname sysname, @backuptype varchar(100) ) with encryption as declare @currentuser sysname declare @role varchar(30) select @currentuser=system_user DECLARE @Version numeric(18,10) DECLARE @Error int declare @Directory nvarchar(100) DECLARE @CheckDirectory nvarchar(4000) DECLARE @DirectoryInfo TABLE (FileExists bit, FileIsADirectory bit, ParentDirectoryExists bit) DECLARE @ErrorMessage nvarchar(max) DECLARE @backupPath2 nvarchar(500) DECLARE @DirTree TABLE (subdirectory nvarchar(255), depth INT) DECLARE @FullPath varchar(1000) declare @backupPath3 nvarchar(500) declare @recovery_model_desc varchar(20) declare @backtype varchar(100) declare @backupdesc varchar(20) declare @backupfilename varchar(max) -----new DECLARE @tmp TABLE (backupfilename varchar(3000), backuptime datetime) declare @fullbafile varchar(3000) declare @result int declare @log_start int set nocount on --检查用户权限 select @role=srvrole from ( select SrvRole = g.name, MemberName = u.name from sys.server_principals u, sys.server_principals g, sys.server_role_members m where g.principal_id = m.role_principal_id and u.principal_id = m.member_principal_id and u.name=@currentuser ) c - -order by 1, 2 if @role !='sysadmin' or @role is null or @role='' begin RAISERROR('当前用户没有需要的权限完成备份! ',16,1) print '你可能是越权操作或其它!'+char(13)+'请联系 DBA!' SET @Error = @@ERROR return end --检查服务器 版本 SET @Error = 0 SET @Version = CAST(LEFT(CAST(SERVERPROPERTY ('ProductVersion') AS nvarchar(max)), CHARINDEX('.',CAST (SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - 1) + '.' + REPLACE(RIGHT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)), LEN(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar (max))) - CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))),'.','') AS numeric(18,10)) IF @Version < 9 BEGIN RAISERROR('该备份方案仅支持 SQL Server 2005, SQL Server 2008和SQL Server 2008 R2.',16,1) SET @Error = @@ERROR return END set @Directory=@backupPath --判断路径格式 IF NOT (@Directory LIKE '[a-z]:%' ) BEGIN SET @ErrorMessage = '输入的目录'+@Directory+'格式 '+ '不支持！.'+' 参考类型如: s:backup' + CHAR(13) + CHAR(10) RAISERROR(@ErrorMessage,16,1) WITH NOWAIT SET @Error = @@ERROR return END --判断输入的数据库名是 否存在 IF @dbname not in(select name from sys.databases) BEGIN SET @ErrorMessage = '数据库名: '+@dbname+' 不存在!.' + CHAR(13) + CHAR(10) RAISERROR(@ErrorMessage,16,1) WITH NOWAIT SET @Error = @@ERROR return END --判断输入的盘符是否存在和是否 新建目录 --检查指定盘符是否存在 SET @CheckDirectory = substring (@Directory,1,3) INSERT INTO @DirectoryInfo (FileExists, FileIsADirectory, ParentDirectoryExists) EXECUTE [master].dbo.xp_fileexist @CheckDirectory IF NOT EXISTS (SELECT * FROM @DirectoryInfo WHERE FileExists = 0 AND FileIsADirectory = 1 AND ParentDirectoryExists = 1) 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+'已经存在！' （编辑：常州站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
