Detaching corrupt databases

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.

2 thoughts on “Detaching corrupt databases

  1. Pingback: Detaching corrupt databases - SQL Server Blog - SQL Server - Toad World

  2. Pingback: Detaching Corrupt Databases – Curated SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s