Text
/*
THIS4.0 数据库备份脚本
[著作] Rulition QQ:7355157
[版本] v2.1
[修正]
2010年5月21日15:35:13 完成
2010年5月24日11:35:13 细节修正
2010年5月25日11:31:13 增长[完全备份]前的一致性检讨
2010年5月27日10:31:13 检讨差别备份的大小,避免过大文件影响差别备份.
2010年5月28日11:14:15 补备份时查看最近是不是已做过完全备份.
2010年10月17日21:58:32 切换@备份路径[如果是第一次运行,可以 Select @备份路径 = @备份路径1]
2011年03月17日15:00:00 增长单双月备份,增长完全备份频次,修证(30)删除过期备份文件
2012-10-26 14:53 修正 月备份 到 \202.202.202.26\Y$\This_BackUp
2012-10-31 10:00 增长 正常运行时 显示 ‘可用磁盘空间不足’的中间盘算成果。
2015年03月31日10:00:00 修正解释，并将 DBCC 改成晚上履行.
----
2015-04-03 09:00 v2.0 修正流程，将外部援用的程序放在前部，处置参数校验，增长更多的解释，差别备份文件太大时改成提醒手工处置。
[环境] SQL2000 和 SQL2005 中测试通过
[起源] https://blog.csdn.net/rulition/article/details/47999561
[解释]
应用双机热备的磁盘空间,将 THIS4.0 数据库的备份筹划设置为以下方法:
"差别备份"-每N小时履行一次,分离存储到 1# 和 2# 的 C: , 保存N天。
"完全备份"-按可用空间，指定在 12:25 21:25 03:25 ,存储到 群集空间，保存X天。
例如：本脚目前的设置状况为:
1\按 SQL 筹划义务的请求，每3小时的25分时运行一次，在 12:25、21:25、03:25 这三个时光点进行完全备份，其他时光点进行差别备份。
2\每一个月1号的月备份后缀名不再是.bak 而是 .ybf。（还可以按单双月，将备份放在不同的处所.）
3\数据库的一致性检讨放在下半夜进行。
[特色]
1）备份频度由 SQL 筹划义务来调理
2）操作日志由 SQL 筹划义务通过追加方法进行搜集 (详见筹划义务的高等选项)
3) 先删除过期备份文件，然后再履行备份操作，与 SQL 操作模式相反
（也能够修正 (备份前的预备工作) 这一小节，与 SQL 操作模式一样。）
4）主动断定并树立备份文件夹、按保存时光删除过期备份文件
5）在履行备份操作前，主动检讨空间是不是够用，主动躲避[差别备份][完全备份]同时进行，并进行一致性检讨。
6）检讨差别备份的大小,避免过大文件影响差别备份.并主动做一次[完全备份]
[注意]
1）脚本中大批应用 MS-DOS 命令，如果不是很熟习，请不要修正！
2）为删除过期备份文件应用了 FORFILES.exe 命令，否则没法履行。
这个可以在 Win2000 环境下应用的命令，是 FORFILES v 1.1 - [email protected] - 4/98
与 Win7 体系自带的中文版，在应用参数上有一点区分，应用时请注意！
3）为减少操作日志大小，在 (一致性检讨) 这一小节调用了 isql.exe ，这个文件在 SQL2005 中没有，
可以从 SQL2000 体系中拷贝过来，应用时注意路径。
4) 所有外部援用的程序(含路径)，必需在 1#机 和 2#机 寄存在同一路径下，且此程序不缺乏相应的
支撑文件，否则，程序将没法正常运行。
5) 所需 FORFILES.exe 和 isql.exe 可从以下地址获得：https://download.csdn.net/detail/rulition/9048595
[参考]
1)清算体系备份日志、 删除备份作业的历史记载
2)关于SQL2005 远程备份
*以上资料详见脚本最后一部份
exec usp_job_backup_this4 0
*/
CREATE Proc usp_job_backup_this4
(@Is_Run int = 0 ) ---- 0-测试运行并显示操作记载 ,当 @Is_Run = 1 时，主动履行所有操作。
AS
----
----
SET NOCOUNT ON
----
Select GetDate() AS "【开端时光】"
--- Test
-- Declare @Is_Run int
-- Select @Is_Run = 0
----
IF @Is_Run = 0 Print "【测试状况】"
----
----
/*设置外部援用程序(含路径)*/
declare @ISQL varchar(50)
declare @FORFILES varchar(50)
Select @ISQL="C:\THIS_BAT\isql.exe"
Select @FORFILES="C:\THIS_BAT\forfiles.exe"
----开启 xp_cmdshell 支撑
-- Exec sp_configure "show advanced options", 1
-- reconfigure with override
-- Exec sp_configure "xp_cmdshell", 1
-- reconfigure with override
-- Exec sp_configure "show advanced options", 0
-- reconfigure with override
----参数检讨
declare @SQL varchar(8000)
Select @SQL="dir "+@ISQL
declare @result int
Exec @result = xp_cmdshell @SQL,NO_OUTPUT
IF (@result = 1)
Begin
Print "毛病:没有找到外部援用程序: "+@ISQL
Return
End
Select @SQL=Replace(@SQL,@ISQL,@FORFILES)
Exec @result = xp_cmdshell @SQL,NO_OUTPUT
IF (@result = 1)
Begin
Print "毛病:没有找到外部援用程序: "+@FORFILES
Return
End
----
----
/*设置运行参数*/
Declare @数据库名 varchar(100)
Declare @备份路径 varchar(100)
Declare @备份文件 varchar(100)
Declare @备份方法 varchar(50)
Declare @保存时光 varchar(10)
Declare @备份语句 varchar(1000)
----
Select @数据库名 = "THIS4"
Select @备份文件 = Convert(char(8),getdate(),112) + replace(convert(char(6),getdate(),108),":","")
----
----
/*完全备份-参数设置*/
IF Convert(varchar,getdate(),24) between "03:00:00" and "04:00:00"
Begin
Select @备份方法 = "完全备份"
Select @备份路径 = "\202.202.202.26\Y$\This_BackUp"
Select @备份文件 = @备份文件+".bak"
Select @保存时光 = "2"
----每一个月1号的备份其后缀名改成 .ybf
---- IF ( DAY(getdate())=1 and MONTH(getdate())%2 = 1 ) ----单月
---- IF ( DAY(getdate())=1 and MONTH(getdate())%2 = 0 ) ----双月
IF DAY(getdate())=1
Begin
Select @备份方法 = @备份方法 +"【月备份】"
Select @备份路径 = "\202.202.202.26\Y$\This_BackUp"
Select @备份文件 = Replace(@备份文件,".bak",".ybf")
Select @保存时光 = "9600"
End
End
----
ELSE
IF Convert(varchar,getdate(),24) between "12:00:00" and "13:00:00"
Begin
Select @备份方法 = "完全备份"
Select @备份路径 = "\202.202.202.46\X$\This_BackUp"
Select @备份文件 = @备份文件+".bak"
Select @保存时光 = "2"
End
----
ELSE
IF Convert(varchar,getdate(),24) between "21:00:00" and "22:00:00"
Begin
Select @备份方法 = "完全备份"
Select @备份路径 = "\202.202.202.26\Z$\This_BackUp"
Select @备份文件 = @备份文件+".bak"
Select @保存时光 = "2"
End
ELSE
----
----
/*差别备份-参数设置（如需时光限制，可以参考上面的办法。）*/
Begin
Select @备份方法 = "差别备份"
Declare @备份路径1 varchar(100)
Declare @备份路径2 varchar(100)
Select @备份路径1 = "\202.202.202.32\C$\This_BackUp"
Select @备份路径2 = "\202.202.202.33\C$\This_BackUp"
Select @备份文件 = @备份文件+".dif"
Select @保存时光 = "1"
End
----
----
Select "★" + @备份方法 + "★"
----
----
/*参数检讨*/
----检讨@数据库名
IF not exists ( Select 1 from master.dbo.sysdatabases where name=@数据库名)
Begin
Select "毛病:没有找到指定的数据库名，或是当前实例区分大小写！"
Return
End
----提取上次备份的有效数据
Declare @backup_set_id int ----备份操作的ID号
Declare @backup_size numeric ----备份文件大小，其成果/1024能力与电脑看到的一致[单位:KB]
Declare @description nvarchar(510) ----备份描写
Declare @name nvarchar(256) ----备份名称，后面跟上"_KB"+当时备份时的库文件大小[单位:KB]
Declare @backup_finish_date datetime ----备份完成时光
Declare @expiration_date datetime ----备份过期时光
----初值设置
Select @backup_size=1, @description="没有找到有效可用的备份文件", @name="无"
----按备份方法供给取最后一次数据
Select top 1 @backup_set_id = backup_set_id , @backup_size = isnull(backup_size/1024,1) ,
@description = [description] , @name = [name] , @backup_finish_date = backup_finish_date , @expiration_date = expiration_date
from msdb.dbo.backupset (NOLOCK)
where type = case when @备份方法 = "差别备份" then "I" ELSE "D" end
and database_name = @数据库名
and expiration_date > getdate() ----有效数据
order by backup_set_id DESC
----显示中间数据
IF @Is_Run = 0
Begin
Print "上次备份的有效数据"
Select @backup_set_id as "ID号", @backup_size as "文件大小", @description as "描写", @name as "名称",
@backup_finish_date as "完成时光", @expiration_date as "过期时光"
End
----处置@备份路径
IF @备份方法 = "差别备份"
Begin
----切换@备份路径
Select @备份路径 = @备份路径2
IF @description = @备份路径1+"\"+@数据库名 Select @备份路径 = @备份路径2
IF @description = @备份路径2+"\"+@数据库名 Select @备份路径 = @备份路径1
else Print "【上次备份】" + @description
End
Print "【本次备份】" + @备份路径 + "\" + @数据库名
----检讨@备份路径
Select @SQL = " IF not exist " + @备份路径 +"\"+@数据库名 + "\*.* md "+ @备份路径 +"\" +@数据库名
Exec @result = xp_cmdshell @SQL , NO_OUTPUT
IF (@result = 1)
Begin
Print("毛病:没法拜访指定的备份路径 "+ @备份路径+" (缘由：1、没有拜访权限。2、目的磁盘不存在。) " )
Print( @SQL )
Return
End
----
----
Print "【备份文件】" + @备份文件
----
----
/*备份前的预备工作*/
---------------------------------------------------------------------------------------------
----(10)获得@备份路径所在分区的剩余空间大小[已换成KB,近似值= 可用字节/1024*0.95 ]
Select @SQL=" dir "+ @备份路径 + " | find "可用字节" > "%temp%\Rulition" "
Exec xp_cmdshell @SQL , NO_OUTPUT
Exec xp_cmdshell " for /f "tokens=3 delims= " %i in (%temp%\Rulition) do @echo %i > "%temp%\Rulition" " , NO_OUTPUT
Select @SQL=" for /f "tokens=1-9 delims=," %1 in (%temp%\Rulition) do @echo %1%2%3%4%5%6%7%8%9 "
----将成果传入到表[必需充许空值插入，否则会出错！]
Create Table #disk_space (free_sizes VarChar(100) NULL )
Insert into #disk_space Exec xp_cmdshell @SQL
Delete from #disk_space where free_sizes is null
----提取成果
Declare @disk_space numeric
Select @disk_space = Convert(numeric,free_sizes)/1024*0.95 from #disk_space
Drop table #disk_space
---------------------------------------------------------------------------------------------
----(20)获得当前数据库大小(参考:sp_spaceused)[已换成KB,近似值= 统计成果/1024*0.95 ]
Select @SQL = " Declare @dbsize bigint " + CHAR(10) +
" Declare @logsize bigint " + CHAR(10) +
" Declare @database_size bigint " + CHAR(10) +
" Select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size ELSE 0 end)) ,"+ CHAR(10) +
" @logsize = sum(convert(bigint,case when status & 64 <> 0 then size ELSE 0 end))" + CHAR(10) +
" from " + @数据库名 + ".dbo.sysfiles " + CHAR(10) +
" Select (convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize) ) * 8192 "
----将成果传入到表
Create Table #database_size (files_sizes dec(15,2) NULL )
Insert into #database_size Exec (@SQL)
----提取成果
Declare @database_size numeric
Select @database_size = Convert(numeric,files_sizes)/1024*0.95 from #database_size
Drop table #database_size
---------------------------------------------------------------------------------------------
----(25)一致性检讨
/*如果一致性检讨在白天进行时要消费很长时光,可以设置为只在晚上处置。(具体情形详见操作成果日志)*/
IF (@备份方法 = "完全备份" and (Convert(varchar,getdate(),24) between "03:00:00" and "04:00:00") )
Begin
/*
在数据库备份之前，应当进行数据的一致性检讨：
1。运行检讨点过程 checkpoint [强迫将当前数据库的所有脏页写到磁盘上。]
2。检讨数据库 dbcc checkdb
3。检讨页面 dbcc checkalloc
4。检讨体系表 dbcc checkcatalog
然后再进行数据库备份。
----
【DBCC CHECKDB】 是大批占用 CPU 和磁盘的操作。每一个须要检讨的数据页都必需首先从磁盘读入内存。
另外，DBCC CHECKDB 应用 tempdb 排序。建议在服务器负荷较少的时候运行 DBCC CHECKDB。
如果在负荷高峰期运行 DBCC CHECKDB，那末事务吞吐量性状和 DBCC CHECKDB 完成时光性状都会遭到影响。
----
【DBCC CHECKALLOC】 对数据库中的分配和页应用（包含索引视图）情形进行检讨。
只用于向后兼容性的 NOINDEX 选项也实用于索引视图。
如果已履行 DBCC CHECKDB，则没必要履行 DBCC CHECKALLOC。
DBCC CHECKDB 是 DBCC CHECKALLOC 的超集，除对索引构造和数据完全性进行检讨以外，它还包含对分配进行检讨。
*/
----★一致性检讨[只保存检讨的终究成果到日志文件，注意修正 isql.exe 应用的用户名和密码还有 isql.exe 所在的路径]★
Print "【一致性检讨】"
Declare @CHECK VarChar(1000)
----
Print "1.运行检讨点过程 (" + Convert(varchar,GetDate(),21) + ")"
IF (@Is_Run = 1) CHECKPOINT
----
Print "2.检讨数据库 (" + Convert(varchar,GetDate(),21) + ")"
Select @CHECK = @ISQL+" -S "+ @@SERVERNAME + " -d "+ @数据库名 + " -U sa -P SERVER -Q "dbcc checkdb (" + @数据库名 + ") " -o "%temp%\check_db.log" -w 9999 & find "CHECKDB" "%temp%\check_db.log" "
IF (@Is_Run = 0) Print @CHECK
IF (@Is_Run = 1) EXEC xp_cmdshell @CHECK
----
Print "3.检讨页面 (" + Convert(varchar,GetDate(),21) + ")"
Select @CHECK = @ISQL+" -S "+@@SERVERNAME + " -d "+ @数据库名 + " -U sa -P SERVER -Q "dbcc checkalloc (" + @数据库名 + ") " -o "%temp%\check.log" -w 9999 & find "CHECKALLOC" "%temp%\check.log" "
IF (@Is_Run = 0) Print @CHECK
IF (@Is_Run = 1) EXEC xp_cmdshell @CHECK
----
Print "4.检讨体系表 (" + Convert(varchar,GetDate(),21) + ")"
Select @CHECK = @ISQL+" -S " + @@SERVERNAME + " -d " + @数据库名 + " -U sa -P SERVER -Q "dbcc checkcatalog (" + @数据库名 + ")" "
IF (@Is_Run = 0) Print @CHECK
IF (@Is_Run = 1) EXEC xp_cmdshell @CHECK
----
Print "5.一致性检讨已完成 (" + Convert(varchar,GetDate(),21) + ")"
End
---------------------------------------------------------------------------------------------
----(30)删除过期备份文件
---- 凡大于(@保存时光)24小时的都会被删除,注意扩大名!!
---- forfiles.exe 也要指定路径，否则没法履行这个操作.
Declare @删除操作 nvarchar(4000)
----
Select @删除操作 =" pushd " + @备份路径 + "\" + @数据库名 + " && " +
@FORFILES +" -M"+@数据库名+"_*.bak -c"cmd /c echo 删除 @FILE & del @FILE " -D-"+ @保存时光 + " && " +
" popd "
Print "【删除文件】保存时光: " + @保存时光 + " 天"
----处置差别备份 .dif
IF @备份方法="差别备份" Select @删除操作=Replace(@删除操作,"_*.bak","_*.dif")
----每一个月1号的备份其后缀名改成 .ybf
IF DAY(getdate())=1 Select @删除操作=Replace(@删除操作,"_*.bak","_*.ybf")
----
IF (@Is_Run = 0) Print @删除操作
IF (@Is_Run = 1) Exec xp_cmdshell @删除操作
---------------------------------------------------------------------------------------------
----(40)备份可行性估算
---- 如果 [剩余空间] 不够寄存 [预期备份]的大小，先删除过期备份文件，如果还不够用，则退出。
---- [预期备份的大小] = [上次备份文件的大小] / [上次备份时数据库的大小] * [当前数据库的大小]
---- SQL的备份策略是先备份后删除，事前也不断定一下，很糟蹋时光。
----
----[上次备份时数据库的大小]取自 msdb.dbo.backupset.name ,在生成备份语句时进行标志。
----
Declare @last_data_sizes numeric
Declare @评估成果 varchar(8)
----
Select @last_data_sizes = 1
Select @评估成果 = "通过"
----如果上次备份时没有"_KB"标志，则默以为1。
IF ( CharIndex("_KB",@name) = 0 ) Select @last_data_sizes = 1
ELSE
Begin
----如果唯一"_KB"标志，但没有数据，也默以为1。
IF ( CharIndex("_KB",@name) + 3 ) > LEN(@name) Select @last_data_sizes = 1
----否则，提取"_KB"后的数据
ELSE Select @last_data_sizes = Convert(numeric,SubString(@name,CharIndex("_KB",@name)+3,20))
End
----显示评估情形明细
IF (@Is_Run = 0)
Begin
Print "【评估情形】"
Select @backup_set_id as "上次备份的ID: "
Select isnull(@backup_size,1) as "上次备份文件的大小(KB)" , isnull(@last_data_sizes,1) as "上次备份时数据库的大小(KB)"
Select @database_size as "当前数据库的大小(KB)" , @disk_space as "可用磁盘空间(KB)"
Print "【本次备份】" + @备份路径 + "\"+ @数据库名
End
----开端评估
----检讨[完全备份]的大小
IF @备份方法="完全备份"
/* 如果[上次备份文件的大小] = 1 , 则比拟 [当前数据库的大小]*0.80 与 [可用磁盘空间]
这里的 (*0.80) 是人个经验值,可以联合每次的【评估情形】大致算出来。*/
Begin
IF @last_data_sizes = 1
Begin
IF @database_size * 0.80 > @disk_space
Begin
Select @备份路径 + " 可用磁盘空间不足，没法存入数据库备份文件，退出操作！" + CHAR(10) as "【毛病提醒1】"
Select @database_size * 0.80 as "@database_size * 0.80" , @disk_space as "@disk_space"
Return
End
End
----[预期备份的大小] = [上次备份文件的大小] / [上次备份时数据库的大小] * [当前数据库的大小]
ELSE
Begin
IF ( @backup_size / @last_data_sizes ) * @database_size > @disk_space
Begin
Select @备份路径 + " 可用磁盘空间不足，没法存入数据库备份文件，退出操作！" + CHAR(10) as "【毛病提醒2】"
Select ( @backup_size / @last_data_sizes ) * @database_size as "预期备份的大小", @disk_space as "磁盘空间"
Return
End
End
End
----检讨[差别备份]的大小
IF( @备份方法 = "差别备份" and ( @last_data_sizes <> 1 ) )
/*如果 [上次备份文件的大小] 相对 [当前数据库的大小] 超过 30% ,解释上次的完全备份失败了,与此同时，最近又没有做过完全备份,
则必需再次进行完全备份,否则,数目过量的大体积差别备份文件会占用对应的磁盘空间,造成差别备份的失败。(v2.0提醒并退出)
这里的 (超过 30%) 是人个经验值,可以联合每次的【评估情形】大致算出来。*/
Begin
IF (isnull(@backup_size,1) / @database_size > 0.3)
IF NOT EXISTS (Select 1 from msdb.dbo.backupset where type = "D" and backup_set_id > @backup_set_id and [name] like "%" + @数据库名 + "%" )
Begin
Print "【有情提醒】" + "上次(差别备份)的文件偏大，有可能最近一次的完全备份失败！请手工运行【完全备份】进行补救。"
Print "【有情提醒】" + "如果屡次涌现这个问题，请检讨相干所有操作或设置。"
Select @评估成果 = "失败"
Return
End
End
----
IF @评估成果 = "通过" Print "【评估通过，可以备份!】"
---------------------------------------------------------------------------------------------
/*组织@备份语句*/
Select @备份语句=""
Select @备份语句="BACKUP DATABASE "+ @数据库名 + " TO DISK = N""" + @备份路径+"\"+@数据库名+"\"+ @数据库名 + "_" +@备份文件 +
""" WITH RETAINDAYS = " + @保存时光 + ", NOFORMAT, NOINIT, NAME = N""" +
@数据库名 + "_" + @备份方法 + "_" + @备份文件 +"_KB" + Convert(varchar,@database_size) +
""", SKIP, NOREWIND, NOUNLOAD, STATS = 100,"
+ " DESCRIPTION = """+ @备份路径 + "\" + @数据库名 +""""
----
IF @备份方法 = "差别备份" Select @备份语句 = @备份语句 + " , DIFFERENTIAL "
----
/*显示@备份语句*/
Print "【备份语句】"
Print @备份语句
----
/*[差别备份]躲避[完全备份]*/
IF exists(Select spid from master.dbo.sysprocesses where db_name(dbid) = @数据库名
and status = "runnable" and cmd= "BACKUP DATABASE")
Begin
Print @数据库名 + " 正在备份，跳过本次操作!"+ CHAR(10)
Return
End
----
/*履行操作*/
IF (@Is_Run = 1) Exec (@备份语句)
----
Select GetDate() AS "【停止时光】"
----
/*
[参考资料1]
----清算日志记载
Select * from msdb.dbo.backupset
Select * from msdb.dbo.restorehistory
----
删除备份和还原历史记载表中所有早于指定日期的备份集的条目。
由于履行备份或还原操作后会在备份和还原历史记载表中添加一些行，
因此应用 sp_delete_backuphistory 可以减小 msdb 数据库中历史记载表的大小。
----
Declare @dt datetime
Select @dt = cast(N"01/01/2015 00:00:00" as datetime)
exec msdb.dbo.sp_delete_backuphistory @dt
----删除作业的历史记载
EXEC msdb.dbo.sp_purge_jobhistory @oldest_date="01/01/2015 00:00:00"
EXEC msdb..sp_maintplan_delete_log null,null,"01/01/2015 00:00:00"
[参考资料2]
关于 SQL2005 远程备份
Rulition 10:19 2009-10-17
要实现 SQL 远程备份,必需处置好本机远程拜访的问题：
第一、远程盘算机与本机最好在同一网段内，如果跨了网段（或VLAN），需交流机路由支撑，乃至修正本地路由表。
第二、为本机供给 SQL 服务的“用户名”和“密码”必需是[远程盘算机]的[合法用户]，且能对[远程盘算机]的[备份文件夹]进行[完全操作]。
一般情形下，在安装 SQL 服务时应用的是体系帐户（多半是 administrator），如果恰好是远程盘算机的合法用户，则处置起来就简略多了。
否则，在进行远程备份时必需应用 xp_cmdshell 调用 MS-DOS 的 net use 命令，应用对方的[合法用户]进行登记，以操作[备份文件夹]。
第三、至于[远程盘算机]的[备份文件夹]，可以事前在[远程盘算机]上将[备份文件夹]进行同享，并指定可完全拜访的用户名。
如果不想让无关的人看到这个[备份文件夹]，可在同享时将同享名后加上$，例如：备份文件夹$
或，在应用 net use 进行[合法用户]登记时，直接应用对方超级用户，这样可直接拜访[备份文件夹]。
例如：\COMPUTER\E$\备份文件夹
net use \COMPUTER\E$\备份文件夹 /user:用户名 口令
如果是空口令，可用""取代: /user:用户名 ""
*/
<span></span>
Text
/*
THIS4.0 数据库备份脚本
[著作] Rulition QQ:7355157
阅读背景：
双机热备数据库备份脚本 v2.1
来源：互联网
分享到：
