Moving Database Files Detach/Attach or ALTER DATABASE?

  • Jonathan Kehayias

    One Orange Chip

    Points: 26778

    David Benoit (5/27/2009)


    Jonathan Kehayias (5/27/2009)


    Brian Munier (5/27/2009)


    Interesting article, thank you. My question is if detach/attach is going away, and I want to move a database from one server to another server does that mean I now have to use backup/restore which can be slower and require more effort to work?

    You should use CREATE DATABASE [databasename] FOR ATTACH as shown in the quote from the Books Online. This has a much more verbose syntax and allows for the options needed to be configured while the database is created to include, who the owner should be, one of the other problems listed in the comments here.

    ...and there is a great script for this HERE[/url]

    Excellent script David. Thanks for pointing that out.

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • GRE (Gethyn Ellis)

    SSCrazy Eights

    Points: 9582

    GRE (5/27/2009)

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

    Have i missed a trick here? I understand that the old way to attach a database is be depreciated, But is SP_Detach_DB bein decreciated too? or will that remain?

    Yes, both the attach and detach stored procedures are deprecated, here is the page for sp_detach_db from Books Online (see the Important box just after the syntax section):

    http://msdn.microsoft.com/en-us/library/ms188031.aspx

    Yes i saw that....but It offers no alternative,like the SP_Attach_Db BOL entry does, so if both these sp are being depreciated, how are you going detach a db in the first place to warrant attaching it using this new script?

    Gethyn Elliswww.gethynellis.com

  • GRE (Gethyn Ellis)

    SSCrazy Eights

    Points: 9582

    Noel McKinney (5/27/2009)


    GRE (5/27/2009)


    Have i missed a trick here? I understand that the old way to attach a database is be depreciated, But is SP_Detach_DB bein decreciated too? or will that remain?

    Yes, both the attach and detach stored procedures are deprecated, here is the page for sp_detach_db from Books Online (see the Important box just after the syntax section):

    http://msdn.microsoft.com/en-us/library/ms188031.aspx%5B/quote%5D

    Yes i saw that....but It offers no alternative,like the SP_Attach_Db BOL entry does, so if both these sp are being depreciated, how are you going detach a db in the first place to warrant attaching it using this new approach?

    Gethyn Elliswww.gethynellis.com

  • Jorge Segarra

    SSCrazy

    Points: 2561

    Jonathan Kehayias (5/27/2009)


    jboc (5/27/2009)


    On occasion a log file will fill up to the problem point and we could use detach/attach to delete the old log and start over. Not something that we would normally want to do but something that was an option in an emergency. Is there a simple way to do that once detach/attach goes away?

    This should never be used as an option. It is essentially ripping the transactional heart out of your database. Despite being successful doing this numerous times in the past, it only takes once for the disasterous effects of doing this to ruin your day, job, career when the database fails to attach without its log.

    I'd recommend reading Paul Randal's article:

    http://technet.microsoft.com/en-us/magazine/cc895648.aspx

    and his blog posts on the Transaction Log:

    http://sqlskills.com/BLOGS/PAUL/category/Transaction-Log.aspx

    Specifically:

    http://sqlskills.com/BLOGS/PAUL/post/CHECKDB-From-Every-Angle-EMERGENCY-mode-repair-the-very-very-last-resort.aspx

    http://sqlskills.com/BLOGS/PAUL/post/BACKUP-LOG-WITH-NO_LOG-use-abuse-and-undocumented-trace-flags-to-stop-it.aspx

    When you attach without the log file, it is rebuilt, which can leave your database incosistent, not a good thing to do.

    Please please listen to Jonathan and Paul's advice on this. Do not EVER delete the transaction log like that. Recently I had to deal with that mess and Paul helped me through it. He wrote a blog post about it: http://www.sqlskills.com/BLOGS/PAUL/post/A-sad-tale-of-mis-steps-and-corruption-(from-today).aspx

    I highly suggest you read that post to understand why you never want to do this.

    =============================================================
    /* Backups are worthless, Restores are priceless */

    Get your learn on at SQL University!
    Follow me on Twitter | Connect on LinkedIn
    My blog: http://sqlchicken.com
    My book: Pro Server 2008 Policy-Based Management

  • don_goodman

    Say Hey Kid

    Points: 670

    It is a terrible practice to detach a database from a production server. If you had any corruption in any database file or in the server itself, you will never be able to re-attach it. I know from experience this is so.

    The CORRECT way to migrate a database and its files to a new location is through a manual backup and restore.

    Ensure you use INIT in the backup statement. Ensure you disconnect the users before restoring it.

    In this way you avoid business exposure to corruption. If it is corrupt, it will not restore. But the database will remain online for the business to use it while you and your team figure out what to do about the corruption. Then in an orderly, non-destructive way, you can migrate the business to a new solution, whatever that is.

    NEVER detach a production database unless you want to answer the business challenge with a job interview elsewhere.

  • Jonathan Kehayias

    One Orange Chip

    Points: 26778

    Brian Munier (5/27/2009)


    Interesting article, thank you. My question is if detach/attach is going away, and I want to move a database from one server to another server does that mean I now have to use backup/restore which can be slower and require more effort to work?

    Brian,

    I actually wrote another article on this exact topic that is pending publication here called "Use Backup/Restore to Minimize Upgrade Downtimes". It will be published sometime in the middle of next month, and details how to minimize the downtime for upgrades by using Backup/Restore. It might be a bit more involved, but the benefits far outweigh any additional efforts.

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • JJ B

    SSCertifiable

    Points: 5042

    What a great article. And the questions and answers in the discussion have been equally illuminating. Thanks.

  • turnegw1

    SSC Enthusiast

    Points: 130

    Jonathan,

    Would the method described in your article work if, instead of moving files to a different location, I wanted to change the underlying physical files for a given database? What would be the effect of using it instead of attach/detach?

  • Jonathan Kehayias

    One Orange Chip

    Points: 26778

    turnegw1 (5/27/2009)


    Jonathan,

    Would the method described in your article work if, instead of moving files to a different location, I wanted to change the underlying physical files for a given database? What would be the effect of using it instead of attach/detach?

    What do you mean by change the underlying physical files for a given database? Like renaming them or something else?

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • turnegw1

    SSC Enthusiast

    Points: 130

    I have a test database used by some third party software. I have two versions of it that contain vastly different data so that different scenarios can be tested. Currently for example, we detach the database when the physical file is a.mdf and attach the same database name with physical file b.mdf. I hope this explains.

  • Charles Kincaid

    SSChampion

    Points: 13593

    Great article. I was under the impression that ALTER DATABASE MODIFY FILE actually moved the file. You seem to indicate that you have to phyically move the files after issuing the comand.

    ATBCharles Kincaid

  • GRE (Gethyn Ellis)

    SSCrazy Eights

    Points: 9582

    don_goodman (5/27/2009)


    It is a terrible practice to detach a database from a production server. If you had any corruption in any database file or in the server itself, you will never be able to re-attach it. I know from experience this is so.

    The CORRECT way to migrate a database and its files to a new location is through a manual backup and restore.

    Ensure you use INIT in the backup statement. Ensure you disconnect the users before restoring it.

    In this way you avoid business exposure to corruption. If it is corrupt, it will not restore. But the database will remain online for the business to use it while you and your team figure out what to do about the corruption. Then in an orderly, non-destructive way, you can migrate the business to a new solution, whatever that is.

    NEVER detach a production database unless you want to answer the business challenge with a job interview elsewhere.

    I think there occaions when an deatach/attach is the best option especially when dealing with larger databases and you only have native SQL backup to use...Sometimes you don't have the neccessary disk space to take a backup and restore the database files (2x times the size of the database) expecially when migrating accross two servers where you can detach the db, copy the database files accorss to the new server and reattch there...If you are worried about corruption run a DBCC CHECKDB before you start.

    SQL Won't restore the database unless it has exclusive access to it...so if users are connected the restore will fail.

    Gethyn Elliswww.gethynellis.com

  • Jonathan Kehayias

    One Orange Chip

    Points: 26778

    Charles Kincaid (5/27/2009)


    Great article. I was under the impression that ALTER DATABASE MODIFY FILE actually moved the file. You seem to indicate that you have to phyically move the files after issuing the comand.

    Yes,

    You issue the ALTER DATABASE commands. Then take the database OFFLINE. Physically Move the files to the location specified in the ALTER DATABASE commands, and then bring the database back ONLINE. It doesn't move the files automatically for you.

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • DouglasAcosta

    Ten Centuries

    Points: 1052

    Great article!!

    For production environments it is recommended to do the most effort to accomplish a database moving without losing any of its settings (security, replication, backup profiles...) this means more work also, but what about our development/test environments these usually have a few or not at all "complex" configuration so in my opinion it is necessary to have t-sql instructions like "sp_detach / attach" and others to allow faster tasks inside these environments without much worry about what happens behind the scenes.

    Again in the test/development environment I am very used to use SqlServer Management studio to accomplish all management tasks, I think if this processes become obsolete or deprecated means these "common tasks" using Management Studio will be off also?

    Best regards

  • Jonathan Kehayias

    One Orange Chip

    Points: 26778

    turnegw1 (5/27/2009)


    I have a test database used by some third party software. I have two versions of it that contain vastly different data so that different scenarios can be tested. Currently for example, we detach the database when the physical file is a.mdf and attach the same database name with physical file b.mdf. I hope this explains.

    I haven't actually tested it, but you should be able to use ALTER DATABASE to change the filenames out, then take the database OFFLINE and bring it ONLINE and have it switch files out without problems. Something to test. I'll post back here in a bit.

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

Viewing 15 posts - 16 through 30 (of 59 total)

You must be logged in to reply to this topic. Login to reply