How to Attach SQL Database Which is Not Properly Detached

When is it recommended to attach and detach a database?
When you have a new server, it is common to receive a request to migrate your database from your old Server to the new one.

You could backup your database and restore on the new server. However, if for example, your database has 5 TB space, it is a lot of space that you use in the old server, wherein you would have enough space for backup and for the new server.

A better option in this scenario is to detach the database from the old server and attach as a new data file. This option will basically move the data files and log files (the physical files that contain the database information. This option is ideal if you want to erase the database in the old server

How can I detach and attach a database in SQL Server?

The easiest option is to use SSMS.

In SSMS Right Click the database that you want to detach and select the option Tasks>Detach:

You can drop connections and update statistics.  

You can see that The database will no longer be connected to the database. You can move your data files and log files to another server and attach the files.

Go to the SSMS and in the Databases Container right click and select Attach:

In the Attach Databases window, press the Add button to add the data file:

Select the MDF file and press OK:

Is it possible to attach and detach a database using T-SQL?

Of course, you can detach your database using these T-SQL sentences:

USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'stellar'
GO

In the master database, you can call the sp_detach system stored procedure and specify the database name to detach.

In order to attach, you can use the following T-SQL sentences:

USE [master]
GO
CREATE DATABASE [stellar] ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\stellar.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\stellar_log.ldf' )
FOR ATTACH
GO

Where the stellar.mdf is the data file and stellar_log.ldf is the log file. You need to specify the path of the MDF and log files.
Make sure that your SQL Server user has permissions in the path of the data files.

Another way to attach a database is to use the create database

CREATE DATABASE Stellar  
ON (FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\stellar.mdf')  
FOR ATTACH ;

The sentence will create a database named by stellar. You only need to specify the primary data file. The log and secondary data files must be in the same location.

If you do not have a log file, you can use the following T-SQL commands to generate a new log file:

CREATE DATABASE Stellar
ON (FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\stellar.mdf')
FOR ATTACH_REBUILD_LOG ;

What are the common errors when you try to detach a database?

A common error message you receive, because the database is under replication. If that is the case, you need to remove the replication first.

Another error is when you have a database snapshot. If that is the case, you need to remove the snapshot before detaching the database.

If the database is under suspect status, you will not be able to detach the database. You have to repair the database first in that scenario.

How to attach a corrupted database?

Sometimes the data file to attach is corrupted and it is necessary to fix the data file.

If you are getting an error message, provided that you have permissions for folder and file and being all this, when you try to attach the database, it means that your detached file is corrupted.

If it is already detached and if you do not have backups, then the option comes in is to repair the data file. If the data file is detached and you cannot attach the database because it is corrupted, it is not possible to repair the database using a DBCC Database with a repair option.

One simple and safest choice is to use Stellar Phoenix SQL Database Repair. This SQL Recovery Software from the company Stellar Information Technology Pvt. Ltd that started in 1993.

The installer has 4.8 MB approximately in size and it supports all the available Windows Desktop editions.
Once the software is installed, you can select the option to select the database and specify the MDF file. You have multiple options to select the corrupt MDF file. As mentioned below.

Once selected the data file, press the repair button to repair the file:

You will receive a message "Selected MS SQL database repaired successfully"

press OK to save the repaired database:

Once repaired, try to attach again and you will successfully attach it.

Conclusion
In this article, we have shown that how to detach and attach a database using SSMS or T-SQL. We have learnt how to use Stellar Phoenix SQL Database Repair to repair the database to attach a corrupt MDF file.

The process is simple, Stellar Phoenix SQL Database Repair is a simple tool and it does not require technical knowledge to use i.e  I insist that one should give a try and download the software for all the SQL database corruption-related problems.

If you have more question about attaching and detaching databases and corrupted databases, feel free to ask your questions.