I came across a scenario where in I had to move database from SQL Server 2008R2 to SQL Server 2005. As most of you must be aware that
Detach/Attach and Backup/Restore doesn’t works in this case.  The only solution is to script out the database objects and data from SQL Server 2008R2 and then run the same on SQL Server 2005. However, this isn’t simple as it looks like. Moving all objects other than table and data is tedious task if the database has lots of foreign key relationships. In this case, the parent table should be created/populated before the child table are created/populate otherwise the creation/insertion terminates with below error

The INSERT statement conflicted with the FOREIGN KEY constraint “FK_Mykey”. The conflict occurred in database “mydb”, table “dbo.mytable”, column ‘mycolumn’.

The resolution to this is to first create and insert data in parent table and then create and populate the corresponding child tables. I came up with an automated solution using PowerShell to for the same. The process assumes that table belongs to default schema dbo.

The first part is to list down tables in the order of their relationship so that parent table is placed ahead of their child tables. This is done using a recurring CTE as shown below

The next part is to iterate through the result of above query and then create and populate the tables in their relationship order.

The below PowerShell code executes the above query and gets the result in DataReader.

The next step is to iterate through data reader results and create and copy table data.

The above code initializes a SMO object $smoserver and then the scripter object $oScripter to get the table script.  The table script
is then passed to Execute-Query function which executes the script on destination database and server. The code for function Execute-Query is given below.

The function Copy-TableData copies the table data from source to destination. It takes 5 arguments, the source and destination connection details and the table to copy
data from. The code for Copy-TableData function is given below.

The function uses SqlBulkCopy object as it’s fast compared to normal insert considering the fact that there isn’t any data in destination tables. Let’s wrap this code in
a function as shown below.

In order to test the above solution run the below script to prepare test scenario.

When done, execute the powershell function to automatically copy all tables and data from copydb1 to copydb2.

 

Regards

Ahmad Osama

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook