I have a procedure that should be run just after databases are restored. For that, I want to make a job with 2 steps:
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.
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