—
利用t-sql语句,实现数据库的备份与还原的功能
—
—
体现了sql server中的四个知识点：
—
—
1． 获取sql server服务器上的默认目录
—
—
2． 备份sql语句的使用
—
—
3． 恢复sql语句的使用，同时考虑了强制恢复时关闭其他用户进程的处理
—
—
4． 作业创建sql语句的使用
/*
1.–得到数据库的文件目录
@dbname 指定要取得目录的数据库名
如果指定的数据不存在,返回安装sql时设置的默认数据目录
如果指定null,则返回默认的sql备份目录名
*/
/*
–调用示例
select 数据库文件目录=dbo.f_getdbpath(‘tempdb’)
,[默认sql server数据目录]=dbo.f_getdbpath(”)
,[默认sql server备份目录]=dbo.f_getdbpath(null)
—
*/
if
exists (
select
*
from dbo.sysobjects
where id
=
object_id(n
‘
[dbo].[f_getdbpath]
‘)
and xtype
in (n
‘
fn
‘, n
‘
if
‘, n
‘
tf
‘))
drop
function
[
dbo
].
[
f_getdbpath
]
go
create
function f_getdbpath(
@dbname sysname)
returns
nvarchar(
260)
as
begin
declare
@re
nvarchar(
260)
if
@dbname
is
null
or
db_id(
@dbname)
is
null
select
@re
=
rtrim(
reverse(filename))
from master..sysdatabases
where name
=
‘
master
‘
else
select
@re
=
rtrim(
reverse(filename))
from master..sysdatabases
where name
=
@dbname
if
@dbname
is
null
set
@re
=
reverse(
substring(
@re,
charindex(
‘
\
‘,
@re)
+
5,
260))
+
‘
backup
‘
else
set
@re
=
reverse(
substring(
@re,
charindex(
‘
\
‘,
@re),
260))
return(
@re)
end
go
/*
2.–备份数据库
*/
/*
–调用示例
–备份当前数据库
exec p_backupdb @bkpath=’c:\’,@bkfname=’db_\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’
—
*/
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
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
—
追加/覆盖备份文件
as
declare
@sql
varchar(
8000)
if
isnull(
@dbname,
”)
=
”
set
@dbname
=
db_name()
if
isnull(
@bkpath,
”)
=
”
set
@bkpath
=dbo.f_getdbpath(
null)
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
print
@sql
exec(
@sql)
go
/*
3.–恢复数据库
*/
/*
–调用示例
–完整恢复数据库
exec p_restoredb @bkfile=’c:\db_20031015_db.bak’,@dbname=’db’
–差异备份恢复
exec p_restoredb @bkfile=’c:\db_20031015_db.bak’,@dbname=’db’,@retype=’dbnor’
exec p_backupdb @bkfile=’c:\db_20031015_df.bak’,@dbname=’db’,@retype=’df’
–日志备份恢复
exec p_restoredb @bkfile=’c:\db_20031015_db.bak’,@dbname=’db’,@retype=’dbnor’
exec p_backupdb @bkfile=’c:\db_20031015_log.bak’,@dbname=’db’,@retype=’log’
—
*/
if
exists (
select
*
from dbo.sysobjects
where id
=
object_id(n
‘
[dbo].[p_restoredb]
‘)
and
objectproperty(id, n
‘
isprocedure
‘)
=
1)
drop
procedure
[
dbo
].
[
p_restoredb
]
go
create
proc p_restoredb
@bkfile
nvarchar(
1000),
—
定义要恢复的备份文件名
@dbname sysname
=
”,
—
定义恢复后的数据库名,默认为备份的文件名
@dbpath
nvarchar(
260)
=
”,
—
恢复后的数据库存放目录,不指定则为sql的默认数据目录
@retype
nvarchar(
10)
=
‘
db
‘,
—
恢复类型:’db’完事恢复数据库,’dbnor’ 为差异恢复,日志恢复进行完整恢复,’df’ 差异备份的恢复,’log’ 日志恢复
@filenumber
int
=
1,
—
恢复的文件号
@overexist
bit
=
1,
—
是否覆盖已经存在的数据库,仅@retype为
@killuser
bit
=
1
—
是否关闭用户使用进程,仅@overexist=1时有效
as
declare
@sql
varchar(
8000)
—
得到恢复后的数据库名
if
isnull(
@dbname,
”)
=
”
select
@sql
=
reverse(
@bkfile)
,
@sql
=
case
when
charindex(
‘
.
‘,
@sql)
=
0
then
@sql
else
substring(
@sql,
charindex(
‘
.
‘,
@sql)
+
1,
1000)
end
,
@sql
=
case
when
charindex(
‘
\
‘,
@sql)
=
0
then
@sql
else
left(
@sql,
charindex(
‘
\
‘,
@sql)
–
1)
end
,
@dbname
=
reverse(
@sql)
—
得到恢复后的数据库存放目录
if
isnull(
@dbpath,
”)
=
”
set
@dbpath
=dbo.f_getdbpath(
”)
—
生成数据库恢复语句
set
@sql
=
‘
restore
‘
+
case
@retype
when
‘
log
‘
then
‘
log
‘
else
‘
database
‘
end
+
@dbname
+
‘
from disk=
”’
+
@bkfile
+
””
+
‘
with file=
‘
+
cast(
@filenumber
as
varchar)
+
case
when
@overexist
=
1
and
@retype
in(
‘
db
‘,
‘
dbnor
‘)
then
‘
,replace
‘
else
”
end
+
case
@retype
when
‘
dbnor
‘
then
‘
,norecovery
‘
else
‘
,recovery
‘
end
print
@sql
—
添加移动逻辑文件的处理
if
@retype
=
‘
db
‘
or
@retype
=
‘
dbnor
‘
begin
—
从备份文件中获取逻辑文件名
declare
@lfn
nvarchar(
128),
@tp
char(
1),
@i
int
—
创建临时表,保存获取的信息
create
table #tb(ln
nvarchar(
128),pn
nvarchar(
260),tp
char(
1),fgn
nvarchar(
128),sz numeric(
20,
0),msz numeric(
20,
0))
—
从备份文件中获取信息
insert
into #tb
exec(
‘
restore filelistonly from disk=
”’
+
@bkfile
+
””)
declare #f
cursor
for
select ln,tp
from #tb
open #f
fetch
next
from #f
into
@lfn,
@tp
set
@i
=
0
while
@@fetch_status
=
0
begin
select
@sql
=
@sql
+
‘
,move
”’
+
@lfn
+
”’
to
”’
+
@dbpath
+
@dbname
+
cast(
@i
as
varchar)
+
case
@tp
when
‘
d
‘
then
‘
.mdf
”’
else
‘
.ldf
”’
end
,
@i
=
@i
+
1
fetch
next
from #f
into
@lfn,
@tp
end
close #f
deallocate #f
end
—
关闭用户进程处理
if
@overexist
=
1
and
@killuser
=
1
begin
declare
@spid
varchar(
20)
declare #spid
cursor
for
select spid
=
cast(spid
as
varchar(
20))
from master..sysprocesses
where dbid
=
db_id(
@dbname)
open #spid
fetch
next
from #spid
into
@spid
while
@@fetch_status
=
0
begin
exec(
‘
kill
‘
+
@spid)
fetch
next
from #spid
into
@spid
end
close #spid
deallocate #spid
end
—
恢复数据库
exec(
@sql)
go
/*
4.–创建作业
*/
/*
–调用示例
–每月执行的作业
exec p_createjob @jobname=’mm’,@sql=’select * from syscolumns’,@freqtype=’month’
–每周执行的作业
exec p_createjob @jobname=’ww’,@sql=’select * from syscolumns’,@freqtype=’week’
–每日执行的作业
exec p_createjob @jobname=’a’,@sql=’select * from syscolumns’
–每日执行的作业,每天隔4小时重复的作业
exec p_createjob @jobname=’b’,@sql=’select * from syscolumns’,@fsinterval=4
—
*/
if
exists (
select
*
from dbo.sysobjects
where id
=
object_id(n
‘
[dbo].[p_createjob]
‘)
and
objectproperty(id, n
‘
isprocedure
‘)
=
1)
drop
procedure
[
dbo
].
[
p_createjob
]
go
create
proc p_createjob
@jobname
varchar(
100),
—
作业名称
@sql
varchar(
8000),
—
要执行的命令
@dbname sysname
=
”,
—
默认为当前的数据库名
@freqtype
varchar(
6)
=
‘
day
‘,
—
时间周期,month 月,week 周,day 日
@fsinterval
int
=
1,
—
相对于每日的重复次数
@time
int
=
170000
—
开始执行时间,对于重复执行的作业,将从0点到23:59分
as
if
isnull(
@dbname,
”)
=
”
set
@dbname
=
db_name()
—
创建作业
exec msdb..sp_add_job
@job_name
=
@jobname
—
创建作业步骤
exec msdb..sp_add_jobstep
@job_name
=
@jobname,
@step_name
=
‘
数据处理
‘,
@subsystem
=
‘
tsql
‘,
@database_name
=
@dbname,
@command
=
@sql,
@retry_attempts
=
5,
—
重试次数
@retry_interval
=
5
—
重试间隔
—
创建调度
declare
@ftype
int,
@fstype
int,
@ffactor
int
select
@ftype
=
case
@freqtype
when
‘
day
‘
then
4
when
‘
week
‘
then
8
when
‘
month
‘
then
16
end
,
@fstype
=
case
@fsinterval
when
1
then
0
else
8
end
if
@fsinterval
<>
1
set
@time
=
0
set
@ffactor
=
case
@freqtype
when
‘
day
‘
then
0
else
1
end
exec msdb..sp_add_jobschedule
@job_name
=
@jobname,
@name
=
‘
时间安排
‘,
@freq_type
=
@ftype ,
—
每天,8 每周,16 每月
@freq_interval
=
1,
—
重复执行次数
@freq_subday_type
=
@fstype,
—
是否重复执行
@freq_subday_interval
=
@fsinterval,
—
重复周期
@freq_recurrence_factor
=
@ffactor,
@active_start_time
=
@time
—
下午17:00:00分执行
go
/*
–应用案例–备份方案:
完整备份（每个星期天一次）+差异备份（每天备份一次）+日志备份（每2小时备份一次）
调用上面的存储过程来实现
—
*/
declare
@sql
varchar(
8000)
—
完整备份（每个星期天一次）
set
@sql
=
‘
exec p_backupdb @dbname=
”
要备份的数据库名
”’
exec p_createjob
@jobname
=
‘
每周备份
‘,
@sql,
@freqtype
=
‘
week
‘
—
差异备份（每天备份一次）
set
@sql
=
‘
exec p_backupdb @dbname=
”
要备份的数据库名
”
,@bktype=
‘df
”
exec p_createjob
@jobname
=
‘
每天差异备份
‘,
@sql,
@freqtype
=
‘
day
‘
—
日志备份（每2小时备份一次）
set
@sql
=
‘
exec p_backupdb @dbname=
”
要备份的数据库名
”
,@bktype=
‘
log
”
exec p_createjob
@jobname
=
‘
每2小时日志备份
‘,
@sql,
@freqtype
=
‘
day
‘,
@fsinterval
=
2
/*
–应用案例2
生产数据核心库：produce
备份方案如下：
1.设置三个作业,分别对produce库进行每日备份,每周备份,每月备份
2.新建三个新库,分别命名为:每日备份,每周备份,每月备份
3.建立三个作业，分别把三个备份库还原到以上的三个新库。
目的:当用户在produce库中有任何的数据丢失时,均可以从上面的三个备份库中导入相应的table数据。
—
*/
declare
@sql
varchar(
8000)
—
1.建立每月备份和生成月备份数据库的作业,每月每1天下午16:40分进行:
set
@sql
=
‘
declare @path nvarchar(260),@fname nvarchar(100)
set @fname=
”
produce_
”
+convert(varchar(10),getdate(),112)+
”
_m.bak
”
set @path=dbo.f_getdbpath(null)+@fname
–备份
exec p_backupdb @dbname=
”
produce
”
,@bkfname=@fname
–根据备份生成每月新库
exec p_restoredb @bkfile=@path,@dbname=
”
produce_月
”
–为周数据库恢复准备基础数据库
exec p_restoredb @bkfile=@path,@dbname=
”
produce_周
”
,@retype=
”
dbnor
”
–为日数据库恢复准备基础数据库
exec p_restoredb @bkfile=@path,@dbname=
”
produce_日
”
,@retype=
”
dbnor
”
‘
exec p_createjob
@jobname
=
‘
每月备份
‘,
@sql,
@freqtype
=
‘
month
‘,
@time
=
164000
—
2.建立每周差异备份和生成周备份数据库的作业,每周日下午17:00分进行:
set
@sql
=
‘
declare @path nvarchar(260),@fname nvarchar(100)
set @fname=
”
produce_
”
+convert(varchar(10),getdate(),112)+
”
_w.bak
”
set @path=dbo.f_getdbpath(null)+@fname
–差异备份
exec p_backupdb @dbname=
”
produce
”
,@bkfname=@fname,@bktype=
”
df
”
–差异恢复周数据库
exec p_backupdb @bkfile=@path,@dbname=
”
produce_周
”
,@retype=
”
df
”
‘
exec p_createjob
@jobname
=
‘
每周差异备份
‘,
@sql,
@freqtype
=
‘
week
‘,
@time
=
170000
—
3.建立每日日志备份和生成日备份数据库的作业,每周日下午17:15分进行:
set
@sql
=
‘
declare @path nvarchar(260),@fname nvarchar(100)
set @fname=
”
produce_
”
+convert(varchar(10),getdate(),112)+
”
_l.bak
”
set @path=dbo.f_getdbpath(null)+@fname
–日志备份
exec p_backupdb @dbname=
”
produce
”
,@bkfname=@fname,@bktype=
”
log
”
–日志恢复日数据库
exec p_backupdb @bkfile=@path,@dbname=
”
produce_日
”
,@retype=
”
log
”
‘
exec p_createjob
@jobname
=
‘
每周差异备份
‘,
@sql,
@freqtype
=
‘
day
‘,
@time
=
171500