This is to correct some information that I had put out there in this article. From reading books online, it appears that my information was intended only for SQL Server 2005 databases. Talking with Robert Davis (Blog | Twitter) at work, I was advised that my earlier article was in correct and sure enough when I tested with one of my personal databases; it did exactly as he warned me. I also want to thank him for proof reading this article for me as well. I am blessed to know him and have adopted him as my mentor.
“Don’t use detach and attach to move database files. It’s risky. If the database doesn’t shut down cleanly, you won’t be able to reattach it. The proper way to move database files is detailed in Books Online.”
The proper article for moving databases in SQL 2008 and later is documented here that Books Online will point you to as well.
Then as I reached out for help on Twitter, I got some great advice here from David Levy. (Blog | Twitter) I modified this script to place comments in it, so I could explain what is going on, as it is a script that generates a script to actually move the files. I like it because it gives you a chance to see what you have BEFORE you do anything.