SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Aug 2004
    Location
    CA
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Moving a SQL Server (via Enterprise Manager) From one server to another

    If I merely try to export from one to another it doesn't move things like key's and identity. Is there a quick way to move the tables (or data) quickly from one server to another?

    I figured out how to generate SQL script however it only gives me the table info and not the insert Data.

    Thank you so much in advanced

  2. #2
    SitePoint Addict danfran's Avatar
    Join Date
    Jan 2005
    Location
    New York City
    Posts
    244
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Zod,

    You can't really "move a SQL Server", technically. (Well, by "sneakernet" you can.. ) -You can move their DATABASES, though. I presume this is what you're talking about..

    The best way to do this is to BACK UP the database to a file, then FTP or email it to the other server. (do not zip the file)

    When the backup file is on the other server, create a blank database, name it what you want, then restore your backup file over your new, blank database. This is how we all do it! (Make sure the checkbox "Force Restore Over Existing Database" is checked and do not focus your new, blank database with the mouse cursor or you will get error messages when you try to restore because focusing the database with your mouse "opens" it, thereby preventing a restore.. because you are "using it"..)

    Scripting your db or going table-by-table with DTS (Import/Export - Data Transformation Services) is only relevant when you want to transfer a PIECE of your database to another server.


    BTW, when you [right-click] "generate sql script" in SQL Server Enterprise Manager, there are options (checkboxes) on the last tab that allow you to specify "foreign keys", "primary keys", "indexes", "users", etc...

    -Dan

  3. #3
    SitePoint Zealot fredmintah's Avatar
    Join Date
    Jun 2004
    Location
    Ghana
    Posts
    102
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What danfran is saying is right. You can also trying replicating data across the two machines involved in the is matter. Configure the one having the data as distributor and publisher and the one receiving the data as the subcriber and there you go.

  4. #4
    SitePoint Member
    Join Date
    Aug 2004
    Location
    CA
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you for your help.

    Is backup better then detach, sending over the 2 files, and attaching it over there?

  5. #5
    SitePoint Zealot fredmintah's Avatar
    Join Date
    Jun 2004
    Location
    Ghana
    Posts
    102
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I will say yes.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •