1

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).

1 Answer 1

1

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.

1
  • Great solution, exactly what I was after, thanks. (Though, for posterity: I actually have several references to [myDb_snapshot] in the script, including a "use" and some queries. So in the end I decided it was easier to have an additional Step in the job, that only runs on failure of Step 1, that creates [myDb_snapshot]. Best solution for me right now, I think).
    – MGOwen
    Mar 24, 2015 at 8:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.