实战:sqlserver 2008 R2容灾方案:另一种思路-1

发布时间：2020-12-12 14:40:24 所属栏目：MsSql教程来源：网络整理

导读：自己的定义的容灾方案，可以根据自己的需要自己定义. 1.源数据库备份数据 ? 说明: ?使用方法:exec master.dbo.fullbackup1 's:backup','suzhou','full' ?0.备份类型只能是full、diff或log，数据库名不能为空 1.通过新建一个历史表记录每次备份内容 2.检查数

自己的定义的容灾方案，可以根据自己的需要自己定义.

1.源数据库备份数据

说明:
?使用方法: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.',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,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,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)+ CHAR(13) + CHAR(10))
???? RAISERROR(@ErrorMessage,1) WITH NOWAIT
???? SET @Error = @@ERROR
???? return
??END

--判断是否输入备份数据库名
?IF @dbname IS NULL OR @dbname = ''
?? BEGIN
???? SET @ErrorMessage = '未输入任何备份数据库名.' + CHAR(13) + CHAR(10)+'备份进程已终止!'
???? RAISERROR(@ErrorMessage,1) WITH NOWAIT
???? SET @Error = @@ERROR
???? return
?? END

else if (@dbname='tempdb' or @dbname='TEMPDB')
??begin
??SET @ErrorMessage = '临时数据库不需要备份.' + CHAR(13) + CHAR(10)+'备份进程已终止!'
???? RAISERROR(@ErrorMessage,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,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,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,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,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,'')+ '.diff'
??backup database @dbname to disk=@FullPath
??WITH buffercount = 30,
??COMPRESSION,DIFFERENTIAL,RETAINDAYS=8,
??NAME=N'差异备份',
??NOUNLOAD,STATS=10
??
??set @backtype='I'
??set @backupdesc='差异备份'
??set @backupfilename=@FullPath
??insert into msdb.dbo.backuphistory
??(dbname,backupfilename)
??values(@dbname,@backupfilename)
???? SET @Error = @@ERROR
???? if @Error !=0
???? begin
???? SET @ErrorMessage = '数据库'+@dbname+'差异备份未顺利完成！: ' + CHAR(13) + CHAR(10)
???? RAISERROR(@ErrorMessage,1) WITH NOWAIT
???? return
???? end
??
??
??print '??????????????????????????????????????????????????????????????????? '
??? print '----------------------------------------------------------------------- '
??? print @dbname+'差异备份 '+@FullPath+' 已经完成!'
?? print '----------------------------------------------------------------------- '
?? return
?end
??
??
?--开始日志备份
?else? if @backuptype='log'
?begin
??print '??????????????????????????????????????????????????????????????????????? '
???? print '检查环境.....请稍等..'
???? print '??????????????????????????????????????????????????????????????????????? '
???? --检查数据库恢复模式
??select @recovery_model_desc=recovery_model_desc from sys.databases
??where name=@dbname
??if @recovery_model_desc not in ('full')
??begin
??print '########错误信息######################################################'
??print '???????????????????????????????????????????????????????????????????????? '+char(13)+'请检查数据库'+@dbname+'的恢复模式!'+char(13)+ '使用命令ALTER DATABASE'+@dbname+' SET RECOVERY FULL WITH NO_WAIT修改!'
??print '???????????????????????????????????????????????????????????????????????? '
??print '########错误信息######################################################'
??print '???????????????????????????????????????????????????????????????????????? '+char(13)+'日志备份已终止!'
??return
??end
???? print '.........................................................................'
??print '???????????????????????????????????????????????????????????????????????? '
???? print '开始日志备份.....请稍等'
???? print '???????????????????????????????????????????????????????????????????????? '
???? print '.........................................................................'
????
???? --检查是否有完全备份或差异备份
??? -------------------------------------------------------------------------
????
???????? --检查是否有完全备份并存在
????

set? @log_start=0??
?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? @log_start=1
???? end
?else
??begin?
???? select @fullbafile=backupfilename from @tmp
???? exec xp_fileexist @fullbafile,@result output
???if (@result=0 )
???begin
??? set? @log_start=2
???end

? end
??
?? --检查是否有差异备份并存在
?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='I'
?group by backupfilename
?order by a.backupfilename desc
?if not exists (select top 1 1 from @tmp)
???? begin
??set? @log_start=3
???? end
?else
??begin?
???? select @fullbafile=backupfilename from @tmp
???? exec xp_fileexist @fullbafile,@result output
???if (@result=0 )
???begin
??? set? @log_start=4
???end
??end

if @log_start !=0
?begin
???? SET @ErrorMessage = '数据库'+@dbname+'没有完全备份或差异备份!: ' + CHAR(13) + CHAR(10)
???? RAISERROR(@ErrorMessage,1) WITH NOWAIT
???? return
?end

????
????
????
????
????
???? ------------------------------------------------------------------------------
????
???? --隐藏检查目录
???? set @backupPath3=@backupPath2+''+'log'
???? 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,'')+ '.trn'
??backup log @dbname to disk=@FullPath WITH COMPRESSION,RETAINDAYS=3,
??NAME=N'日志备份',STATS=10
??
??set @backtype='L'
??set @backupdesc='日志备份'
??set @backupfilename=@FullPath
??insert into msdb.dbo.backuphistory
??(dbname,@backupfilename)
???? SET @Error = @@ERROR
???? if @Error !=0
???? begin
???? SET @ErrorMessage = '数据库'+@dbname+'日志备份未顺利完成！: ' + CHAR(13) + CHAR(10)
???? RAISERROR(@ErrorMessage,1) WITH NOWAIT
???? return
???? end
??
??
???print '??????????????????????????????????????????????????????????????????? '
??? print '-------------------------------------------------------------------------'
??? print @dbname+'日志备份 '+@FullPath+' 已经完成!' ?? print '---------------------------------------------------------------------------' ?? return ?end ?set nocount off ?GO ?

（编辑：李大同）

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