asked
need to copy Schema and Data from local server to AWS RDS
I have SQL Server Express running a DB in a named instance for an off the shelf application. I want to move the DB to an Amazon Web Service RDS SQL Express instance. Unfortunately AWS does not allow you to do a restore from file copy. I tried using Data-tier application migraation and Export/Import process but none of them were succesfful. When you use the Export/Import, it does not copy over the schema and tried using SQLAzureMW as well but not successful. Can anyone recommend the best way or tool to copy the schema and data in this scenario.
I generated the scripts and ran them but keep getting the following error when trying to do the data export - Validating (Error) Messages Error 0xc0202049: Data Flow Task 1: Failure inserting into the read-only column "RecId". (SQL Server Import and Export Wizard) Error 0xc0202045: Data Flow Task 1: Column metadata validation failed. (SQL Server Import and Export Wizard) Error 0xc004706b: Data Flow Task 1: "Destination 3 - Asset" failed validation and returned validation status "VS_ISBROKEN". (SQL Server Import and Export Wizard) Error 0xc004700c: Data Flow Task 1: One or more component failed validation. (SQL Server Import and Export Wizard) Error 0xc0024107: Data Flow Task 1: There were errors during task validation. (SQL Server Import and Export Wizard)
I'd imagine that recid is an identity column if you use the set identity_insert on it should work. You'll need to do this for each table with an identity column and remember to do a set identity_insert off at the end too
You have some good answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
Don't know if this is an option, but it looks like the Red Gate compare products can deploy to SQL Azure. Schema -
https://www.red-gate.com/products/sql-development/sql-compare/ Data -
https://www.red-gate.com/products/sql-development/sql-data-compare/ Might be well worth the cost to do it right. Or you could see if the free trial will suit your needs. Hope that helps!
Have you tried the following - Script all the schema objects using the SSMS wizard - Run then script on the Amazon istance - Use Import/Export wizard to move the data to the new objects. While writing I thought I would google.. Amazon have a guide [here][1], it also guides you through using BCP. [1]:
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.html
Simplest way is probably to use "Generate Scripts" within management studio. On the third tab "Set scripting options", click the advanced button and then, depending on the version of management studio you're using, set either the option; "script data" to yes or "types of data to script" to schema and data This will put the schema out and produce a bunch of insert statements for the data
22 People are following this question.
