question

mtandao avatar image
mtandao 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.
sql-server-2008schemadata-importaws
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

mtandao avatar image mtandao commented ·
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)
0 Likes 0 ·
Dave Morrison avatar image Dave Morrison commented ·
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
0 Likes 0 ·
mtandao avatar image mtandao commented ·
Thanks Dave . was able to use both SSMS and Red Gate SQL Compare to copy the schema and data. When using SSMC used Dave Morrison comments to generate scripts.
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
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.
0 Likes 0 ·
JohnM avatar image
JohnM answered
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!
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sp_lock avatar image
sp_lock answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Dave Morrison avatar image
Dave Morrison answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.