Perry Whittle (2/13/2013)
Jason Shadonix (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.
[/Quote]
No, I was not joking. The fact that you have to type out "CREATE DATABASE ... FOR ATTACH" instead of sp_attach_db to attach a database is a minor syntax change which alone isn't a reason to scrap the Detatch/Attach approach.
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.
Ah ha! Finally something you mention that actually explains some cases where you would have an advantage by not detaching/attaching a database. That's all I was looking for. So if you are not dealing with a replicated database, it probably doesn't matter that much I guess. (But I don't think you can offline a database participating in mirroring or with snapshots can you?)
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 onlinebooks 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.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 😉
I never said I was still using sp_attach_db instead of CREATE DATABASE ... FOR ATTACH syntax. I was simply trying to get at what advantages the described method has over the Detatch/Attach method. No need to get snarky about it...I'm simply trying to learn what the differences are, not trying to question or be negative about your method.
The Redneck DBA