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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

        -- Stop using db 

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


        -- DB files are missing - drop database
        IF ERROR_NUMBER() = 945 
            EXEC ('DROP DATABASE  ' + @DB_NAME);
share|improve this question
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
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
I know this is an old post but an FYI for anyone using Drop Database it will remove the mdf file entirely from the system. Tried it and the DB vanished. Wasn't too worried as I had backups but some people might not have that luxury. – Johnny5 Mar 26 '15 at 18:38
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


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.