CODEWITHSUNDEEP
sql-servert-sqlssissql-server-agent
Miszyn
Miszyn

Reputation: 23

Job step dependent on the availability of the database

I have a procedure that should be run just after databases are restored. For that, I want to make a job with 2 steps:

  1. Checking the databases' status at time intervals
  2. Procedure, triggering after Step 1 on success

For Step 1, I want to use this query to retrieve status:

SELECT msd.state_desc
FROM [master].[sys].[databases] msd

Checking for certain databases where state_desc is not equal to RESTORING. If this condition is met, the second step should trigger.

I was thinking about doing this in SSIS. Which blocks should I use for this check? Or is it better to use just T-SQL?

Searched the web for solutions, but without success.

Upvotes: 0

Views: 70

Answers (1)

Miszyn
Miszyn

Reputation: 23

I have found a solution in T-SQL.

DECLARE @Database_Count int

SELECT @Database_Count = COUNT(*)
FROM [master].[sys].[databases] msd
WHERE msd.name IN (
    'dbname1',
    'dbname2',
    'dbname3'
)
AND msd.state_desc <> N'ONLINE' 
    
IF @Database_Count > 0 BEGIN  
    THROW 53000, 'DB not ready',1;
END

Upvotes: 2

Related Questions