再次完善了下备份T-sql:
create
proc
sp_backupdatabase
@bak_path
nvarchar
(
4000
)
=
''
--
备份路径;
,
@baktype
int
=
null
--
备份类型为全备,1为差异备,2为日志备份
,
@type
int
=
null
--
设置需要备份的库,0为全部库,1为系统库,2为全部用户库,3为指定库,4为排除指定库;
,
@dbnames
nvarchar
(
4000
)
=
''
--
需要备份或排除的数据库，用,隔开，当@type=3或4时生效
,
@overdueDay
int
=
null
--
设置过期天数，默认天;
,
@compression
int
=
0
--
是否采用sql2008的压缩备份,0为否,1为采用压缩
as
--
sql server 2005/2008备份/删除过期备份T-sql 版本v1.0
/*
author:perfectaction
date :2009.04
desc :适用于sql2005/2008备份，自动生成库文件夹，可以自定义备份类型和备份库名等
可以自定义备份过期的天数
删除过期备份功能不会删除最后一次备份，哪怕已经过期
如果某库不再备份，那么也不会再删除之前过期的备份
如有错误请指正，谢谢.
*/
set
nocount
on
--
开启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
print
char
(
13
)
+
'
------------------------
'
--
判断是否填写路径
if
isnull
(
@bak_path
,
''
)
=
''
begin
print
(
'
error:请指定备份路径
'
)
return
end
--
判断是否指定需要备份的库
if
isnull
(
ltrim
(
@baktype
),
''
)
=
''
begin
print
(
'
error:请指定备份类型aa:0为全备,1为差异备,2为日志备份
'
)
return
end
else
begin
if
@baktype
not
between
0
and
2
begin
print
(
'
error:指定备份类型只能为,1,2: 0为全备,1为差异备,2为日志备份
'
)
return
end
end
--
判断是否指定需要备份的库
if
isnull
(
ltrim
(
@type
),
''
)
=
''
begin
print
(
'
error:请指定需要备份的库,0为全部库,1为系统库,2为全部用户库,3为指定库,4为排除指定库
'
)
return
end
else
begin
if
@type
not
between
0
and
4
begin
print
(
'
error:请指定需要备份的库,0为全部库,1为系统库,2为全部用户库,3为指定库,4为排除指定库
'
)
return
end
end
--
判断指定库或排除库时，是否填写库名
if
@type
>
2
if
@dbnames
=
''
begin
print
(
'
error:备份类型为
'
+
ltrim
(
@type
)
+
'
时，需要指定@dbnames参数
'
)
return
end
--
判断指定指定过期时间
if
isnull
(
ltrim
(
@overdueDay
),
''
)
=
''
begin
print
(
'
error:必须指定备份过期时间,单位为天,0为永不过期
'
)
return
end
--
判断是否支持压缩
if
@compression
=
1
if
charindex
(
'
2008
'
,
@@version
)
=
0
or
charindex
(
'
Enterprise
'
,
@@version
)
=
0
begin
print
(
'
error:压缩备份只支持sql2008企业版
'
)
return
end
--
判断是否存在该磁盘
declare
@drives
table
(drive
varchar
(
1
),
[
size
]
varchar
(
20
))
insert
into
@drives
exec
(
'
master.dbo.xp_fixeddrives
'
)
if
not
exists
(
select
1
from
@drives
where
drive
=left
(
@bak_path
,
1
))
begin
print
(
'
error:不存在该磁盘:
'
+left
(
@bak_path
,
1
))
return
end
--
格式化参数
select
@bak_path
=
rtrim
(
ltrim
(
@bak_path
)),
@dbnames
=
rtrim
(
ltrim
(
@dbnames
))
if
right
(
isnull
(
@bak_path
,
''
),
1
)
!=
'
/
'
set
@bak_path
=
@bak_path
+
'
/
'
if
isnull
(
@dbnames
,
''
)
!=
''
set
@dbnames
=
'
,
'
+
@dbnames
+
'
,
'
set
@dbnames
=
replace
(
@dbnames
,
'
'
,
''
)
--
定义变量
declare
@bak_sql
nvarchar
(
max
),
@del_sql
nvarchar
(
max
),
@i
int
,
@maxid
int
declare
@dirtree_1
table
(id
int
identity
(
1
,
1
)
primary
key
,subdirectory
nvarchar
(
600
),depth
int
,files
int
)
declare
@dirtree_2
table
(id
int
identity
(
1
,
1
)
primary
key
,subdirectory
nvarchar
(
600
),depth
int
,files
int
,
dbname
varchar
(
300
),baktime
datetime
,isLastbak
int
)
declare
@createfolder
nvarchar
(
max
),
@delbackupfile
nvarchar
(
max
),
@delbak
nvarchar
(
max
)
--
获取需要备份的库名--------------------start
declare
@t
table
(id
int
identity
(
1
,
1
)
primary
key
,name
nvarchar
(
max
))
declare
@sql
nvarchar
(
max
)
set
@sql
=
'
select name from sys.databases where state=0 and name!=
''
tempdb
''
'
+
case
when
@baktype
=
2
then
'
and recovery_model!=3
'
else
''
end
+
case
@type
when
0
then
'
and 1=1
'
when
1
then
'
and database_id<=4
'
when
2
then
'
and database_id>4
'
when
3
then
'
and charindex(
''
,
''
+Name+
''
,
''
,
'''
+
@dbnames
+
'''
)>0
'
when
4
then
'
and charindex(
''
,
''
+Name+
''
,
''
,
'''
+
@dbnames
+
'''
)=0 and database_id>4
'
else
'
1>2
'
end
insert
into
@t
exec
(
@sql
)
--
获取需要备份的库名---------------------end
--
获取需要创建的文件夹------------------start
insert
into
@dirtree_1
exec
(
'
master.dbo.xp_dirtree
'''
+
@bak_path
+
'''
,0,1
'
)
select
@createfolder
=
isnull
(
@createfolder
,
''
)
+
'
exec master.dbo.xp_cmdshell
''
md
'
+
@bak_path
+
''
+
name
+
'''
,no_output
'
+
char
(
13
)
from
@t
as
a
left
join
@dirtree_1
as
b
on
a.name
=
b.subdirectory
and
b.files
=
0
and
depth
=
1
where
b.id
is
null
--
获取需要创建的文件夹-------------------end
--
生成处理过期备份的sql语句-------------start
if
@overdueDay
>
0
begin
insert
into
@dirtree_2
(subdirectory,depth,files)
exec
(
'
master.dbo.xp_dirtree
'''
+
@bak_path
+
'''
,0,1
'
)
if
@baktype
=
0
delete
from
@dirtree_2
where
depth
=
1
or
files
=
0
or
charindex
(
'
_Full_bak_
'
,subdirectory)
=
0
if
@baktype
=
1
delete
from
@dirtree_2
where
depth
=
1
or
files
=
0
or
charindex
(
'
_Diff_bak_
'
,subdirectory)
=
0
if
@baktype
=
2
delete
from
@dirtree_2
where
depth
=
1
or
files
=
0
or
charindex
(
'
_Log_bak_
'
,subdirectory)
=
0
if
exists
(
select
1
from
@dirtree_2
)
delete
from
@dirtree_2
where
isdate
(
left
(
right
(subdirectory,
19
),
8
)
+
'
'
+
substring
(
right
(subdirectory,
20
),
11
,
2
)
+
'
:
'
+
substring
(
right
(subdirectory,
20
),
13
,
2
)
+
'
:
'
+
substring
(
right
(subdirectory,
20
),
15
,
2
)
)
=
0
if
exists
(
select
1
from
@dirtree_2
)
update
@dirtree_2
set
dbname
=
case
when
@baktype
=
0
then
left
(subdirectory,
charindex
(
'
_Full_bak_
'
,subdirectory)
-
1
)
when
@baktype
=
1
then
left
(subdirectory,
charindex
(
'
_Diff_bak_
'
,subdirectory)
-
1
)
when
@baktype
=
2
then
left
(subdirectory,
charindex
(
'
_Log_bak_
'
,subdirectory)
-
1
)
else
''
end
,baktime
=left
(
right
(subdirectory,
19
),
8
)
+
'
'
+
substring
(
right
(subdirectory,
20
),
11
,
2
)
+
'
:
'
+
substring
(
right
(subdirectory,
20
),
13
,
2
)
+
'
:
'
+
substring
(
right
(subdirectory,
20
),
15
,
2
)
from
@dirtree_2
as
a
delete
@dirtree_2
from
@dirtree_2
as
a
left
join
@t
as
b
on
b.name
=
a.dbname
where
b.id
is
null
update
@dirtree_2
set
isLastbak
=
case
when
(
select
max
(baktime)
from
@dirtree_2
where
dbname
=
a.dbname)
=
baktime
then
1
else
0
end
from
@dirtree_2
as
a
select
@delbak
=
isnull
(
@delbak
,
''
)
+
'
exec master.dbo.xp_cmdshell
''
del
'
+
@bak_path
+
''
+
dbname
+
'
/
'
+
subdirectory
+
'''
,no_output
'
+
char
(
13
)
from
@dirtree_2
where
isLastbak
=
0
and
datediff
(
day
,baktime,
getdate
())
>
@overdueDay
end
--
生成处理过期备份的sql语句--------------end
begin
try
print
(
@createfolder
)
--
创建备份所需文件夹
exec
(
@createfolder
)
--
创建备份所需文件夹
end
try
begin
catch
print
'
err:
'
+
ltrim
(error_number())
print
'
err:
'
+
error_message()
return
end
catch
select
@i
=
1
,
@maxid
=
max
(id)
from
@t
while
@i
<=
@maxid
begin
select
@bak_sql
=
''
+
char
(
13
)
+
'
backup
'
+
case
when
@baktype
=
2
then
'
log
'
else
'
database
'
end
+
quotename
(Name)
+
'
to disk=
'''
+
@bak_path
+
Name
+
'
/
'
+
Name
+
case
when
@baktype
=
0
then
'
_Full_bak_
'
when
@baktype
=
1
then
'
_Diff_bak_
'
when
@baktype
=
2
then
'
_Log_bak_
'
else
null
end
+
case
when
@compression
=
1
then
'
compression_
'
else
''
end
+
replace
(
replace
(
replace
(
convert
(
varchar
(
20
),
getdate
(),
120
),
'
-
'
,
''
),
'
'
,
'
_
'
),
'
:
'
,
''
)
+
case
when
@baktype
=
2
then
'
.trn
'
when
@baktype
=
1
then
'
.dif
'
else
'
.bak
'
end
+
''''
+
case
when
@compression
=
1
or
@baktype
=
1
then
'
with
'
else
''
end
+
case
when
@compression
=
1
then
'
compression,
'
else
''
end
+
case
when
@baktype
=
1
then
'
differential,
'
else
''
end
+
case
when
@compression
=
1
or
@baktype
=
1
then
'
noformat
'
else
''
end
from
@t
where
id
=
@i
set
@i
=
@i
+
1
begin
try
print
(
@bak_sql
)
--
循环执行备份
exec
(
@bak_sql
)
--
循环执行备份
end
try
begin
catch
print
'
err:
'
+
ltrim
(error_number())
print
'
err:
'
+
error_message()
end
catch
end
begin
try
print
(
@delbak
)
--
删除超期的备份
exec
(
@delbak
)
--
删除超期的备份
end
try
begin
catch
print
'
err:
'
+
ltrim
(error_number())
print
'
err:
'
+
error_message()
end
catch
--
关闭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
SQL code
/*
调用示例：
--备份系统库(全备)
exec master.dbo.sp_backupdatabase
--备份路径;
@bak_path ='D:/temp/dbbak'
--备份类型为全备,1为差异备,2为日志备份
,@baktype = 0
--设置需要备份的库,0为全部库,1为系统库,2为全部用户库,3为指定库,4为排除指定库;
,@type = 1
--需要备份或排除的数据库，用,隔开，当@type=3或4时生效
,@dbnames =''
--设置过期天数，默认天;
,@overdueDay = 31
--是否采用sql2008的压缩备份,0为否,1为采用压缩
,@compression =0
--备份用户库(全备)--建议一周一次
exec master.dbo.sp_backupdatabase
--备份路径;
@bak_path ='D:/temp/dbbak'
--备份类型为全备,1为差异备,2为日志备份
,@baktype = 0
--设置需要备份的库,0为全部库,1为系统库,2为全部用户库,3为指定库,4为排除指定库;
,@type = 3
--需要备份或排除的数据库，用,隔开，当@type=3或4时生效
,@dbnames ='db1,db2'
--设置过期天数，默认天;
,@overdueDay = 15
--是否采用sql2008的压缩备份,0为否,1为采用压缩
,@compression =0
--备份用户库(差异备)--建议一天一次
exec master.dbo.sp_backupdatabase
--备份路径;
@bak_path ='D:/temp/dbbak'
--备份类型为全备,1为差异备,2为日志备份
,@baktype = 1
--设置需要备份的库,0为全部库,1为系统库,2为全部用户库,3为指定库,4为排除指定库;
,@type = 3
--需要备份或排除的数据库，用,隔开，当@type=3或4时生效
,@dbnames ='db1,db2'
--设置过期天数，默认天;
,@overdueDay = 15
--是否采用sql2008的压缩备份,0为否,1为采用压缩
,@compression =0
--备份用户库(日志备)--建议1小时一次
exec master.dbo.sp_backupdatabase
--备份路径;
@bak_path ='D:/temp/dbbak'
--备份类型为全备,1为差异备,2为日志备份
,@baktype = 2
--设置需要备份的库,0为全部库,1为系统库,2为全部用户库,3为指定库,4为排除指定库;
,@type = 3
--需要备份或排除的数据库，用,隔开，当@type=3或4时生效
,@dbnames ='db1,db2'
--设置过期天数，默认天;
,@overdueDay = 15
--是否采用sql2008的压缩备份,0为否,1为采用压缩
,@compression =0
*/本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
