How to detach corrupt database in SQL Server

Question

I have Sql server data files saved on external storage. I want to detach specific database. If external storage is not connected I want to drop database.

Is this safe way to do that:

 DECLARE @DB_NAME SYSNAME = 'Database_name';

 BEGIN TRY          

        -- Check if db has correct structure
        DBCC CHECKDB (@DB_NAME)  WITH  ALL_ERRORMSGS ;

        -- Stop using db 
        EXEC( 'ALTER DATABASE ' +@DB_NAME + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE; ');

        -- Detach db
        EXEC sp_detach_db @dbname= @DB_NAME , @skipchecks= 'true';      

    END TRY
    BEGIN CATCH

        -- DB files are missing - drop database
        IF ERROR_NUMBER() = 945 
        BEGIN
            EXEC ('DROP DATABASE  ' + @DB_NAME);
        END     
    END CATCH;

Answer 1

Like gbn commented, if the database is on storage that is not accessible, it is going to be set to suspect and the attempt to set it to single-user will always fail. The call to sp_dbremove will properly remove the entry from the database list, and it won't be attempted to be brought online next time you start the SQL service.

Be aware that sp_dbremove is deprecated, and DROP DATABASE should work just as well if the database is suspect.