- 技术人生
- 备份数据库的通用存储过程
if
exists
(
select
*
from
dbo.sysobjects
where
id
=
object_id
(N
'
[dbo].[p_backupdb]
'
)
and
OBJECTPROPERTY
(id, N
'
IsProcedure
'
)
=
1
)
drop
procedure
[
dbo
]
.
[
p_backupdb
]
GO
/**/
/*--备份数据库的通用存储过程/*--调用示例--备份当前数据库exec p_backupdb @bkpath='c:',@bkfname='DBNAME_DATE_db.bak'--差异备份当前数据库exec p_backupdb @bkpath='c:',@bkfname='db_DATE_df.bak',@bktype='DF'--备份当前数据库日志exec p_backupdb @bkpath='c:',@bkfname='db_DATE_log.bak',@bktype='LOG'--*/
create
proc
p_backupdb
@dbname
sysname
=
''
,
--
要备份的数据库名称,不指定则备份当前数据库
@bkpath
nvarchar
(
260
)
=
''
,
--
备份文件的存放目录,不指定则使用SQL默认的备份目录
@bkfname
nvarchar
(
260
)
=
''
,
--
备份文件名,文件名中可以用DBNAME代表数据库名,DATE代表日期,TIME代表时间
@bktype
nvarchar
(
10
)
=
'
DB
'
,
--
备份类型:'DB'备份数据库,'DF' 差异备份,'LOG' 日志备份
@appendfile
bit
=
1
,
--
追加/覆盖备份文件
@password
nvarchar
(
20
)
=
''
--
为备份文件设置的密码(仅sql2000支持),设置后,恢复时必须提供此密码
as
declare
@sql
varchar
(
8000
)
if
isnull
(
@dbname
,
''
)
=
''
set
@dbname
=
db_name
()
if
isnull
(
@bkpath
,
''
)
=
''
begin
select
@bkpath
=
rtrim
(
reverse
(filename))
from
master..sysfiles
where
name
=
'
master
'
select
@bkpath
=
substring
(
@bkpath
,
charindex
(
'
'
,
@bkpath
)
+
1
,
4000
) ,
@bkpath
=
reverse
(
substring
(
@bkpath
,
charindex
(
'
'
,
@bkpath
),
4000
))
+
'
BACKUP
'
end
if
isnull
(
@bkfname
,
''
)
=
''
set
@bkfname
=
'
DBNAME_DATE_TIME.BAK
'
set
@bkfname
=
replace
(
replace
(
replace
(
@bkfname
,
'
DBNAME
'
,
@dbname
) ,
'
DATE
'
,
convert
(
varchar
,
getdate
(),
112
)) ,
'
TIME
'
,
replace
(
convert
(
varchar
,
getdate
(),
108
),
'
:
'
,
''
))
set
@sql
=
'
backup
'
+
case
@bktype
when
'
LOG
'
then
'
log
'
else
'
database
'
end
+
@dbname
+
'
to disk=
'''
+
@bkpath
+
@bkfname
+
'''
with
'
+
case
@bktype
when
'
DF
'
then
'
DIFFERENTIAL,
'
else
''
end
+
case
@appendfile
when
1
then
'
NOINIT
'
else
'
INIT
'
end
+
case
isnull
(
@password
,
''
)
when
''
then
''
else
'
,PASSWORD=
'''
+
@password
+
''''
end
exec
(
@sql
)
go
