Sql-server – What to do when Sql Server Agent refuses to run a Job Step that references a missing database snapshot

jobssnapshotsql serversql-server-2012sql-server-agent

I have a SQL Server Agent Job creating a snapshot of my main database every minute:

-- delete snapshot if it exists
if exists (select * from sys.databases where name = 'mydb_snapshot' ) 
begin 
    drop database [mydb_snapshot]
end;

-- create new snapshot (whether or not we just deleted an old one)
create database [mydb_snapshot] on ( NAME = 'MYDBYEAH', FILENAME = 'D:\mydb_snapshot.ss' ) as snapshot of [mydb];

The snapshot is used both for reporting, and for a query later in the same Job Step.

(I'm new to Sql Server Agent Jobs and Snapshots, so feel free to point out any WTFs so far.)

My problem is Sql Server Agent refuses to run the job at all, citing that [mydb_snapshot] doesn't exist:

Database 'mydb_snapshot' does not exist. Make sure that the name is entered correctly. [SQLSTATE 08004] (Error 911). The step failed.

(That error doesn't have a line number associated with it. Rather, it seems SS Agent is checking the whole script before it starts running it and is smart enough to see I reference a non-existant snapshot, but not smart enough to see I always create that snapshot in the previous line. With debug statements, I can see that it doesn't even run the first line of the TSQL in my Job Step).

Any ideas what I can do to fool SQL Server Agent into running this job?

Or perhaps a different approach altogether?

(One thing I tried: it does work if I run the above create database [mydb_snapshot] statement manually, first. From then on, the Job runs every minute, no issues. But that means if that snapshot is deleted – which has happened at least once, not sure why – my Job fails for no good reason).

Best Answer

Replace 'drop database [myDb_snapshot]' with:

declare @cmd nvarchar(max);
set @cmd = 'drop database [myDb_snapshot]';
exec (@cmd);

The entire SQL statement is evaluated for correctness prior to running it. Since the snapshot doesn't exist, it won't run. Dynamic SQL removes that limitation.