Database folder only if backup is successful

Question

It might be easy for someone, but I haven't found a simple solution yet.

Now I am automating a larger process, and one step is to back up and then reset the database before re-creating it from scratch.

I have a script that will backup and discard 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 DROP will happen even if BACKUP fails.

How can I change the script, so if BACKUP fails, DROP will not happen?

Thanks in advance!

Answer 1

SQL Server 2005 , try catch. , catch, ...

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

Answer 2

, SQL Server, . . , , T-SQL, , :

USE [Master]

DECLARE @errorCode int

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

SET @errorCode = @@ERROR

IF (@errorCode = 0)
BEGIN

    ALTER DATABASE [databaseName] 
        SET SINGLE_USER 
        WITH ROLLBACK IMMEDIATE

    DROP DATABASE [databaseName] 

END

, , -, . SELECT * FROM master.sys.messages , .