How do I copy a SQL database?

  • What is the easiest way to copy a complete SQL database, data and structure?

  • DTS, Data Transformation Services. Use the import and export wizard and it will walk you through all the details. -JG


    -JG

  • If there is ability to shut DB down then:

    1.detach from orginal server

    2.copy file(s) to new server

    3.attach_db on new server

    4.attach_db on original server

  • Oleg,

    I do have access to the server and can shut it down. What are the specific steps in doing what you suggest?

    Once I detach db what files do I copy?

  • Backup from current location and restore to new location.

    Should be all three options in three posts.

    David

    David

    @SQLTentmaker

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

  • I tried backing up and restoring. But for some reason it would not let me restore to a different folder/structure. It insisted that I replicate the folder structure (which I couldn't) for it to restore.

  • You have to use the "with move" option. However, if you can detach as Oleg has offered, then the files you would look for would be the .MDF and .LDF files associated with the database. If you right click on the database in EM and go to properties, you can select the "Data Files" tab and "Transaction Log" tab to find name and location.

    Hope this helps.

    David

    David

    @SQLTentmaker

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

Viewing 7 posts - 1 through 6 (of 6 total)

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