Q:

SQL Server Ignoring IF Statement

Every night, I backup my production server and push the backup to my dev server. My dev server then has a job that runs which first checks if the backup file exits, if so check if the database exists in dev and if so drop the database, then restore from file. This all works fine, unless the file is not yet complete due to slow transfer, etc. If the file is not completely downloaded when the job runs then the first step sees it exists and drops the database. The next step tries to restore and of course fails. The next day when the job runs I would expect that when it checks if the database exists, it would see that it does not and shouldn't attempt to drop it and just restore. However, what's happening is the job is unable to drop the database and just fails at that point. This requires manual intervention to get the database restored, which is my problem. I'm less concerned with the fact of having no database existing on the server for a day (in theory) as I can tweak the schedule further to restore sooner. What I am concerned with is why is the IF statement not working to check if the database exists and attempts to drop a database regardless? Here's the T-SQL code that I am using:
DECLARE @output INT
DECLARE @SqlPath varchar(500) = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\PROD-01_prod_backup.bak'

EXECUTE master.dbo.xp_fileexist @SqlPath, @output OUT
IF @output = 1
BEGIN
    IF (EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE ('[' + name + ']' = '[PROD-01]')))
    BEGIN
        ALTER DATABASE [PROD-01] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
        DROP DATABASE [PROD-01]
    END

    RESTORE DATABASE [PROD-01] FROM DISK = @SqlPath
END

A:

I'm not sure how this is happening, as I am unable to reproduce, but a TRY / CATCH block is an ideal solution for this case:
SET XACT_ABORT ON
GO

DECLARE @output INT
DECLARE @SqlPath varchar(500) = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\PROD-01_prod_backup.bak'

EXECUTE master.dbo.xp_fileexist @SqlPath, @output OUT
IF @output = 1
BEGIN
    IF (EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE (name = 'PROD-01')))
    BEGIN TRY
        ALTER DATABASE [PROD-01] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
        DROP DATABASE [PROD-01]
    END TRY
    BEGIN CATCH
        SELECT ERROR_MESSAGE();
    END CATCH

    RESTORE DATABASE [PROD-01] FROM DISK = @SqlPath
END

