Resolving SQL server Error Message 5123: Cannot attach DB

To move a SQL database from one location to another location; first you need to detach the database from server and then re-attach it on another server. SQL server offers two methods for detaching a database: using SQL server management studio (SSMS) or using Transact SQL (T-SQL). And the detached database can be moved to another server and attached over new server.

Sometimes you may got following error message while attaching a detached database on another server

SQL Server -Error 5123: Cannot attach DB.

Cause: It is a very common error message that has been seen by most of the database administrator during attaching a database. Above error message generally occurs when attaching a database on the different location.

Resolution: Please check any of the following solutions that can fix your problem:

Verify the Name of database: It might be possible that you have misspelled the name of database or trying to attach the database from different location. Please verify the name and path of database and try again.

Rename the database file: Just rename the database file name and try to reattach the database from newly named database.

Disable the Anti-virus Software: If you have installed any anti-virus software on your PC then disable it and re-try to attach your database. It has been seen that sometimes anti-virus software locks the database.

Check the Backup Software: Check the Backup software. It also locks the database.

Allow NTFS permission security: It might be possible that you have not given NTFS security permission to everyone. So first all the permission for every one and try again.

Restart the SQL server services: First close all the services of SQL server and start the SQL server again. Most of the experts DBAs recommend restarting the SQL services after your defined working hours.

Use SP_Attach_DB Command: It is another method to attach a database. If you had tried GUI method to attach the database then try sp_attach_db command. Open a new query and use sp_attach_db command.

Change the Location of database file: Move the database to a new location (sub directory, root directory etc.) and try to re-attach the database from there.

Each of the above mentioned possible solutions can do the trick. Please try re-attaching the DB after each possible solution.