Yulingxu (2/11/2013)

---

Thanks Perry for this article.

I see MS SQL Server is more like Oracle Database to handle those physical files.

Thanks
Yuling

sismb8 (2/12/2013)

---

Nice article. Thanks! Introduction of the move file technique has been a welcome advance in SQL Server. In the bad old days (SQLServer 6.5/7.0) I used to add a file to a filegroup on the new disk location, set the quota on the other files to zero, let the DB daemon siphon all the data to the new file and then drop the old ones when empty. Clunky but it worked. The move is miles better.

kevaburg (2/12/2013)

---

Although this is a very good article with regards to moving user databases

kevaburg (2/12/2013)

---

what happens if you want to move the master database somewhere else?

Jason Shadonix (2/12/2013)

---

Question - What advantages does this method have over the detatch/reattach method of moving files around?

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?

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/13/2013)

---

But that's just a minor syntax change in the process.

Jason Shadonix (2/13/2013)

---

Between the two methods (which both accomplish the same thing as far as I can tell) - both will have essentially the same amount of down time as near as I can tell.

What disadvantages are there in dropping a database from the system catalogs and adding them again for a detatch/attach? All of your security/permisisons should be unchanges as long as you are keeping the database on the same server.

Jason Shadonix (2/13/2013)

---

Also, when you say "it's the supported way of moving database files"... Are you saying detatch/attach is not supported? I don't see anything to indicate that in MSDN with some quck searches: http://msdn.microsoft.com/en-us/library/ms190794.aspx

books online

---

However, we recommend that you move databases by using the ALTER DATABASE planned relocation procedure, instead of using detach and attach. For more information, see Move User Databases.