Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2004
    Posts
    10

    Unanswered: Transferring SQL server DB

    I appreciate this may appear to a bit of a noob question but bear with me!

    I'm having a problem when I transfer my locally developed MS SQL server db to my hosts server.

    I am logging into the host server and then importing the tables and data from my local machine. All the tables and data then get transferred alright but the ID colums lose the primary key and identity setting and any default values in the table structure also get lost.

    This means I then have to go through each table on the host server an put things straight. A pain in the bum... I've tried creating the table structure using a SQL script which keeps the ID colum but not the default values.

    Why is this happening and how can I resolve it?

    Thanks in advance

    Phil

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd suggest that you script out the schema from the source database (you can get detailed steps from another thread), then play that script into the target database, then copy your data. As a convenience feature, you might want to comment out the foreign key constraints until after you transfer the data. If you don't, then you have to figure out the table's data dependancy order, which can be ugly.

    -PatP

  3. #3
    Join Date
    Dec 2003
    Location
    Delhi INDIA
    Posts
    58
    you can use copy database wizard... or backup restore if it is a db level movement not table level.
    Deep..

    MCDBA
    ------------------------------------------
    How much data you can afford to lose??

  4. #4
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    To use CDW ensure the login used does have required privilege on both the machines.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  5. #5
    Join Date
    Apr 2004
    Posts
    10
    Quote Originally Posted by Pat Phelan
    I'd suggest that you script out the schema from the source database (you can get detailed steps from another thread), then play that script into the target database, then copy your data. As a convenience feature, you might want to comment out the foreign key constraints until after you transfer the data. If you don't, then you have to figure out the table's data dependancy order, which can be ugly.

    -PatP

    Hey Pat,

    Thanks for the detailed reply and thread. Just quickly created a script on my local and all seems good. I'm going to transfer it up to my host later today. I can connect to their DB through ent. manager or query analyzer so this should work.

    The only point in you post I'm not 100% is the foreign constraints point. What are they and how will I know if I have any? I did warn you I'm a noob with MS SQL but we all started somewhere!


    Everyone else, thankyou for the replies, I'm going to try out Pat's solution but I may be back if it goes pear shaped!

  6. #6
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Run sp_helpconstraint for a table from query analyzer to get list of constraints or INFORMATION_SCHEMA.CHECK_CONSTRAINTS can help you to get the list.

    Refer to books online for the information on above topics.

    HTH
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by simplycfhost
    The only point in you post I'm not 100% is the foreign constraints point. What are they and how will I know if I have any? I did warn you I'm a noob with MS SQL but we all started somewhere!
    If you don't know that you've used a FOREIGN KEY constraint or what one is, it is unlikely that you've used one. The simple way to check is to look for the string FOREIGN in the script.

    As a side issue, if you aren't using FOREIGN KEY declarations, you probably aren't using any kind of referential integrity. These are quite important as your database gets larger, they'll save lots of your hair!

    For more information, check out any of the good database books, or for an overview simply Google the phrase "Third Normal Form" or "3NF". Visit the Database Concepts & Design forum here.

    -PatP

Posting Permissions

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