Jason Shadonix (2/12/2013)
Question - What advantages does this method have over the detatch/reattach method of moving files around?
It's the supported way of moving database files and doesn't involve dropping the database from the system catalogs.
SP_ATTACH_DB is deprecated and will be removed in a future version of SQL Server, you should use CREATE DATABASE ... FOR ATTACH to attach database files to an instance of SQL Server. The ALTER DATABASE command is used to alter database configurations.
Jason Shadonix (2/12/2013)
I've always just detatched/reattached whenever I move a database, and the time consuming part is always waiting for the file to transfer, not the detatch/reattach process, which has always been pretty fast for me. It seems like there are more ways to make a mistake using the method described in this article. Or am I missing something?
Yes, often the file copy process can take some time. If you're smart you'll copy the files first then and only then remove the originals once the database comes online successfully using the new paths.
As long as you pay attention to what you're typing as part of the ALTER DATABASE ... MODIFY FILE command there's no reason why it should fail.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉