May 22, 2007 at 12:20 pm
This cursor is intended to return all databases not backed up within the last two days and there is just one
But by the time the query is killed it returns about 42 of the same entries
Your help in this will be greatly appreciated
Feel free to look at the loop statement only starting from this line if you do not have time to go through the entire thing
DECLARE c CURSOR FOR SELECT dbname FROM #backupmissed
DROP TABLE #backupmissed
CREATE TABLE #backupmissed(dbname VARCHAR(50),lastbackup VARCHAR(21))
SET ansi_warnings OFF
SET NOCOUNT on
DECLARE @names VARCHAR(30)
INSERT INTO #backupmissed(dbname,lastbackup)
SELECT A.database_name as 'DBName',
A.backup_finish_date as 'Backup Finished'
FROM msdb.dbo.backupset A,
msdb.dbo.backupmediafamily B,
(SELECT database_name,
MAX(backup_finish_date) as 'maxfinishdate'
FROM msdb.dbo.backupset
WHERE Type = 'D'
GROUP BY database_name) C
WHERE A.media_set_id = B.media_set_id AND
A.backup_finish_date = C.maxfinishdate AND
A.type = 'D' AND cast(A.backup_finish_date as datetime)
DECLARE c CURSOR FOR SELECT dbname FROM #backupmissed
open c
FETCH next FROM c INTO @names
while @@fetch_status=0
BEGIN
PRINT @names
RAISERROR('The most current backup of the database %s is more than two days old',16,10,@names)with nowait
END
close c
deallocate c
DROP TABLE #backupmissed
May 22, 2007 at 1:26 pm
You need to add another fetch next into your begin/end block like so:
CREATE TABLE #backupmissed(dbname VARCHAR(50),lastbackup VARCHAR(21))
SET ansi_warnings OFF
SET NOCOUNT on
DECLARE @names VARCHAR(30)
INSERT INTO #backupmissed(dbname,lastbackup)
SELECT A.database_name as 'DBName',
A.backup_finish_date as 'Backup Finished'
FROM msdb.dbo.backupset A,
msdb.dbo.backupmediafamily B,
(SELECT database_name,
MAX(backup_finish_date) as 'maxfinishdate'
FROM msdb.dbo.backupset
WHERE Type = 'D'
GROUP BY database_name) C
WHERE A.media_set_id = B.media_set_id
AND A.backup_finish_date = C.maxfinishdate
AND A.type = 'D'
-- AND cast(A.backup_finish_date as datetime)
ORDER BY DBName
DECLARE c CURSOR FOR SELECT dbname FROM #backupmissed
open c
FETCH next FROM c INTO @names
while @@fetch_status=0
BEGIN
PRINT @names
RAISERROR('The most current backup of the database %s is more than two days old',16,10,@names)with nowait
FETCH next FROM c INTO @names
END
close c
deallocate c
DROP TABLE #backupmissed
May 22, 2007 at 1:41 pm
Thanks that worked .Now there is no next to fetch so fetch_status should not be equal to zero and the test fails
Mike
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply