Post reply

Migrating data from one server to another

  • June 27, 2018 at 11:58 pm

    #371336

    Hi

    Is there any way other than export and import where we can migrate data from one server to another.

    Thanks

  • June 28, 2018 at 12:46 am

    #1995558

    Backup and restore or
    insert into {TableName} select * from {TableName} using a linked server or
    SSIS

    There isn't much information on your post but these are the obvious methods.

    Thanks

  • June 28, 2018 at 1:00 am

    #1995559

    NorthernSoul - Thursday, June 28, 2018 12:46 AM

    Backup and restore or
    insert into {TableName} select * from {TableName} using a linked server or
    SSIS

    There isn't much information on your post but these are the obvious methods.

    Thanks

    Actually there is no linked server connection. I am trying to export but it is failing due to identity column.
    I am not finding the option Identity Insert On in Edit Column settings. Don't know why.

  • June 28, 2018 at 1:09 am

    #1995561

    How much data are you migrating? 
    Complete Database or only certain tables or rows?

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

  • June 28, 2018 at 1:22 am

    #1995563

    Its for 20 tables and the total data comes to around 5000 rows. I tried generating script from the tasks and got the script.

  • June 28, 2018 at 1:32 am

    #1995564

    KGNH - Thursday, June 28, 2018 1:22 AM

    Its for 20 tables and the total data comes to around 5000 rows. I tried generating script from the tasks and got the script.

    Why don't you want to use import and export? It's not a large number of rows so I don't imagine it would take a long time.

    Thanks

  • June 28, 2018 at 1:44 am

    #1995565

    NorthernSoul - Thursday, June 28, 2018 1:32 AM

    KGNH - Thursday, June 28, 2018 1:22 AM

    Its for 20 tables and the total data comes to around 5000 rows. I tried generating script from the tasks and got the script.

    Why don't you want to use import and export? It's not a large number of rows so I don't imagine it would take a long time.

    Thanks

    Actually  initially I tried with exporting only but getting error since there are identity columns in most of the tables. I do not find the option Identity Insert On in Edit Column settings.

  • June 29, 2018 at 5:23 am

    #1995767

    You can use IDENTITY_INSERT to allow inserts into identity columns

    SET IDENTITY_INSERT [ [ database_name . ] schema_name . ] table { ON | OFF }

    See https://docs.microsoft.com/en-us/sql/t-sql/statements/set-identity-insert-transact-sql?view=sql-server-2017 for a full explanation

    --
    Scott

  • July 3, 2018 at 7:43 am

    #1996142

    KGNH - Thursday, June 28, 2018 1:00 AM

    NorthernSoul - Thursday, June 28, 2018 12:46 AM

    Backup and restore or
    insert into {TableName} select * from {TableName} using a linked server or
    SSIS

    There isn't much information on your post but these are the obvious methods.

    Thanks

    Actually there is no linked server connection. I am trying to export but it is failing due to identity column.
    I am not finding the option Identity Insert On in Edit Column settings. Don't know why.

    Are you sysadmin?

    What you can try is dropping the table on the destination server then exporting the data there and it will recreate the table during the export. Or do as Scott suggests and run the T-SQL on the destination server prior to exporting to it. Just don't forget to turn Identity_Insert back off when you're done or you won't be able to use it again for another table. SQL has this weird thing (or did) where this option can only be turned on for one table at a time.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • July 4, 2018 at 3:26 am

    #1996262

    Thank you all for your support

Viewing 10 posts - 1 through 9 (of 9 total)

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