Moving Databases with the ALTER Command

  • 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.


  • 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

  • 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.


    "I'm still learning the things i thought i knew!"
  • 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.

  • Does this move indexes too?

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

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