How to Attach & Detach SQL Server Database without Hassles in Microsoft Windows

This blog will discuss about procedure to detach and attach the database from SQL Server. First we will detach the database and then will attach the same database from available MDF files. It is suggested to have updated backup of the database to avoid any data loss. In this article, we’re going to first learn how to detach database SQL Server & then about how to attach database SQL Server.

Sometimes users find that attach database failed for server unable to open the physical filedue to dislocation of the MDF file or other reasons. A section on the problems and troubleshooting of the errors occurred while attaching the database is also discussed below.

Manual Method to Detach SQL Database Easily

This method will describe a step-wise procedure to detach SQL database;

• Go to Start>>Programs>>SQL Server>>SQL Server Management Studio and open the application.
• Once the application is open, connect to the SQL Server. Here enter correct server credentials and connect. (Connect with Windows Authentication (You have Windows administration rights)

• Once you are connected successfully, search for the database you want to detach. Select it and right-click to the database. Select Tasks and then click on Detach option as shown in the image below.

• This will open another window; click OK in order to execute SQL Server detach database successfully.

• Once the database is detached successfully, the window will close automatically and the database will not be listed in the Object Explorer. Once the database is detached, no database will be available and will appear like below;

• The detached files can be found in the system location. You will find the MDF file and the log file associated with this main database. These files can be moved to any other location as well.

We’re way there in the process of learning how to detach and attach database in SQL Server database. Now it’s time to know the file path & then attach method.

Path Location for SQL Database

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA

Manual Method to Attach SQL Database Easily

This method will describe a step-wise procedure to attach SQL database.

• In the same SQL Management Studio object explorer, right-click folder database and select the option of Attach to begin SQL Server attach database task.

• This will open a new window where you can click on Add in order to add database.

• Another window will open where you can browse for the location where the database file is stored. Here only the MDF (main database) has to be selected and the log file will be automatically added. Once selected, click OK.

• Click OK in next window which shows the added databases.

• You will be able to see the database afterwards to the object explorer.

Finally we know the steps to detach and attach SQL database without facing any errors. Let’s proceed further.

Error Handling While Attaching Database in SQL

#Problem: Attach Database Failed for Server Operating System Error 5 Access is Denied

While attaching the database below mentioned error: “Attach database failed for server Operating System error 5 access is denied” appears which might restrict you from attaching the database.

Reason: When you try to learn how to attach database SQL Server (SQL Server 2008R2) which was detached by another user or any person with different login, this error might generate. The two different logins for detach and attach operations is the root cause of such error to get generated. While detaching the database, the user becomes the owner of the database and thus in order to attach the database again you will have to attach with same login using which the database was detached.

Resolution: This error can be resolved if the database properties is changed and the login gets maximum Control to perform changes in the database. For this, go to the location of database and right-click on it. Click on Properties, and open Security tab. Here click on Add option and add the new login providing it full control. Perform the same procedure to LDF file as well and click OK.

#Problem: OperationFailed for Server Unable to Open the Physical File

Whileattaching the MDF file which has been moved from the default location, users may experience that operation forattach database failed for server microsoft.sqlserver.smo in SQL server. This might generate error like below;

 

Reason: When users detach any database from the SQL server and store the detached MDF file to another location (other than default), user might get some issues or error prompts while attaching the same database to the server. This happensdue to the Read/Write permissions of the hard disk drive where the database is present. This can result in attach database failed for server access denied error. Also, users must note that it is not common to expereince these errors while learning how to detach database SQL Server but while establishing a connection.

Resolution: Users can try to change the Read/Write permissions of the drive where they have the SQL MDF files. For this, right-click on the hard disk drive to which the MDF file is moved and saved. Select Properties>>Security>> here select Users and click on Edit and allow all the permissions by checking these options.

Conclusion

Finally, using the above ways, one can execute SQL detach database or SQL attach database operations. users must note that they can can detach multiple databases at a time. However,  they need to make sure to detach all of the files like MDF, NDF, & even LDF completely.

For instance; if user is trying to detach 2 databases at once, there will be total four files: 2 databases and 2 log files. Moreover, we suggest users to perform the steps under observation of network administrator or database administrator.