Drop database only if Backup is successful

Question

This might be an easy one for some one but I haven't found a simple solution yet.

I'm automating a larger process at the moment, and one step is to back up then drop the database, before recreating it from scratch.

I've got a script that will do the back up and drop as follows:

Use [Master]
BACKUP DATABASE [databaseName]
  TO DISK='D:\Backup\databaseName\20100122.bak'

ALTER DATABASE [databaseName] 
    SET SINGLE_USER 
    WITH ROLLBACK IMMEDIATE

DROP DATABASE [databaseName] 

but I'm worried that the DROP will happen even if the BACKUP fails.

How can I change the script so if the BACKUP fails, the DROP won't happen?

Thanks in advance!

Answer 1

If your SQL Server version is 2005 or greater, you can wrap your statements with a try catch. If the backup fails, it will jump to the catch without dropping the database...

Use [Master]
BEGIN TRY

BACKUP DATABASE [databaseName]
  TO DISK='D:\Backup\databaseName\20100122.bak'

ALTER DATABASE [databaseName] 
    SET SINGLE_USER 
    WITH ROLLBACK IMMEDIATE

DROP DATABASE [databaseName] 
END TRY
BEGIN CATCH
PRINT 'Unable to backup and drop database'
END CATCH