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;
Improve this question
4
  • 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. Commented Mar 5, 2012 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...? Commented Mar 5, 2012 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. Commented Mar 6, 2012 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. Commented Mar 26, 2015 at 18:38

3 Answers 3

Reset to default
7

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.

Improve this answer
1
  • 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. Commented Mar 6, 2012 at 9:06
3

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.

Improve this answer
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 */
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.