内容:
/*
--备份数据库--
*/
/* --调用示例
--备份当前数据库
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
-- --------------------------------------------------------------------
/* --恢复数据库-- */
/* --调用示例
--完整恢复数据库
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
/* --创建作业 -- */
/* --调用示例
--每月执行的作业
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
/* --得到数据库的文件目录
@dbname 指定要取得目录的数据库名
如果指定的数据不存在,返回安装SQL时设置的默认数据目录
如果指定NULL,则返回默认的SQL备份目录名
--邹建 2003.10-- */
/* --调用示例
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
-- -----------------------------------------------------------------------------
-- /*--得到数据库的文件目录:f_getdbpath
/* --调用示例
select 数据库文件目录=dbo.f_getdbpath('tempdb')
,[默认SQL SERVER数据目录]=dbo.f_getdbpath('')
,[默认SQL SERVER备份目录]=dbo.f_getdbpath(null)
-- */
-- -----------------------------------------------------------------------------
-- /*--备份数据库:p_backupdb
-- /*--调用示例
-- 备份当前数据库
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 '
-- */
/* --参数说明
@dbname sysname='', --要备份的数据库名称,不指定则备份当前数据库
@bkpath nvarchar(260)='', --备份文件的存放目录,不指定则使用SQL默认的备份目录
@bkfname nvarchar(260)='', --备份文件名,文件名中可以用\DBNAME\代表数据库名,\DATE\代表日期,\TIME\代表时间
@bktype nvarchar(10)='DB', --备份类型:'DB'备份数据库,'DF' 差异备份,'LOG' 日志备份
@appendfile bit=1 --追加/覆盖备份文件
-- */
-- -----------------------------------------------------------------------------
-- -----------------------------------------------------------------------------
-- /*--恢复数据库:p_RestoreDb
-- /*--调用示例
-- 完整恢复数据库
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 '
-- */
/* --参数说明
@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时有效
-- */
-- -----------------------------------------------------------------------------
-- -----------------------------------------------------------------------------
-- /*--创建作业:p_createjob
-- /*--调用示例
-- 每月执行的作业
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
-- */
/* --参数说明:
@jobname varchar(100), --作业名称
@sql varchar(8000), --要执行的命令
@dbname sysname='', --默认为当前的数据库名
@freqtype varchar(6)='day', --时间周期,month 月,week 周,day 日
@fsinterval int=1, --相对于每日的重复次数
@time int=170000 --开始执行时间,对于重复执行的作业,将从0点到23:59分
*/--
/* --应用案例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
相对于直接将数据库文件备份到其它机器上,需要利用ftp协议
/* --调用示例
--备份当前数据库
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
-- --------------------------------------------------------------------
/* --恢复数据库-- */
/* --调用示例
--完整恢复数据库
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
/* --创建作业 -- */
/* --调用示例
--每月执行的作业
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
/* --得到数据库的文件目录
@dbname 指定要取得目录的数据库名
如果指定的数据不存在,返回安装SQL时设置的默认数据目录
如果指定NULL,则返回默认的SQL备份目录名
--邹建 2003.10-- */
/* --调用示例
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
-- -----------------------------------------------------------------------------
-- /*--得到数据库的文件目录:f_getdbpath
/* --调用示例
select 数据库文件目录=dbo.f_getdbpath('tempdb')
,[默认SQL SERVER数据目录]=dbo.f_getdbpath('')
,[默认SQL SERVER备份目录]=dbo.f_getdbpath(null)
-- */
-- -----------------------------------------------------------------------------
-- /*--备份数据库:p_backupdb
-- /*--调用示例
-- 备份当前数据库
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 '
-- */
/* --参数说明
@dbname sysname='', --要备份的数据库名称,不指定则备份当前数据库
@bkpath nvarchar(260)='', --备份文件的存放目录,不指定则使用SQL默认的备份目录
@bkfname nvarchar(260)='', --备份文件名,文件名中可以用\DBNAME\代表数据库名,\DATE\代表日期,\TIME\代表时间
@bktype nvarchar(10)='DB', --备份类型:'DB'备份数据库,'DF' 差异备份,'LOG' 日志备份
@appendfile bit=1 --追加/覆盖备份文件
-- */
-- -----------------------------------------------------------------------------
-- -----------------------------------------------------------------------------
-- /*--恢复数据库:p_RestoreDb
-- /*--调用示例
-- 完整恢复数据库
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 '
-- */
/* --参数说明
@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时有效
-- */
-- -----------------------------------------------------------------------------
-- -----------------------------------------------------------------------------
-- /*--创建作业:p_createjob
-- /*--调用示例
-- 每月执行的作业
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
-- */
/* --参数说明:
@jobname varchar(100), --作业名称
@sql varchar(8000), --要执行的命令
@dbname sysname='', --默认为当前的数据库名
@freqtype varchar(6)='day', --时间周期,month 月,week 周,day 日
@fsinterval int=1, --相对于每日的重复次数
@time int=170000 --开始执行时间,对于重复执行的作业,将从0点到23:59分
*/--
/* --应用案例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
有一个例子,转自:http://tech.it168.com/db/s/2007-03-22/200703221542886.shtml
以下脚本执行 SQL Server 中某一数据库全备份,并将备份文件传至另一机器。备份文件命名规则:数据库名_时间戳.BAK。
执行环境: windows 2003 server SQL Server 2000
@ECHO
off
REM Backup Database Script Log Files
SET dbbak_dir = e:testdbbak
if not exist % dbbak_dir % mkdir % dbbak_dir %
REM Backup Database Name
SET B_Database_Name = db1
SET B_DATE =% date: ~ 0 , 4 %% date: ~ 5 , 2 %% date: ~ 8 , 2 %
ECHO use master ; >% dbbak_dir % FULL_backup.sql
ECHO go >>% dbbak_dir % FULL_backup.sql
rem ECHO SP_ADDUMPDEVICE ' DISK ' , >>% dbbak_dir % FULL_backup.sql
rem ECHO ' %B_Database_Name%_%B_DATE%_FULL ' , >>% dbbak_dir % FULL_backup.sql
rem ECHO ' %dbbak_dir%%B_Database_Name%_%B_DATE%_FULL.bak ' ; >>% dbbak_dir % FULL_backup.sql
rem ECHO go >>% dbbak_dir % FULL_backup.sql
ECHO BACKUP DATABASE % B_Database_Name % >>% dbbak_dir % FULL_backup.sql
ECHO TO DISK = ' %dbbak_dir%%B_Database_Name%_%B_DATE%_FULL.bak ' >>% dbbak_dir % FULL_backup.sql
ECHO WITH INIT , NOUNLOAD ; >>% dbbak_dir % FULL_backup.sql
ECHO go >>% dbbak_dir % FULL_backup.sql
ECHO .
REM osql.exe path
SET OSQL_PATH = C:Program FilesMicrosoft SQL Server80ToolsBinn
REM Database user name and password
SET DBUser = sa
SET DBpassword = passwd
ECHO BACKUP DATABASE
" % OSQL_PATH % osql.exe" - U % DBUser % - P % DBpassword % - i % dbbak_dir % FULL_backup.sql - o % dbbak_dir %% B_Database_Name % _ % B_DATE % _ FULL . log
REM FTP info
set FTP_User = user1
set FTP_PW = passwd
set FTP_IP = 10.1 . 9.1
set FTP_PORT = 21
set FTP_DIR =/ home / dbbak
SET NCFTP_PATH = D:Program FilesNcFTP
" % NCFTP_PATH % ncftpput.exe" - u % FTP_User % - p % FTP_PW % - P % FTP_PORT % % FTP_IP % % FTP_DIR % % dbbak_dir %% B_Database_Name % _ % B_DATE % _ FULL . *
REM Delete old backup files
FORFILES / P % dbbak_dir % / D - 7 - M % B_Database_Name %* .bak / C "cmd / c del @file "
FORFILES / P % dbbak_dir % / D - 7 - M % B_Database_Name %* . log / C "cmd / c del @file "
REM Delete Temp files
DEL / Q % dbbak_dir % FULL_backup.sql
REM Backup Database Script Log Files
SET dbbak_dir = e:testdbbak
if not exist % dbbak_dir % mkdir % dbbak_dir %
REM Backup Database Name
SET B_Database_Name = db1
SET B_DATE =% date: ~ 0 , 4 %% date: ~ 5 , 2 %% date: ~ 8 , 2 %
ECHO use master ; >% dbbak_dir % FULL_backup.sql
ECHO go >>% dbbak_dir % FULL_backup.sql
rem ECHO SP_ADDUMPDEVICE ' DISK ' , >>% dbbak_dir % FULL_backup.sql
rem ECHO ' %B_Database_Name%_%B_DATE%_FULL ' , >>% dbbak_dir % FULL_backup.sql
rem ECHO ' %dbbak_dir%%B_Database_Name%_%B_DATE%_FULL.bak ' ; >>% dbbak_dir % FULL_backup.sql
rem ECHO go >>% dbbak_dir % FULL_backup.sql
ECHO BACKUP DATABASE % B_Database_Name % >>% dbbak_dir % FULL_backup.sql
ECHO TO DISK = ' %dbbak_dir%%B_Database_Name%_%B_DATE%_FULL.bak ' >>% dbbak_dir % FULL_backup.sql
ECHO WITH INIT , NOUNLOAD ; >>% dbbak_dir % FULL_backup.sql
ECHO go >>% dbbak_dir % FULL_backup.sql
ECHO .
REM osql.exe path
SET OSQL_PATH = C:Program FilesMicrosoft SQL Server80ToolsBinn
REM Database user name and password
SET DBUser = sa
SET DBpassword = passwd
ECHO BACKUP DATABASE
" % OSQL_PATH % osql.exe" - U % DBUser % - P % DBpassword % - i % dbbak_dir % FULL_backup.sql - o % dbbak_dir %% B_Database_Name % _ % B_DATE % _ FULL . log
REM FTP info
set FTP_User = user1
set FTP_PW = passwd
set FTP_IP = 10.1 . 9.1
set FTP_PORT = 21
set FTP_DIR =/ home / dbbak
SET NCFTP_PATH = D:Program FilesNcFTP
" % NCFTP_PATH % ncftpput.exe" - u % FTP_User % - p % FTP_PW % - P % FTP_PORT % % FTP_IP % % FTP_DIR % % dbbak_dir %% B_Database_Name % _ % B_DATE % _ FULL . *
REM Delete old backup files
FORFILES / P % dbbak_dir % / D - 7 - M % B_Database_Name %* .bak / C "cmd / c del @file "
FORFILES / P % dbbak_dir % / D - 7 - M % B_Database_Name %* . log / C "cmd / c del @file "
REM Delete Temp files
DEL / Q % dbbak_dir % FULL_backup.sql
说明:
1. 为避免 Windows 自带 FTP Client 的 PORT、PASV问题,使用 ncftp client
2. 保留最新 7 天的备份,使用 Resource kit 里的命令 FORFILES 删除老备份文件
3. ftp 目的机器是一个 linux 系统,用下面的 shell 脚本删除老备份文件
#!/bin/sh
cd /home/dbbak/
if [[ -s mm_db_`date +%Y%m%d`*.BAK ]]
then
#echo "not 0"
find . -mtime +7 | xargs rm
fi