Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;
share|improve this question
1  
I assume that your database is marked suspect otherwise you would not be wanting to do this. Just be certain that really do not want to try and re-attach after detaching because you will not be able to do so. –  jl01 Mar 5 '12 at 15:36
1  
If the database is not there, it is suspect. So you'd test this via sys.databases. Why doy ou have DBs on external storage...? –  gbn Mar 5 '12 at 16:17
    
@gbn I use database on USB to quickly exchange work between users in disconnected environment. If you pull out USB, sys.database will still "think" that everything is OK. –  Hacko Mar 6 '12 at 9:19

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
    
Thank you, I'll use DROP DATABASE instead of sp_dbremove. DBCC CHECKDB (@DB_NAME) WITH ALL_ERRORMSGS will throw exception if db is suspect and than it will be deleted. I don't want to delete database files because I use them to recreate database later. –  Hacko Mar 6 '12 at 9:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.