可以每天备份日志,然后再完全备份,并且以每周7天进行 一个循环.你可以恢复到最近一周的任何一个时间点.建立作业(job),在作业中指定数据库名称,备份路径.exec master..sp_bk_log_week(mydb,'d:\db_bak') exec master..sp_bk_db_week(mydb,'d:\db_bak')在master中运行脚本,建立存储过程: if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_BK_DB_WEEK]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[SP_BK_DB_WEEK] GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_BK_LOG_WEEK]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[SP_BK_LOG_WEEK] GOSET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GOCREATE PROCEDURE SP_BK_DB_WEEK ---每周备份 ( @db_name varchar(50), --数据库名 @path varchar(200) --备份路径
) ASdeclare @week int declare @bak_name varchar(50) declare @str varchar(5000)select @week= DATEPART ( weekday , getdate() ) - 1if @week=0 select @week = 7select @bak_name=@db_name + '_bak_7_'+convert(varchar(10),@week)select @str='backup database ' + @db_name + ' to disk ='''+@path +'\' + @bak_name +''' with init'exec(@str)GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GOCREATE PROCEDURE SP_BK_LOG_WEEK ---每周备份 ( @db_name varchar(50), --数据库名 @path varchar(200) --备份路径
) ASdeclare @week int declare @bak_name varchar(50) declare @str varchar(5000)select @week=DATEPART ( weekday , getdate() ) -1if @week=0 select @week = 7select @bak_name=@db_name + '_log_7_'+convert(varchar(10),@week)select @str='backup log ' + @db_name + ' to disk ='''+@path +'\' + @bak_name +''' with init'--select @strexec(@str)GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
一个循环.你可以恢复到最近一周的任何一个时间点.建立作业(job),在作业中指定数据库名称,备份路径.exec master..sp_bk_log_week(mydb,'d:\db_bak')
exec master..sp_bk_db_week(mydb,'d:\db_bak')在master中运行脚本,建立存储过程:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_BK_DB_WEEK]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_BK_DB_WEEK]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_BK_LOG_WEEK]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_BK_LOG_WEEK]
GOSET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GOCREATE PROCEDURE SP_BK_DB_WEEK ---每周备份
(
@db_name varchar(50), --数据库名
@path varchar(200) --备份路径
)
ASdeclare @week int
declare @bak_name varchar(50)
declare @str varchar(5000)select @week= DATEPART ( weekday , getdate() ) - 1if @week=0
select @week = 7select @bak_name=@db_name + '_bak_7_'+convert(varchar(10),@week)select @str='backup database ' + @db_name + ' to disk ='''+@path +'\' + @bak_name +''' with init'exec(@str)GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GOCREATE PROCEDURE SP_BK_LOG_WEEK ---每周备份
(
@db_name varchar(50), --数据库名
@path varchar(200) --备份路径
)
ASdeclare @week int
declare @bak_name varchar(50)
declare @str varchar(5000)select @week=DATEPART ( weekday , getdate() ) -1if @week=0
select @week = 7select @bak_name=@db_name + '_log_7_'+convert(varchar(10),@week)select @str='backup log ' + @db_name + ' to disk ='''+@path +'\' + @bak_name +''' with init'--select @strexec(@str)GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO