Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
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 Mar 20 '12 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 Mar 20 '12 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 Mar 20 '12 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 Mar 20 '12 at 14:40
add comment

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
Thank you very much StanleyJohns, this is exactly what I've had missed! The tables replicated properly at first look! :) Thanks! –  Christophe De Troyer Mar 21 '12 at 8:12
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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