Delay run until restore is done.

I had a problem the a while back where I noticed a job kept dieing because it was pulling data from a log shipped database and it was trying to pull that data while the database was restoring it's log file. So I came up with this little sp to fire right before I execute my code. What this does is, if the datase is restoring (State = 1) it performs a loop and every 5 seconds checks back to see if it is still restoring and does this until it is no longer restoring. Since I'm executing this before I do a EXEC (@SQL) it will delay the run of my code until the database is ready.



This can be edited to work for other states, or all non ONLINE states by simply changing the while loop to from @IsRestoring = 1 to @IsRestoring <> 0

I wish there was a better way of formatting this, my code doesn't look this crappy

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

/**********************************
**  Run_After_Restore_Completes  **
**  Created by Eric Zierdt       **
**********************************/

ALTER PROCEDURE [dbo].[Run_After_Restore_Completes] (@DBName VARCHAR(155))
AS

DECLARE @IsRestoring BIT

SET NOCOUNT ON

-- RUN IF THE DATABASE EXISTS
IF EXISTS (SELECT name FROM sys.databases WHERE name = @DBName)
BEGIN
 -- CHECK THE STATE OF THE DATABASE
 SELECT @IsRestoring = State 
 FROM sys.databases 
 WHERE NAME = @DBName

 -- LOOP UNTIL DATABASE IS NO LONGER RESTORING
 WHILE @IsRestoring = 1
 BEGIN
  -- SET A DELAY SO IT ISN'T RUNNING CONSTANTLY
  WAITFOR DELAY '00:00:05'
  SELECT @IsRestoring = State FROM sys.databases WHERE NAME = @DBName
 END
END