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

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

   ) 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 = '输入的目录格式'+ '不支持！.'+' 参考类型如: 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 = '数据库名: 不存在!.' + 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 = '脱机的数据库不需要备份.' + 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 '不支持类型!   只能输入(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 '系统将新建目录： ............' 

   EXEC master.dbo.xp_create_subdir @backupPath2

   print '目录：新建成功!'

   print '         '

   delete from  @DirTree

end

else

begin

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

 print '目录：已经存在！'

 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

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 = '数据库完全备份未顺利完成！: ' + CHAR(13) + CHAR(10)

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    return

    end

  

print '                                                                    '

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

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

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 and a.backtype='D'

 group by backupfilename

 order by a.backupfilename desc

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

    begin

    SET @ErrorMessage = '数据库没有完全备份历史记录!!' + 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 = '数据库完全备份文件不存在!做差异备份无意义!' + 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

 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 = '数据库差异备份未顺利完成！: ' + CHAR(13) + CHAR(10)

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    return

    end

 

 

 print '                                                                    '

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

   print @dbname+'差异备份 已经完成!'

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

  return

end

 

 

--开始日志备份

else  if @backuptype='log'

begin

 print '                                                                        '

    print '检查环境.....请稍等..'

    print '                                                                        '

    --检查数据库恢复模式

 select @recovery_model_desc=recovery_model_desc from sys.databases

 where

 if @recovery_model_desc not in ('full')

 begin

 print '########错误信息######################################################'

 print '                                                                         '+char(13)+'请检查数据库的恢复模式!'+char(13)+ '使用命令ALTER DATABASE 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 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 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 = '数据库没有完全备份或差异备份!: ' + 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 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 = '数据库日志备份未顺利完成！: ' + CHAR(13) + CHAR(10)

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    return

    end

 

 

  print '                                                                    '

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

   print @dbname+'日志备份 已经完成!'

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

  return

end

set nocount off

GO