Question – Can you detach a corrupt database?
Answer – IT DEPENDS!
More specifically it depends on the SQL Server version. SQL 2005 and prior it was possible, with the newer versions of SQL you will not be able to issue the command.
So let’s confirm this with SQL 2016.
Well I manufactured some corruption (I am starting to enjoy doing this too much for my own liking 😉 ) where the error log stated:
The header for file ‘C:\Program Files\Microsoft SQL Server\MSSQL12.SQLMCA\MSSQL\DATA\AmazonUK.mdf’ is not a valid database file header
I try to detach the database. (By the way, there is no good reason to do this; corruption does not just go away)
USE [master] GO ALTER DATABASE [AmazonUK] SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO USE [master] GO EXEC master.dbo.sp_detach_db @dbname = N'AmazonUK' GO
SQL Server Management Studio returns
ALTER DATABASE statement failed.
Msg 3707, Level 16, State 2, Line 7
Cannot detach a suspect or recovery pending database. It must be repaired or dropped.
I totally forgot how helpful this is. I thought that I would refresh the minds of whoever reads this too.
Pingback: Detaching corrupt databases - SQL Server Blog - SQL Server - Toad World
Pingback: Detaching Corrupt Databases – Curated SQL