现在重新把需求说一下,请大家帮帮忙。
1,比方我有5个数据库需要备份,而且都有相对应的保存目录
2,我现在要检查这些备份是否成功(备份是以压缩包的形式存在,且命名有规律)
3,可以先对这5个数据库建立表格,然后用游标检索相应的位置,再检查备份是否成功
4,将结果返回到另一张表中,可用逻辑类型表示。基本是这样的要求,但是我现在没有什么思路,希望大家多给建议。下面我给出备份压缩包的命名规律:
全备:'OperationDB_FULL_' + @RCL_DATE + '.dmp'
差备:'OperationDB_DIFF_' + @RCL_DATE + '.dmp'
1,比方我有5个数据库需要备份,而且都有相对应的保存目录
2,我现在要检查这些备份是否成功(备份是以压缩包的形式存在,且命名有规律)
3,可以先对这5个数据库建立表格,然后用游标检索相应的位置,再检查备份是否成功
4,将结果返回到另一张表中,可用逻辑类型表示。基本是这样的要求,但是我现在没有什么思路,希望大家多给建议。下面我给出备份压缩包的命名规律:
全备:'OperationDB_FULL_' + @RCL_DATE + '.dmp'
差备:'OperationDB_DIFF_' + @RCL_DATE + '.dmp'
set fs=CreateObject("Scripting.FileSystemObject")
if fs.fileexists("c:\OperationDB_FULL_" + @RCL_DATE + ".dmp") then
osql -E -dtest -Q "insert into test ([db_name],bak_date,flag) values ('test',getdate(),'Y')"
end if
set fs=CreateObject("Scripting.FileSystemObject")
if fs.fileexists("c:\OperationDB_FULL_" + @RCL_DATE + ".dmp") then
osql -E -dtest -Q "insert into test ([db_name],bak_date,flag) values ('test',getdate(),'Y')"
end if
set fs=CreateObject("Scripting.FileSystemObject")
if fs.fileexists("c:\OperationDB_FULL_" + @RCL_DATE + ".dmp") then
osql -E -dtest -Q "insert into test ([db_name],bak_date,flag) values ('test',getdate(),'Y')"
end if
是不是能麻烦你再深入的说一下,Dim fs是什么?我不是很清楚
你给出的这一段应该是检测是否存在并返回结果把?那如何先用游标来找到某个备份包呢?
其实关键是用哪个存储过程完成对指定目录下压缩包的检查,看他是否存在并返回结果保存是不是能再给点建议,谢谢
可以在sysjobhistory获得信息.
怎样通过存储过程来完成,我现在也在思考中,希望大家多帮助我
set @filepath=(select --'C:\tmp.txt'
path+[filename]
from [file]
)
Declare @INT_ERR int ----错误代码,0 成功
Declare @INT_FSO int ----FSO对象标志
declare @flag int -- if exists
--创建FSO对象
EXEC @INT_ERR = sp_OACreate 'Scripting.FileSystemObject', @INT_FSO OUTPUT
-----文件路径
Declare @STR_FILENAME nvarchar(50)
Set @STR_FILENAME = @FilePath
exec @int_err=sp_OAMethod @int_fso,'fileexists',@flag output,@str_filename
insert into t_test([file_name],flag) values (@str_filename,@flag)
----销毁创建的对象
EXEC @INT_ERR=SP_OADESTROY @INT_FSOP:我機子好像中招了,每次發帖都會發三次一樣的出來,鬱悶..........
set @filepath=(select --'C:\tmp.txt'
path+[filename]
from [file]
)
Declare @INT_ERR int ----错误代码,0 成功
Declare @INT_FSO int ----FSO对象标志
declare @flag int -- if exists
--创建FSO对象
EXEC @INT_ERR = sp_OACreate 'Scripting.FileSystemObject', @INT_FSO OUTPUT
-----文件路径
Declare @STR_FILENAME nvarchar(50)
Set @STR_FILENAME = @FilePath
exec @int_err=sp_OAMethod @int_fso,'fileexists',@flag output,@str_filename
insert into t_test([file_name],flag) values (@str_filename,@flag)
----销毁创建的对象
EXEC @INT_ERR=SP_OADESTROY @INT_FSOP:我機子好像中招了,每次發帖都會發三次一樣的出來,鬱悶..........
set @filepath=(select --'C:\tmp.txt'
path+[filename]
from [file]
)
Declare @INT_ERR int ----错误代码,0 成功
Declare @INT_FSO int ----FSO对象标志
declare @flag int -- if exists
--创建FSO对象
EXEC @INT_ERR = sp_OACreate 'Scripting.FileSystemObject', @INT_FSO OUTPUT
-----文件路径
Declare @STR_FILENAME nvarchar(50)
Set @STR_FILENAME = @FilePath
exec @int_err=sp_OAMethod @int_fso,'fileexists',@flag output,@str_filename
insert into t_test([file_name],flag) values (@str_filename,@flag)
----销毁创建的对象
EXEC @INT_ERR=SP_OADESTROY @INT_FSOP:我機子好像中招了,每次發帖都會發三次一樣的出來,鬱悶..........
看是否真的备份成功了还要看表 msdb.dbo.backupsethttp://www.mssqltips.com/tip.asp?tip=1251
CREATE Proc [dbo].[SSP_CheckLog]
AS
SET NOCOUNT ON
DECLARE @BegDate Datetime ,@EndDate Datetime
SELECT @BegDate=CONVERT(Varchar(10),GetDate()-1,120),@EndDate=CONVERT(Varchar(10),GetDate(),120)
SELECT @[email protected]
/*****获取在日期期间的备份信息,备份数据库和备份物理文件****/
SELECT Media_SET_id,Backup_finish_date,Database_name,name
INTO #tmpbackup
FROM msdb..backupSET
WHERE Backup_finish_date>=@BegDate And Backup_finish_date<@EndDate And Name IS NULL
SELECT media_SET_id,backup_finish_date,database_name
INTO #TmpBak
FROM #tmpbackup a
WHERE (SELECT COUNT(*) FROM #tmpbackup b WHERE a.database_name=b.database_name
--and a.backup_SET_id=b.backup_SET_id
And a.media_SET_id<b.media_SET_id)<25
--and a.backup_finish_date>=@BegDate and a.backup_finish_date<@EndDate
And a.name is null
ORDER BY a.database_name,a.backup_finish_date
SELECT physical_device_name,device_type,a.*
INTO #tmpDevice
FROM #TmpBak a left join msdb..backupmediafamily b ON b.media_SET_id=a.media_SET_id
WHERE a.database_name not in('master', 'msdb','model','reportserver','reportservertempdb') DECLARE @str varchar(2000)
SET @str=''
SELECT @str=database_name+'('+convert(varchar,count(*))+')'+char(13)+@str FROM #tmpDevice
GROUP by database_name
HAVING count(*)<24
IF @str<>''
SELECT @str='按每天每隔一小时备份,以下数据库并没有如此进行备份,请检查!'+char(13)+@str+char(13)
/********************获取备份文件的序列号(log Backup)************************/
SELECT *
INTO #templsn
FROM
(SELECT backup_finish_date,database_name,first_lsn,last_lsn
FROM msdb..backupSET WHERE Name IS NULl And backup_finish_date<@EndDate And backup_finish_date>=@BegDate And Type='L' ) a
WHERE a.first_lsn NOT IN
(SELECT last_lsn FROM (
SELECT last_lsn FROM msdb..backupSET WHERE Name IS NULL And backup_finish_date<@EndDate And backup_finish_date>=@BegDate And Type='L'
) b )
DECLARE @Strlog Varchar(500)
SET @Strlog=''
/***********如果序列号前后的不匹配超过1次,则一定存在问题**********************/
--if (SELECT datepart(dw,getdate()))<>1
SELECT @Strlog=database_name+char(13)+@Strlog FROM #templsn
GROUP BY Database_name
HAVING COUNT(*)>1
IF @strlog<>''
SELECT @strlog=char(13)+'以下数据库存在不连续的日志序列,请检查日志备份是否有问题!'+Char(13)+@strlog+Char(13)
/*************获取备份文件的序列号(Full Backup)****************************/
SELECT Backup_finish_date,
database_name,
first_lsn,
last_lsn
INTO #tempFullBAK
FROM msdb..backupSET
WHERE Name IS NULL And Backup_finish_date<@EndDate And Backup_finish_date>=@BegDate And Type='D'
DECLARE @strData Varchar(500)
DEClARE @FullBAKSum int
DEClare @FullDBSum int
SELECT @FullBAKSum =COUNT(*) FROM #tempFullBAK
SELECT @FullDBSum= COUNT(*) FROM (SELECT DISTINCT Database_Name FROM msdb.dbo.sysdbmaintplan_databases)D
SET @strData='' IF ((SELECT datepart(dw,getdate()-1))=1) And (@FullBAKSum <> @FullDBSum)
SELECT @strData = char(13)+'数据库存在未正确全备份的数据库,请检查全备份!'+Char(13)
/***********循环校验备份的日志是否有存在硬盘上,检查备份集是否完整以及所有卷是否都可读********/
CREATE Table #error(device_name varchar(2000),errorstr varchar(1000))
SET XACT_ABORT OFF
DECLARE @physical_device_name varchar(1000),@execstr varchar(1000)
DECLARE ChecksLog CURSOR FOR
SELECT physical_device_name
FROM #tmpDevice
OPEN ChecksLog
FETCH NEXT
FROM ChecksLog
INTO @physical_device_name
WHILE @@FETCH_STATUS = 0
BEGIN
begin try
SELECT @execstr = 'SET NOCOUNT ON RESTORE VERIFYONLY FROM disk=''' + RTRIM(@physical_device_name) + ''''
EXEC (@execstr)
end try
--if @@error<>0
begin catch
INSERT INTO #error SELECT @physical_device_name,'无法打开备份设备,设备出现错误或设备脱机。'
end catch
FETCH NEXT
FROM ChecksLog
INTO @physical_device_name
END
CLOSE ChecksLog
DEALLOCATE ChecksLog
/**********如果上面有错误,或者#error有值,则发邮件提示大家************/
IF (len(@STR)>2) or (exists (SELECT count(*) FROM #error having count(*)>0))
BEGIN
Declare @str11 varchar(6000)
SET @str11=''
SELECT @str11=@str11+device_name+char(13) from #error
--SELECT @str1
if exists (SELECT count(*) FROM #error having count(*)>0)
SET @STR=@STR+'
----------------------------
无法打开备份设备,设备出现错误或设备脱机(日志不存在硬盘上):
'+@str11