Moving Databases with the ALTER Command

  • John Mitchell-245523

    SSC Guru

    Points: 148434

    Thanks for the article, Jimmy. One thing you might want to consider is that if you have large data files with small amounts of data in them, backup and restore may be the quicker option. This is because backup and restore will only move the data, whereas detach-move-attach will move the whole data files, including empty space. I know that the restore process will also need to create the data file in the new location, but in a test I did on a single disk PC, it took more than 100 times longer to copy a 10GB database file into a new location than it did to create the file in the first place.

    John

  • the sqlist

    SSCrazy

    Points: 2398

    What hapens to the index files (NDF) if they exist?

    Don't just give the hungry man a fish, teach him how to catch it as well.

    the sqlist

  • imSQrLy

    Ten Centuries

    Points: 1335

    the sqlist (8/27/2009)


    What hapens to the index files (NDF) if they exist?

    The primary and secondary data files are moved as well, to the same location. If you need them to go on different disks you would need to modify the script.

    Jimmy

    "I'm still learning the things i thought i knew!"
  • thehumansponge

    Grasshopper

    Points: 17

    Found an issue with this script, if a Database is off-line ... it will be brought back online without moving the mdf and ldf files.

  • computercio

    Grasshopper

    Points: 11

    Does this move indexes too?

Viewing 5 posts - 16 through 20 (of 20 total)

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