Recently I just come across with an error while I was attaching and detaching sql server database so gathered some point for everyone and surely they will help them.
If you are having issues in attaching and detaching sql server databases so there are number of reasons behind it you need to fix and troubleshoot them one by one.
The first step probably that you are detaching the database from sql server when you click on database and tap into task>detach then a dialog box will open saying database name and in the last column you would see a “link message” saying users connected with database so you can ask these users to disconnect from database or you can follow other procedure to disconnect users from the same database by force.
Simple option to disconnect users by force is to click drop connection option from the same dialog and click "ok" this will drop all connected users from database and you can do detaching database.
Another way include you can run scripts for detaching database, and you also can tap into activity monitor utility which is available when you click on sql server node and right click from sql server management studio let the utility load and tap into processes and select your database name and see activity details and processes by right clicking on the process from there.
If you are attaching database and facing issues so there are number of steps you can follow:
If you find error like the following:
Error Question:
______________________________________________
Error while opening mdf file
there is no editor for “path for your database and file”
Make sure application for the file type is installed
______________________________________________
Error Answer:
The above error says – you have tried opening database file .mdf without SSMS.
Actually mdf file can be browsed when you will attach it to sql server database so you cannot just open it, first you need to attach this mdf file to database using sql server management studio.
Attaching mdf file to sql server
Load SQL Server Management Studio> Connect with sql server instance> select database and right click on it> click attach> browse your mdf file and click ok. You are done you would be seeing your database listed when you refresh the database node.
You may be getting access denied error while opening mdf file.
_____________________________________________________________
Attach database failed for server “server name” . (Microsoft.SQLServer.smo)
Unable to open physical file “file location mdf”. Operating system error 5: “5(Access is denied.)”.(Microsoft SQL Server, Error: 5120)
_____________________________________________________________
Mostly there are permission issues when you are having error while attaching database to SQL server. This error also a permission issue on OS level.
Final and super option to fix this permission issue instead of going through number of other options and trying other credentials you just copy that mdf and ldf file from data folder of sql server to other location and try connecting them from other location , if mdf file is attached so you can copy back the mdf file to its location means data folder which is default folder for all databases by SQL server.
The above practice is based on SSMS 2008 R2.
If you are having issues in attaching and detaching sql server databases so there are number of reasons behind it you need to fix and troubleshoot them one by one.
The first step probably that you are detaching the database from sql server when you click on database and tap into task>detach then a dialog box will open saying database name and in the last column you would see a “link message” saying users connected with database so you can ask these users to disconnect from database or you can follow other procedure to disconnect users from the same database by force.
Simple option to disconnect users by force is to click drop connection option from the same dialog and click "ok" this will drop all connected users from database and you can do detaching database.
Another way include you can run scripts for detaching database, and you also can tap into activity monitor utility which is available when you click on sql server node and right click from sql server management studio let the utility load and tap into processes and select your database name and see activity details and processes by right clicking on the process from there.
If you are attaching database and facing issues so there are number of steps you can follow:
If you find error like the following:
Error Question:
______________________________________________
Error while opening mdf file
there is no editor for “path for your database and file”
Make sure application for the file type is installed
______________________________________________
Error Answer:
The above error says – you have tried opening database file .mdf without SSMS.
Actually mdf file can be browsed when you will attach it to sql server database so you cannot just open it, first you need to attach this mdf file to database using sql server management studio.
Attaching mdf file to sql server
Load SQL Server Management Studio> Connect with sql server instance> select database and right click on it> click attach> browse your mdf file and click ok. You are done you would be seeing your database listed when you refresh the database node.
You may be getting access denied error while opening mdf file.
_____________________________________________________________
Attach database failed for server “server name” . (Microsoft.SQLServer.smo)
Unable to open physical file “file location mdf”. Operating system error 5: “5(Access is denied.)”.(Microsoft SQL Server, Error: 5120)
_____________________________________________________________
Mostly there are permission issues when you are having error while attaching database to SQL server. This error also a permission issue on OS level.
Final and super option to fix this permission issue instead of going through number of other options and trying other credentials you just copy that mdf and ldf file from data folder of sql server to other location and try connecting them from other location , if mdf file is attached so you can copy back the mdf file to its location means data folder which is default folder for all databases by SQL server.
The above practice is based on SSMS 2008 R2.
