While attempting to attach a database on one of my test servers, I encountered an error. Recognizing the potential value in sharing solutions to common database issues, I've decided to document the steps to troubleshoot and resolve this problem in my blog.
The fix is to:
Error Message
Attach database failed for Server 'ANULINCE'. (Microsoft.SqlServer.Smo)
For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.200.48044.0&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The file "D:\Stackoverflow\StackOverflow2010.mdf" is compressed but does not reside in a read-only database or filegroup. The file must be decompressed.
Could not open new database 'StackOverflow2010'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 5118)
For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-5118-database-engine-error
The fix is to:
- Go to D:\Stackoverflow\' might be different, depending on your mdf or ldf destination.
- Right click the ‘Stackoverflow’ folder.
- Choose 'Properties'.
- Click the 'Advanced' button.
- Uncheck the 'Compress contents to save disk space' checkbox.
- Click Ok..
- Select Apply Changes to this folder, Subfolders, and files.
- Click Ok
- Try Restore or Attach database again
For a comprehensive guide on troubleshooting database attachment errors related to compressed volumes, check out the Microsoft Learn documentation provided below: