<

How to Attach SQL Database Which is Not Properly Detached

Published on
844 Points
744 Views
1 Endorsement
Last Modified:
Bharat Bhushan
Experts in data recovery from corrupt Exchange mailboxes, SQL database, and Outlook emails.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.

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.

1
Comment
1 Comment

Expert Comment

by:Rejin James
Thanks for such an informative article! It was of great help to me. Had already tried the 1st two methods which you had mentioned so I directly skipped to trying the software you recommended. Initially the software took about 35 minutes to scan my file, but the best part is that it showed all the records of my database. That moment was a huge sigh of relief to me since I was able to see the crucial data after spending almost 2 days repairing the .mdf file. Now that I have recovered my data, I can finally sleep in peace. Stellar SQL Database Repair is definitely a brilliant software!
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Join & Write a Comment

SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month