1、建一个database maindb,里面建两个表 systemdatabases systemerrorCREATE TABLE [dbo].[SystemDatabases] ( [DataBaseName] [char] (20) COLLATE Chinese_PRC_CI_AS NOT NULL , [LastBackupDBTime] [datetime] NULL , [LastBackupLogTime] [datetime] NULL , [LastCheckDbTime] [datetime] NULL ) ON [PRIMARY] GOCREATE TABLE [dbo].[systemerror] ( [databasename] [char] (20) COLLATE Chinese_PRC_CI_AS NULL , [type] [char] (9) COLLATE Chinese_PRC_CI_AS NULL , [time] [datetime] NULL ) ON [PRIMARY] GO 将你要备份的数据库写到systemdatabases里2、新建一个存储过程,作完全备份的 CREATE PROCEDURE Backup_DB AS declare @currenthour smallint declare @backupcommand varchar(255) declare @databasename varchar(20) declare @dumpfile varchar(30) declare @i smallint,@j smallint select @i=0 select @j=count(databasename) from systemdatabases while @i<@j begin select @databasename=databasename from maindb.dbo.systemdatabases where LastBackupDBTime=(select min(LastBackupDBTime) from maindb.dbo.systemdatabases) if @databasename is null return select @databasename=rtrim(ltrim(@databasename)) select @currenthour=datepart(hour,getdate()) if @currenthour>=12 select @dumpfile='Noon'+@databasename+'Bak' else select @dumpfile=@databasename+'Bak' select @backupcommand='backup log '+@databasename+' with truncate_only' print @backupcommand execute(@backupcommand) select @backupcommand='backup Database '+@databasename+' to '+@dumpfile+' with init,nounload,noskip,stats=10,noformat' print @backupcommand execute(@backupcommand) if @@error<>0 insert into systemerror (databasename,type,time) values(@databasename,'BACKUPDB',getdate()) update systemdatabases set LastBackupDBTime=getdate(),lastbackuplogtime=getdate() where databasename=@databasename select @i=@i+1 end3、新建一个存储过程,作日志增量备份的CREATE PROCEDURE Backup_Log AS declare @currenthour smallint declare @backupcommand varchar(255) declare @databasename varchar(20) declare @dumpfile varchar(30) declare @i smallint,@j smallint select @i=0 select @j=count(databasename) from systemdatabases while @i<@j begin select @databasename=databasename from maindb.dbo.systemdatabases where LastBackupLogTime=(select min(LastBackupLogTime) from maindb.dbo.systemdatabases) if @databasename is null return select @databasename=rtrim(ltrim(@databasename))
select @currenthour=datepart(hour,getdate()) if @currenthour>=12 select @dumpfile='Noon'+@databasename+'Bak' else select @dumpfile=@databasename+'Bak' --select @backupcommand="dump transaction "+@databasename+" to "+@dumpfile+" with nounload,noinit,noskip" select @backupcommand='backup log '+@databasename+' to '+@dumpfile+' with noinit,nounload,noskip,noformat' print @backupcommand execute(@backupcommand) if @@error<>0 insert into systemerror (databasename,type,time) values(@databasename,'BACKUPLOG',getdate()) update systemdatabases set LastBackupLogTime=getdate() where databasename=@databasename select @i=@i+1 end4、新建一个存储过程,作定期维护的 CREATE PROCEDURE check_maintain_db AS declare @dbname varchar(20),@checkcommand varchar(40) declare @i smallint,@j smallint select @i=0 select @j=count(databasename) from systemdatabases while @i<@j begin select @dbname=databasename from systemdatabases where lastcheckdbtime=(select min(lastcheckdbtime) from systemdatabases) select @dbname=ltrim(rtrim(@dbname)) select @checkcommand='dbcc checkdb ('+@dbname+')' print @checkcommand exec(@checkcommand) if @@error<>0 insert into systemerror (databasename,type,time) values(@dbname,'CHECKDB',getdate()) update systemdatabases set lastcheckdbtime=getdate() where databasename=@dbname select @i=@i+1 end5、建立各个database对应的备份设备如xxx建立xxxbak、noonxxxbak两个备份设备,分别容纳上午和下午的备份数据 (待续)
sorry 忘了写邮箱了 afu7548@163com 多谢 多谢
可以联系一下:
[email protected]
thanks!!!
[DataBaseName] [char] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[LastBackupDBTime] [datetime] NULL ,
[LastBackupLogTime] [datetime] NULL ,
[LastCheckDbTime] [datetime] NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[systemerror] (
[databasename] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[type] [char] (9) COLLATE Chinese_PRC_CI_AS NULL ,
[time] [datetime] NULL
) ON [PRIMARY]
GO
将你要备份的数据库写到systemdatabases里2、新建一个存储过程,作完全备份的
CREATE PROCEDURE Backup_DB AS
declare @currenthour smallint
declare @backupcommand varchar(255)
declare @databasename varchar(20)
declare @dumpfile varchar(30)
declare @i smallint,@j smallint
select @i=0
select @j=count(databasename) from systemdatabases
while @i<@j
begin
select @databasename=databasename from maindb.dbo.systemdatabases
where LastBackupDBTime=(select min(LastBackupDBTime) from maindb.dbo.systemdatabases)
if @databasename is null
return
select @databasename=rtrim(ltrim(@databasename)) select @currenthour=datepart(hour,getdate())
if @currenthour>=12
select @dumpfile='Noon'+@databasename+'Bak'
else
select @dumpfile=@databasename+'Bak' select @backupcommand='backup log '+@databasename+' with truncate_only'
print @backupcommand
execute(@backupcommand) select @backupcommand='backup Database '+@databasename+' to '+@dumpfile+' with init,nounload,noskip,stats=10,noformat'
print @backupcommand
execute(@backupcommand)
if @@error<>0
insert into systemerror (databasename,type,time) values(@databasename,'BACKUPDB',getdate())
update systemdatabases set LastBackupDBTime=getdate(),lastbackuplogtime=getdate() where databasename=@databasename
select @i=@i+1
end3、新建一个存储过程,作日志增量备份的CREATE PROCEDURE Backup_Log AS
declare @currenthour smallint
declare @backupcommand varchar(255)
declare @databasename varchar(20)
declare @dumpfile varchar(30)
declare @i smallint,@j smallint
select @i=0
select @j=count(databasename) from systemdatabases
while @i<@j
begin
select @databasename=databasename from maindb.dbo.systemdatabases
where LastBackupLogTime=(select min(LastBackupLogTime) from maindb.dbo.systemdatabases)
if @databasename is null
return
select @databasename=rtrim(ltrim(@databasename))
select @currenthour=datepart(hour,getdate())
if @currenthour>=12
select @dumpfile='Noon'+@databasename+'Bak'
else
select @dumpfile=@databasename+'Bak' --select @backupcommand="dump transaction "+@databasename+" to "+@dumpfile+" with nounload,noinit,noskip"
select @backupcommand='backup log '+@databasename+' to '+@dumpfile+' with noinit,nounload,noskip,noformat'
print @backupcommand
execute(@backupcommand)
if @@error<>0
insert into systemerror (databasename,type,time) values(@databasename,'BACKUPLOG',getdate())
update systemdatabases set LastBackupLogTime=getdate() where databasename=@databasename
select @i=@i+1
end4、新建一个存储过程,作定期维护的
CREATE PROCEDURE check_maintain_db AS
declare @dbname varchar(20),@checkcommand varchar(40)
declare @i smallint,@j smallint
select @i=0
select @j=count(databasename) from systemdatabases
while @i<@j
begin
select @dbname=databasename from systemdatabases
where lastcheckdbtime=(select min(lastcheckdbtime) from systemdatabases)
select @dbname=ltrim(rtrim(@dbname))
select @checkcommand='dbcc checkdb ('+@dbname+')'
print @checkcommand
exec(@checkcommand)
if @@error<>0
insert into systemerror (databasename,type,time) values(@dbname,'CHECKDB',getdate())
update systemdatabases set lastcheckdbtime=getdate() where databasename=@dbname
select @i=@i+1
end5、建立各个database对应的备份设备如xxx建立xxxbak、noonxxxbak两个备份设备,分别容纳上午和下午的备份数据
(待续)
6.1、每天0:00am 执行Backup_DB
6.2、每天0:15am-11:45am 每5分钟执行Backup_LOG
--上午的备份,每5*n分钟(n为参加备份的数据库数量)备份一次数据库到xxxbak中
6.3、每天12:00pm 执行Backup_DB
6.4、每天12:15pm-11:45pm 每5分钟执行Backup_LOG
--下午的备份,每5*n分钟(n为参加备份的数据库数量)备份一次数据库到noonxxxbak中
6.5、每周选一个时间执行check_maintain_db ,整理一下数据库
(改变这里的时间间隔和运行时间可以实现不同的备份频次要求,如每天、每周、每月等等)
------------------------------------------------------------------
7、用NT的计划任务将xxxbak\noonxxxbak备份到磁带,千万不要随意对数据库进行备份到其他文件的操作,否则log的备份会乱掉。8、要常检查任务的运行日志,万一失效9、将database本身的属性设置为自动收缩,这样你的log文件可以控制在比较小的级别。我实际运行的一个数据库,数据大约6G,log文件保持在10M以下。10、对备份的xxxbak\noonxxxbak文件你可以压缩后作日常的备份,保持数据的可追溯性。比如你可以保留一个季度中每天的两个备份文件,就可以任意恢复到这个季度的任意5分钟的数据。别问我数据太多怎么办,现在硬盘才多少钱???