自己的定义的容灾方案,可以根据自己的需要自己定义.
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.',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+'已经存在!'
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
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,16,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,getdate(),20),'-',''),' ',''),':','')+ '.trn'
backup log @dbname to disk=@FullPath WITH COMPRESSION,RETAINDAYS=3,NOFORMAT,NOINIT,
NAME=N'日志备份',SKIP,NOREWIND,
NOUNLOAD,STATS=10
set @backtype='L'
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
set nocount off
GO
分享到:
相关推荐
新形势下的DELL EMC数据容灾保护解决方案-v1 -- Dell.pdf
新形势下的DELL EMC数据容灾保护解决方案-v1 -- Dell.zip
容灾-异地容灾备份-容灾一体机-数据安全备份-数据库双活网关.docx
sqlserver 2008双机热备,最好有三台服务器,其中一台当做见证服务器,没有也没有关系,该文档从实施到最后测试均有详细的记载,其中实施过程中遇到的问题也在网上得到了解答,感谢互联网上的大神博客。如有问题可以...
方案-惠普容灾与备份解决方案介绍 方案-金融灾备数据中心技术方案 方案-某电信容灾方案 方案-容灾解决方案 方案-容灾需求分析及方案 方案-软件定义的容灾解决方案 方案-数据容灾备份解决方案 方案-智慧容灾 课件-...
容灾备份:成就兴隆大家庭“60小时不夜城”
NetApp 容灾备份技术(SnapMirror-SnapVault-OSSV).pdf
这个是介绍本地的集群方案,有很多集群容灾方案方面参考价值
SQL数据库负载均衡及容灾方案PDF
数据中心网络存储容灾技术白皮书----内部文档
Oracle容灾备份: 实时备份、 数据迁移应用实践 内容: DDS实时复制软件的技术体系 DRS逻辑备份与恢复 DDS解决方案与案例 DDS产品特点 技术交流
两地三中心不仅是容灾/双活,更是 对基础架构的持续优化
云上跨Region容灾-异地容灾解决方案 云上两地三中心容灾解决方案 D2C备份,备份软件部署在云中 D2C备份,备份软件部署在用户数据中心 D2D2C备份,本地备份再分级存储至云 二维码支付业务跨云双活容灾 Region内跨AZ...
通过与XX公司的技术人员的探讨和交流沟通,针对用户在日常维护过程中所碰到的一些迫切需要解决的问题,通过全面、深入的测试来检验爱数备份平台是否能具有满足各种实际需求的功能和特性,为今后数据保护与恢复管理的...
容灾-备份-数据恢复-异地容灾.pdf
DELL EMC数据容灾保护解决方案-v1.pdf
本文将通过介绍一些业界主流的数据库高可用架构、每种方案的特性和优缺点,以及数据库高可用架构的自动化运维实现,讲讲数据库高可用容灾方案设计与实现,希望抛砖引玉,和大家一起讨论。
MySQLreplicate容灾方案.docx
Oracle数据库异地容灾方案.doc
HDS 容灾方案,orace数据库复制技术。。