Drop database only if Backup is successful

backup, database, rollback, sql, sql-server

Solution

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

Problem

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!

Original source