Perry Whittle (2/13/2013)

---

You're joking right? Create and alter database have been enhanced extensively to include extra commands (file management, mirroring, etc). Previously, under 2000 detach\attach was the only way to move files around.

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

Mirrored, snapshotted and replicated databases spring to mind here, they cant just be detached.

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.aspxThis below from MS books online



You may want to read this and this.

Phone MS support and tell them you're having trouble attaching a db using SP_ATTACH_DB, i guarantee I know their response

Perry Whittle (2/13/2013)

---

I'm not being snarky that comment is a subtle hint.

The old

EXEC sp_attach_db @dbname = N'AdventureWorks2012', @filename1 = 
N'C:\Somepath\AdventureWorks2012_Data.mdf', @filename2 = 
N'C:\Somepath\AdventureWorks2012_log.ldf';

The new

CREATE DATABASE database_name 
ON PRIMARY (name=mydb_data, filename='d:\somepath\mydbdata.mdf')
LOG ON (name=mydb_log, filename='e:\somepath\mydblog.ldf')
FOR ATTACH

Jason Shadonix (2/13/2013)

---

Right...but the mere fact that you have to type in slightly different syntax to attach a database isn't by itself an argument to stop detatching and attaching databases. Not sure why you keep zeroing in on that.

Perry Whittle (2/13/2013)

---

You're the one who keeps zeroing in on syntax not me, you're missing the whole point of the article with your blind devotion to an ancient stored procedure. You dont have to detach the db or stop the sql server service to move the data files as i have seen suggested many times before on the forums.


You cant just detach a mirrored or replicated db and as already mentioned or linked things like service broker, owner and backup history meta are lost.

george sibbald (2/13/2013)

---

Jason, for an article on the benefits of alter database method and why it is the preferred method for SQL2005 onwards see here

I would add another reason, with detach\attach the owner of the database can be changed, using alter database that will not happen.

In a nutshell any metadata about a database held in master is lost when it is detached, but not when alter database is used