Posted by: Colin Smith
DB Online, T-SQL
I was also asked to make sure that I have jobs set up on my servers that have database mirroring set up. They only want the job to run if the database is online and they do not want to have to enable and disable jobs when failovers occur. So I did the following.
/* Style Definitions */
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-fareast-font-family:”Times New Roman”;
mso-bidi-font-family:”Times New Roman”;
declare @online int
set @online = (select state from sys.databases
where name = ‘Database_name’)
if @online = 1
raiserror (‘Database is not the current principal. not attempting to backup.’, 16,1)
Print ‘The Database is online’
So you can see that this is also pretty simple. I just check in sys.databases to make sure that the database of interest is online. If not then I have the step fail and I have the job set to quit the job reporting success. That way I do not see a bunch of job failures when it actually did just what I wanted. If the database is online then the step succeeds and moves on to step 2. This way everyone is happy.
hope this helps