January 17, 2014 at 7:21 am
How can I tell which of my backup jobs is using the verify option.
I have an estate with over 80 Servers. Is there a table in the MSDB that can be queried.
January 17, 2014 at 7:33 am
January 17, 2014 at 8:31 am
Sorry I don't want to verify the backup> I want to check all my backup jobs to see which ones have or haven't got the verify enabled. Is there some T-sql I can run across the estate to return this info.
January 17, 2014 at 8:47 am
How do you create your backups? Do you use a GUI or do you use code?
If you use code then you need to see if the verify option is used. You can query the code of stored procedures in table [syscomments]. If you have the code in job steps you'll need to query the table [sysjobsteps].
January 17, 2014 at 8:51 am
Talib123 (1/17/2014)
Sorry I don't want to verify the backup> I want to check all my backup jobs to see which ones have or haven't got the verify enabled. Is there some T-sql I can run across the estate to return this info.
Why do you not want to verify your backups? If you restore it and it succeeds you wouldn't have to worry about making sure that the verify option is selected?
January 17, 2014 at 8:57 am
You can query the code of stored procedures in table [syscomments].
Best to use sys.sql_modules, since syscomments starts a new row after every 4000 characters (I think) of the definition. If the text you're looking for spans the break, it won't get picked up.
Why do you not want to verify your backups?
I'm not sure he's saying he doesn't actually want to verify his backups - just that that isn't what he's asking here how to do.
John
January 17, 2014 at 9:01 am
You can play with has_backup_checksums column in msdb..backupset table
January 17, 2014 at 9:02 am
Why do you not want to verify your backups?
I'm not sure he's saying he doesn't actually want to verify his backups - just that that isn't what he's asking here how to do.
John
Granted, but if you restore from your backups you can verify that the backup set is readable (which is what the "Verify Backup when finished" does) and that you can actual restore the backup. Two different things. As long as this option being set doesn't give the OP a false sense of security thinking that they can restore the backup too.
January 17, 2014 at 9:06 am
SQL Guy 1 (1/17/2014)
You can play with has_backup_checksums column in msdb..backupset table
That is not the same as "Verify backup when finished". That will show you if you selected "Perform checksum before writing to media".
January 17, 2014 at 9:08 am
Agreed, yes, and much has been written about the importance of doing test restores. I still verify my backups as well - after all, there's no point in going to the trouble of trying to restore a backup if it's failed the first test.
John
January 17, 2014 at 9:13 am
Talib123 (1/17/2014)
How can I tell which of my backup jobs is using the verify option.I have an estate with over 80 Servers. Is there a table in the MSDB that can be queried.
This may help
SELECT j.name
, j.enabled
, j.description
, js.step_id
, js.step_name
, js.subsystem
, js.command
FROM MSDB..sysjobs j
INNER JOIN MSDB..sysjobsteps js
ON j.job_id = js.job_id
WHERE js.command LIKE '%RESTORE%VERIFYONLY%'
ORDER BY j.name, js.step_id
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 17, 2014 at 10:29 am
I'll read through all of this later but the Environment is too large and too much going on to test backups regularly. 150 + Servers. Just one DBA.
99% of the jobs will be via GUI. I don't want to log onto every server.
January 17, 2014 at 10:52 am
Talib123 (1/17/2014)
I'll read through all of this later but the Environment is too large and too much going on to test backups regularly. 150 + Servers. Just one DBA.99% of the jobs will be via GUI. I don't want to log onto every server.
Run the above query via central management server 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 17, 2014 at 1:34 pm
Talib123 (1/17/2014)
I'll read through all of this later but the Environment is too large and too much going on to test backups regularly. 150 + Servers. Just one DBA.99% of the jobs will be via GUI. I don't want to log onto every server.
To manage that many instances by your own, you need to build/buy/borrow some kind of automated maintenance and some kind of monitoring and central management server. The scripts of Ola Hallengren http://ola.hallengren.com/ are a good start for maintenance on each instance. There is also a freeware project from DBWarden http://www.sqlservercentral.com/articles/Monitoring/98106/ as a central monitoring server that you can use as a start. If you search the net, you will probably find several more.
Build scripts and jobs and let each instance regularly report to a central server. From this central server you can query about the status of each instance.
January 20, 2014 at 3:54 am
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.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply