Home Forums Article Discussions Article Discussions by Author Discuss content posted by Perry Whittle Moving Database Files in SQL Server RE: Moving Database Files in SQL Server

  • February 13, 2013 at 12:56 pm

    #1587636

    Jason Shadonix (2/13/2013)

    But that's just a minor syntax change in the process. [/Quote]

    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.aspx%5B/quote%5D

    This below from MS books online

    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.

    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 😉

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉