备份脚本小结

1、数据库完整备份和差异备份的存储过程：

USE [xxxDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
-- backpath 备份路径
-- deldate 保存备份的天数
-- dbname备份的数据库名

CREATE proc [dbo].[DBA_BackupFullDB]
@deldate varchar(50)
,@bakpath varchar(50)
,@dbname varchar(50)
as
declare @sqltxtdel varchar(max)
,@createpath varchar(max)
,@backupdb varchar(max)
,@ddate varchar(8)
,@dweek varchar(10)
,@weekday int
,@filename varchar(500)
,@sqltxtcopy varchar(max)
set @ddate = convert(char(8),getdate(),112)
set @dweek = datepart(week,getdate())
set @weekday = datepart(weekday,getdate())
set @createpath='exec (''xp_create_subdir '''''+@bakpath+'\'+@dbname+''''''')'
print @createpath
exec (@createpath)
if (@weekday = 1 )
begin
--set @deldate='-9' 保存备份的天数
set @sqltxtdel ='xp_cmdshell ''forfiles /P '+@bakpath+' /D '+@deldate+' /S /M *.full /c "cmd /c del @file"'''
print @sqltxtdel
exec (@sqltxtdel)
--完整备份
set @backupdb='
backup database ['+@dbname+'] to disk = '''+@bakpath+'\'+@dbname+'\'+@dbname+'_'+@dweek+'_'+@ddate+'.full''
WITH CHECKSUM, COMPRESSION
'
print @backupdb
exec (@backupdb)
end
else
begin
--set @deldate = '-8'
set @sqltxtdel ='xp_cmdshell ''forfiles /P '+@bakpath+' /D '+@deldate+' /S /M *.diff /c "cmd /c del @file"'''
print @sqltxtdel
exec (@sqltxtdel)
--差异备份
set @backupdb='
backup database ['+@dbname+'] to disk = '''+@bakpath+'\'+@dbname+'\'+@dbname+'_'+@dweek+'_'+@ddate+'.diff''
WITH CHECKSUM,COMPRESSION,differential
'
-- print @backupdb
exec (@backupdb)
end

GO

 

以上存储过程可以结合下面的作业脚本实现每日的备份：

declare @delday int,
        @bakpath varchar(50),
        @dbname varchar(500)

set @delday='-16'
set @bakpath='F:\DBbackup'

-- backpath 备份路径
-- deldate 保存备份的天数
-- dbname备份的数据库名

declare  mycursor cursor for
select db_name(b.database_id)
from sys.dm_hadr_availability_replica_states a 
    join sys.dm_hadr_database_replica_states  b on a.group_id=b.group_id and a.replica_id=b.replica_id
where role=1
open mycursor
fetch next from mycursor into @dbname
--获取需要备份的数据库名称（always on中的数据库）
while @@FETCH_STATUS=0
begin 
print @dbname
exec DBA_BackupFullDB @delday,@bakpath,@dbname

fetch next from mycursor into @dbname
end

close mycursor
deallocate mycursor

2、日志备份的可用的存储过程：

USE [xxDB]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE proc [dbo].[DBA_BackupLog]
        @deldate varchar(50)
        ,@bakpath varchar(50)
        ,@dbname varchar(50)
as        

declare @sqltxtdel varchar(max)
        ,@createpath varchar(max)
        ,@backupLog varchar(max)
        ,@sn nvarchar(50)

--set @bakpath  备份路径
--set @deldate  日志保留天数

set @createpath='
sp_msforeachdb ''xp_create_subdir '''''+@bakpath+'\'+@dbname+'\'''''''
print @createpath
exec (@createpath)

set @sqltxtdel ='
xp_cmdshell ''forfiles /P '+@bakpath+'\'+@dbname+' /D '+@deldate+' /S /M *.trn /c "cmd /c del @file"'''
print @sqltxtdel
exec (@sqltxtdel)
-- 日志备份开始
set @sn = replace(replace(replace(convert(varchar,getdate(),120),'-',''),' ',''),':','')
set @backupLog='backup Log ['+@dbname+'] to disk = '''+@bakpath+'\'+@dbname+'\'+@dbname+'_No'+@sn+'.trn'' with compression'
print @backuplog
exec (@backuplog)




GO

以上存储过程可在作业中调用，根据需要制定备份的计划，可在sqlserver代理的作业中实现备份策略。

3、在作业执行的时候可能会报SQL Server 阻止了对组件“xp_cmdshell”的 过程“sys.xp_cmdshell”的访问，因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用 sp_configure 启用“xp_cmdshell”。

这时候需要开启“xp_cmdshell”

在新建查询窗口执行：

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;