刚写了个数据库自动备份的东西 给你参考下吧 把相应的存储过程建个作业 在作业里调用就可以了 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[backup_db_LOG]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[backup_db_LOG] GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[backup_db_diff]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[backup_db_diff] GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[backup_db_full]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[backup_db_full] GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[check_db]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[check_db] GOSET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO--日志备份 CREATE proc backup_db_LOG as begin --得到目录信息 create table #t (a int,b int,c int) insert into #t(a,b,c) exec master..xp_fileexist 'E:\back_log\log' declare @b int declare @c int select @b=b,@c=c from #t drop table #t --判断目录是否存在 如不存在 创建该目录 if @b=0 or @c=0 exec master..xp_cmdshell 'md E:\back_log\log' exec master.dbo.xp_sqlmaint N' -Rpt "E:\back_log\log\test_LOG.log" -D "test" -BkUpLog "E:\back_log" -BkUpMedia DISK -BkExt "TRN" -DelBkUps 2hours' DBCC SHRINKFILE (test_Log,1) endGO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO --差异备份 CREATE proc backup_db_diff as begin --得到目录信息 create table #t (a int,b int,c int) insert into #t(a,b,c) exec master..xp_fileexist 'E:\back_diff\log' declare @b int declare @c int select @b=b,@c=c from #t drop table #t --判断目录是否存在 如不存在 创建该目录 if @b=0 or @c=0 exec master..xp_cmdshell 'md E:\back_diff\log'
declare @sql nvarchar(100) backup database test to disk='E:\back_diff\backup_test.diff' with DIFFERENTIAL,init if @@error<>0 begin set @sql='echo '+convert(varchar(21),getdate())+':数据库差异备份失败>>E:\back_diff\log\back_diff.log' exec master..xp_cmdshell @sql end else begin set @sql='echo '+convert(varchar(21),getdate())+':数据库差异备份完成>>E:\back_diff\log\back_diff.log' exec master..xp_cmdshell @sql DBCC SHRINKFILE (test_Log,1) end end GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO--完全备份 CREATE proc backup_db_full as begin --得到目录信息 create table #t (a int,b int,c int) insert into #t(a,b,c) exec master..xp_fileexist 'E:\back_full\log' declare @b int declare @c int select @b=b,@c=c from #t drop table #t --判断目录是否存在 如不存在 创建该目录 if @b=0 or @c=0 exec master..xp_cmdshell 'md E:\back_full\log' exec master.dbo.xp_sqlmaint N' -Rpt "E:\back_full\log\test_full.log" -D "test" -BkUpDB "E:\back_full" -BkUpMedia DISK -BkExt "bak" -DelBkUps 1days' DBCC SHRINKFILE (test_Log,1) endGO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO--数据库检测 CREATE proc check_db as begin --得到目录信息 create table #t (a int,b int,c int) insert into #t(a,b,c) exec master..xp_fileexist 'E:\check_db_rpt' declare @b int select @b=b from #t drop table #t --判断目录是否存在 如不存在 创建该目录 if @b=0 exec master..xp_cmdshell 'md E:\check_db_rpt' exec master.dbo.xp_sqlmaint N'-CkDB -CkAl -CkCat -Rpt "E:\check_db_rpt\check_db.log" -D "test"' end GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
1) 建个作业, 用Script
2) 用Maintainence Plan
请先确认SQLSERVER AGENT是否开启?
给你参考下吧
把相应的存储过程建个作业 在作业里调用就可以了 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[backup_db_LOG]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[backup_db_LOG]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[backup_db_diff]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[backup_db_diff]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[backup_db_full]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[backup_db_full]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[check_db]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[check_db]
GOSET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO--日志备份
CREATE proc backup_db_LOG
as
begin
--得到目录信息
create table #t (a int,b int,c int)
insert into #t(a,b,c)
exec master..xp_fileexist 'E:\back_log\log'
declare @b int
declare @c int
select @b=b,@c=c from #t
drop table #t
--判断目录是否存在 如不存在 创建该目录
if @b=0 or @c=0
exec master..xp_cmdshell 'md E:\back_log\log' exec master.dbo.xp_sqlmaint N' -Rpt "E:\back_log\log\test_LOG.log" -D "test" -BkUpLog "E:\back_log" -BkUpMedia DISK -BkExt "TRN" -DelBkUps 2hours'
DBCC SHRINKFILE (test_Log,1)
endGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
--差异备份
CREATE proc backup_db_diff
as
begin
--得到目录信息
create table #t (a int,b int,c int)
insert into #t(a,b,c)
exec master..xp_fileexist 'E:\back_diff\log'
declare @b int
declare @c int
select @b=b,@c=c from #t
drop table #t
--判断目录是否存在 如不存在 创建该目录
if @b=0 or @c=0
exec master..xp_cmdshell 'md E:\back_diff\log'
declare @sql nvarchar(100)
backup database test to disk='E:\back_diff\backup_test.diff' with DIFFERENTIAL,init
if @@error<>0
begin
set @sql='echo '+convert(varchar(21),getdate())+':数据库差异备份失败>>E:\back_diff\log\back_diff.log'
exec master..xp_cmdshell @sql
end
else
begin
set @sql='echo '+convert(varchar(21),getdate())+':数据库差异备份完成>>E:\back_diff\log\back_diff.log'
exec master..xp_cmdshell @sql
DBCC SHRINKFILE (test_Log,1)
end
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO--完全备份
CREATE proc backup_db_full
as
begin
--得到目录信息
create table #t (a int,b int,c int)
insert into #t(a,b,c)
exec master..xp_fileexist 'E:\back_full\log'
declare @b int
declare @c int
select @b=b,@c=c from #t
drop table #t
--判断目录是否存在 如不存在 创建该目录
if @b=0 or @c=0
exec master..xp_cmdshell 'md E:\back_full\log'
exec master.dbo.xp_sqlmaint N' -Rpt "E:\back_full\log\test_full.log" -D "test" -BkUpDB "E:\back_full" -BkUpMedia DISK -BkExt "bak" -DelBkUps 1days'
DBCC SHRINKFILE (test_Log,1)
endGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO--数据库检测
CREATE proc check_db
as
begin
--得到目录信息
create table #t (a int,b int,c int)
insert into #t(a,b,c)
exec master..xp_fileexist 'E:\check_db_rpt'
declare @b int
select @b=b from #t
drop table #t
--判断目录是否存在 如不存在 创建该目录
if @b=0
exec master..xp_cmdshell 'md E:\check_db_rpt' exec master.dbo.xp_sqlmaint N'-CkDB -CkAl -CkCat -Rpt "E:\check_db_rpt\check_db.log" -D "test"'
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
一般自己 维护计划就可以了 维护计划里有个自动删除上次备份的功能