1

I just built an Umbraco website. This uses a not-that-small SQL Server db. I copied this DB using the export data function built in in SQL Server 2008.

This copies all the tables and their content but it sets (for the one table i know) the Identity(1,1) column to "int". This way my umbraco installation fails over this.

As I tried to change the column type to identity, obviously I can't edit the columns. Even if there was a way to do this manually, I'd still have to change most of the tables in the new database.

Is there some option or so I missed when copying? I'd like to make it copy with the included identity.

I tried backup, and restore but my SQL server edition are not exactly the same. The one is x86 and the other is x64, which is giving me compatibility issues.

Thanks in advance!

Improve this question
4
  • 2
    IDENTITY(.., ..) is a property, not a data type. Take a look at your original database and look at the table definition. When you say the "export data function", what exactly do you mean? Did you just script out the whole database and use the option to include schema and data?
    – Thomas Stringer
    Commented Mar 20, 2012 at 12:20
  • I did it as following: The original database is running on my local SQL Server. I connected to the database engine locally usnig SQL Management Studio. Then I connected (locally) to my remote SQL server using a VPN connection. NExt, I right clicked my original database and chose "Tasks -> Export Data". And then automatically "Copy data from one or more table or views".
    – Christophe De Troyer
    Commented Mar 20, 2012 at 12:22
  • (Took too long to edit) And I did this TO an empty table with the same name. The column (datatype?) is "id(PK, int, not null). And in my replicated database the column is like this: "id(int, not null).
    – Christophe De Troyer
    Commented Mar 20, 2012 at 12:32
  • AFAIK there are no compatability issues restoring a database from an x86 server to an x64 server or vice versa. What you can not do is go backwards in versions - meaning you can not restore a 2008 R2 database to 2008 or 2005, etc and so on down the line.
    – Henry Lee
    Commented Mar 20, 2012 at 14:40

1 Answer 1

Reset to default
2

If you want to copy a DB from A to B, use the backup and restore commands. This might be easier for what you need to do.

If you want to export the tables: during the export process at the Select Source Tables and Views screen, click on the Edit Mappings button, then check the Enable identity insert check box.

Improve this answer
1
  • Thank you very much StanleyJohns, this is exactly what I've had missed! The tables replicated properly at first look! :) Thanks!
    – Christophe De Troyer
    Commented Mar 21, 2012 at 8:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.