January 21, 2014 at 10:34 am
You can query the default trace to find which databases have RESTORE VERIFYONLY commands run against them. You're limited to how far back the default trace goes, but since you're talking about scheduled backups, the entries should be in there.
Something like the following to look through all default trace files at once (credit where credit is due: http://allen-mcguire.blogspot.com/2013/01/query-all-default-trace-files-at-once.html):
DECLARE @filename NVARCHAR(4000);
-- Current default trace
SELECT @filename = CAST(value AS NVARCHAR(4000))
FROM ::
FN_TRACE_GETINFO(DEFAULT)
WHERE traceid = 1
AND property = 2
-- Preserve the path and replace the current default trace with log.trc
SET @filename = LEFT(@filename,
LEN(@filename) - CHARINDEX('\',REVERSE(@filename)))
+ '\log.trc'
-- Auto shrink/growth events in the current trace file
SELECT @@servername,t.StartTime, t.EndTime, t.textdata
FROM sys.fn_trace_gettable ( @filename, DEFAULT) T
JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
where t.TextData like 'restore verifyonly%'
ORDER BY t.StartTime;
You may want to modify it to only show if RESTORE VERIFYONLY is being run at all (instead of returning rows for every RESTORE VERIFYONLY), or to parse the text data to see what databases are being backed up with the verify option checked.
Another option, assuming that all your backups are definitely run through maintenance plans, would be to adopt an approach like the following, borrowed and modified from http://sqlchad.com/?p=339:
WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS DTS
, 'www.microsoft.com/sqlserver/dts/tasks/sqltask' AS SQLTask)
,ssis AS (
SELECT name
, CAST(CAST(packagedata AS varbinary(MAX)) AS XML) AS package
FROM [msdb].[dbo].[sysssispackages]
WHERE packagetype = 6
--This is "Maintenance Plan" Category
)
SELECT @@servername as Instance, s.name as MaintenancePlanName,
CASE c.value('(SQLTask:SqlTaskData/@SQLTask:BackupAction)[1]', 'INT')
WHEN 0 THEN
CASE c.value('(SQLTask:SqlTaskData/@SQLTask:BackupIsIncremental)[1]', 'bit')
WHEN 1 THEN 'DIFFERENTIAL'
WHEN 0 THEN 'FULL'
ELSE 'UNKNOWN'
END
WHEN 1 THEN 'FILES'
WHEN 2 THEN 'LOG'
ELSE 'UNKNOWN'
END as BackupType,
c.value('(SQLTask:SqlTaskData/@SQLTask:BackupVerifyIntegrity)[1]','VARCHAR(MAX)') as VerifyIsChecked
FROM ssis s
CROSS APPLY package.nodes('//DTS:ObjectData') t(c)
WHERE c.exist('SQLTask:SqlTaskData/@SQLTask:BackupDestinationAutoFolderPath') = 1
That will show all the backup maintenance plans along with whether the verify option is checked or not. That snippet is for 2008. The article in the URL shows how it is different for 2005 servers.
I hope this helps!
January 21, 2014 at 11:09 am
Talib123 (1/20/2014)
Perry Thanks, but the query didn't return anything. I am running my backups via Maintenance Plans (SSIS).Hanshi I need the script to run via my CMS to see where verify is not already being used. On those server I have it planned to use OLAs scripts.
You'll need to check the sql server log
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply