August 6, 2009 at 3:15 pm
Hello,
I am attempting to test my backups by running a query that will pull all the backup devices from each DB on my instance an then run a RESTORE VERIFY ONLY on that backup device. While trying to do this I noticed that on the instances were I used maintenance plans to create the backup jobs I do not have any backup devices. Does anyone know why that is, and any way I could get around this?
August 6, 2009 at 3:18 pm
You should be able to query the backups tables in msdb and use the data from there.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 7, 2009 at 6:36 am
Thanks for the reply...
Would you happen to have the code for this?
This is what I was hoping to use with the backup devices
DECLARE csrBackups CURSOR FOR
SELECT name FROM sys.sysdevices
OPEN csrBackups
DECLARE @Backupdevice varchar (25), @sql nvarchar (50)
FETCH NEXT FROM csrBackups INTO @Backupdevice
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT #backupdevice (backupdevice) -Temp table is created for logging on which backups did not verify
VALUES (@Backupdevice)
SET @sql = 'RESTORE VERIFYONLY FROM' + @Backupdevice
EXEC sp_executesql @sql
August 10, 2009 at 7:32 am
You'd have to modify it a bit, but what you're looking for are the "backupfile", "backupfilegroup", "backupmediafamily", "backupmediaset", and "backupset" tables.
They're pretty straightforward if you dig into them.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 10, 2009 at 10:02 am
Ok Thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply