7

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;
  • 2
    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
  • 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
6

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.

  • 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
1

I know this is an old post, but, I just wanted to say that in my case, an entire drive upon which a database was dependent, was removed. As it happened the database was no longer required, as it was just a temporary holding area. However, I was unable to detach or drop the database as SQL Server complained about being unable to find the drive. However, the "deprecated" sp_dbremove, worked like a champ.

0

Indeed, old but still valid (adding the full error text to help with the search, as well):

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'SSDTest2WithFOURfiles';
GO

USE [master]
GO
/****** Object:  Database [SSDTest2WithFOURfiles]    Script Date: 20/11/2019 21:10:38 ******/
DROP DATABASE [SSDTest2WithFOURfiles]; /* Suggested, but doesn't work */
GO

/*
Msg 823, Level 24, State 2, Line 6

The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x000000000ae000 in file 'W:\Data\SSDTest2WithFOURfiles_01.mdf'. 
Additional messages in the SQL Server error log and system event log may provide more detail. 

This is a severe system-level error condition that threatens database integrity and must be corrected immediately. 
Complete a full database consistency check (DBCC CHECKDB). 

This error can be caused by many factors; for more information, see SQL Server Books Online.

Completion time: 2019-11-20T21:25:04.9050311+01:00
*/


EXEC sp_dbremove @dbname = N'SSDTest2WithFOURfiles'; /* Deprecated, but does work, indeed */
GO

/*
Database removed.

Completion time: 2019-11-20T21:26:29.6991511+01:00
*/
0

If you are unable to detach your database then, what is the state of your DB? It may be in SUSPECT mode. If It is in Suspect mode he, try the following solution:

ALTER DATABASE  dbName  SET  EMERGENCY
DBCC CHECKDB ('dbName')
ALTER DATABASE dbName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CHECKDB (dbName, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE dbName SET MULTI_USER

Note: be careful while using repair allow data loss command. You can also check this thread: https://stackoverflow.com/questions/38995380/sql-server-how-to-attach-repair-a-detached-damaged-database/38995692

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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