TSQL for "Ignore databases were the state is not online" I have a few custom jobs on a mirrored SQL Server 2005 and they fail from time to time because of automatic failover.

Is there some T-SQL code that I can write into my automated jobs or do I just have to remember to set failover so the job doesn't fail?
I would suggest to use Ola's backup solution (and Index maintenance  solution as well). This is a well applauded solution that is smart enough to work with mirroring (in your case).

Maintenance plans are not bad, but when your environment grows, the limited flexibility and functionality that maintenance plans provide wont be sufficient.

For e.g Maintenance plans suffer from below drawbacks :


There is no CHECKSUM option when taking backup of databases or there is no option of verifying backups using CHECKSUM.
There is not much logic you can implement when dealing with index fragmentation and once you configure it, they run regardless if you really need a reorg or rebuild on your indexes... and many more :-)


To address your issue, you need to check sys.databases where state_desc = 'ONLINE' and adjust your scripts.

Also, you can have a script that detects failover and disables the jobs on secondary server.

Gentle Note: Extended support for SQL Server 2005 ends on April 12, 2016