Moving Database Files Detach/Attach or ALTER DATABASE?

  • Jonathan Kehayias

    One Orange Chip

    Points: 26667

    Comments posted to this topic are about the item Moving Database Files Detach/Attach or ALTER DATABASE?

    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]

  • Lasse Schioettz

    Right there with Babe

    Points: 739

    Hey Jonathan,

    Good article. A couple of minor things to mention, though. To detach the database you use "sp_detach_db" - not "sp_attach_db". It may also be worth noting that the physical_name you look up in the master db is the current file path and the FILENAME argument to the ALTER DATABASE MODIFY FILE is the path to the new location.

    Sincerely,

    Lasse

  • gabriele.valerio

    SSC Rookie

    Points: 39

    Hi Jonathan,

    it seems to me that another problem with detach/attach is that you potentially lose the original db owner. If I am right this is another good reason to use ALTER DATABASE.

    Regards,

    Gabriele Valerio

  • dmajkic

    SSC Enthusiast

    Points: 103

    If sp_attach_db is to be deprecated, what is "the new" way to just attach a database?

    BTW:

    Is it possible to add a few xp calls to do file copy on server, and make moving automatic?

  • Brian Munier

    SSCrazy

    Points: 2765

    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?

  • sejal p gudhka

    SSCrazy

    Points: 2042

    Hi All,

    One more thing to add to this is that if you want to move TEMPDB database to different location, Alter DAtabase is only option you have.

    You need to restart SQL Server services in order for it to affect.

  • Jonathan Kehayias

    One Orange Chip

    Points: 26667

    dmajkic (5/27/2009)


    If sp_attach_db is to be deprecated, what is "the new" way to just attach a database?

    BTW:

    Is it possible to add a few xp calls to do file copy on server, and make moving automatic?

    This is covered in the BOL reference quoted in the article:

    This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. We recommend that you use CREATE DATABASE database_name FOR ATTACH instead. For more information, see CREATE DATABASE (Transact-SQL).

    You could do xp calls to copy the files, you want to open xp_cmdshell up on your server which is a security risk, that is why it is disabled by default. I don't recommend that you do it, and I wouldn't do it myself personally.

    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]

  • Jonathan Kehayias

    One Orange Chip

    Points: 26667

    Lasse Schioettz (5/27/2009)


    Hey Jonathan,

    Good article. A couple of minor things to mention, though. To detach the database you use "sp_detach_db" - not "sp_attach_db". It may also be worth noting that the physical_name you look up in the master db is the current file path and the FILENAME argument to the ALTER DATABASE MODIFY FILE is the path to the new location.

    Sincerely,

    Lasse

    Thanks, bad copy/paste when I was working on this. I'll submit a correction to Steve for that.

    The physical_name is not necessarily the current file path. It is the location that SQL will look for the file at startup. If you issue an ALTER DATABASE MODIFY FILE, and then pull the physical_name it will reflect the changes, even if the database hasn't been taken offline, and the files haven't been moved yet.

    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]

  • Jonathan Kehayias

    One Orange Chip

    Points: 26667

    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.

    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: 9580

    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?

    Gethyn Ellis
    www.gethynellis.com

  • jboc

    SSC Rookie

    Points: 46

    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?

  • David Benoit

    SSC-Dedicated

    Points: 34562

    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]

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • Noel McKinney

    Hall of Fame

    Points: 3377

    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

  • Brian Munier

    SSCrazy

    Points: 2765

    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]

    Thanks,

    I think my colleague uses this script all the time.

    Brian

  • Jonathan Kehayias

    One Orange Chip

    Points: 26667

    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.

    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 - 1 through 15 (of 59 total)

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