Target Audience: Beginners
Keywords: SQL Server, Attaching Databases
This post is not related to SharePoint directly but yes indirectly it is still related to SharePoint. I do lots of applications for SharePoint. I create custom built solutions in .NET for SharePoint. I don't like the back up utility of SQL Server. It is not reliable as it misses several stored procedures when restoring. Although it does not happen always but no body likes to take chances so i usually back up my database by detaching the db and saving the .MDF file to the back up folder. I also use the same mechanism for replicating the DB. Some times when you get stuck when attaching the detached DB. For example, consider this scenario. I want to replicate a DB. I detach the original DB and make a copy of it and rename it. I attach the original DB without any problem but when i try to attach the renamed DB copy, I get an error. This is not a technical solution to a problem as problem itself does not exist :) This is a very simple tip but many users will find it very useful. Believe me. When you are in a hurry to do a replication and could not find the solution, this tip will really come in very handy. Anyway, here are the steps involved:
1. Right click Databases. All Tasks > Attach Database.
2. Browse to find the renamed DB file (the MDF file).
3. Under "Current File(s) Location", double click the file name and change it to the new name. The box shows you the original MDF file name even though you have renamed it. Rename it and DO NOT HIT THE ENTER BUTTON. Instead, use your mouse and click somewhere in the second row. When you hit the enter button, SQL Server does not accept your change and reverse the file name back to the original file name but when you move your focus away from first row to second row by simply clicking the mouse, this problem does not occur.
4. "Attach As" also will still be showing the original file name. Type in the new name that you want to give to your database.
5. Specify the database owner and you are done.
Very simple! What's the big deal in it? Yes, what's the big deal in it? If you are a senior DBA reading this post and laughing and thinking why i am posting such a simple thing then let me tell you you were not the intended audience for this post. This happens with me very frequently. People criticize me for posting simple solutions but let me tell you that my goal is to write for every one, not just for the senior people. Most of the emails that i daily receive are from the young people are still learning the tricks of the trade. We can not ignore them or move away instead of helping them. Well, it gives me pleasure when a senior person appreciates my work but it equally pleases me when a beginner takes advantage of my simple tips and praises my work and thank me for writing it. So, getting back to the tip, here is one final note. If you get an error while attaching the DB without any apparent reason, see if your SQL query analyzer is open and you are accessing the DB in it. Some times, it happens that you are working in Query Analyzer, you leave the session open and detach the DB, for any purpose, be it taking backup or replication, when you try to re-attach the DB, you get an error telling you that attaching failed!! You don't see any reason why your attaching failed. This happens because of the open sessions in your Query Analyzer. Close the session by closing the Analyzer and try attaching the DB again.
Visit the following link to see how to attach a DB using T-SQL:
-SSA
Keywords: SQL Server, Attaching Databases
This post is not related to SharePoint directly but yes indirectly it is still related to SharePoint. I do lots of applications for SharePoint. I create custom built solutions in .NET for SharePoint. I don't like the back up utility of SQL Server. It is not reliable as it misses several stored procedures when restoring. Although it does not happen always but no body likes to take chances so i usually back up my database by detaching the db and saving the .MDF file to the back up folder. I also use the same mechanism for replicating the DB. Some times when you get stuck when attaching the detached DB. For example, consider this scenario. I want to replicate a DB. I detach the original DB and make a copy of it and rename it. I attach the original DB without any problem but when i try to attach the renamed DB copy, I get an error. This is not a technical solution to a problem as problem itself does not exist :) This is a very simple tip but many users will find it very useful. Believe me. When you are in a hurry to do a replication and could not find the solution, this tip will really come in very handy. Anyway, here are the steps involved:
1. Right click Databases. All Tasks > Attach Database.
2. Browse to find the renamed DB file (the MDF file).
3. Under "Current File(s) Location", double click the file name and change it to the new name. The box shows you the original MDF file name even though you have renamed it. Rename it and DO NOT HIT THE ENTER BUTTON. Instead, use your mouse and click somewhere in the second row. When you hit the enter button, SQL Server does not accept your change and reverse the file name back to the original file name but when you move your focus away from first row to second row by simply clicking the mouse, this problem does not occur.
4. "Attach As" also will still be showing the original file name. Type in the new name that you want to give to your database.
5. Specify the database owner and you are done.
Very simple! What's the big deal in it? Yes, what's the big deal in it? If you are a senior DBA reading this post and laughing and thinking why i am posting such a simple thing then let me tell you you were not the intended audience for this post. This happens with me very frequently. People criticize me for posting simple solutions but let me tell you that my goal is to write for every one, not just for the senior people. Most of the emails that i daily receive are from the young people are still learning the tricks of the trade. We can not ignore them or move away instead of helping them. Well, it gives me pleasure when a senior person appreciates my work but it equally pleases me when a beginner takes advantage of my simple tips and praises my work and thank me for writing it. So, getting back to the tip, here is one final note. If you get an error while attaching the DB without any apparent reason, see if your SQL query analyzer is open and you are accessing the DB in it. Some times, it happens that you are working in Query Analyzer, you leave the session open and detach the DB, for any purpose, be it taking backup or replication, when you try to re-attach the DB, you get an error telling you that attaching failed!! You don't see any reason why your attaching failed. This happens because of the open sessions in your Query Analyzer. Close the session by closing the Analyzer and try attaching the DB again.
Visit the following link to see how to attach a DB using T-SQL:
No comments:
Post a Comment