Refresh database from SQL 2008 to SQL 2005 by using import/Export


Today I got request refresh database from Production to QA; my production server is SQL 2008 R2 and QA environment is SQL 2005. Well is it possible to downgrade database version or try to make SQL fool by detaching and attaching data and log files from higher version to lower. Database on SQL 2008 has bit different file structure and system tables and SQL 2005 does not understand the SQL 2008 structure hence cannot restore or attach/detach.

Answer is NO and never. What are other options???? My SQL 2008 database has store procedures, Function, Views and tables has identity column. To overcome this I used combination of IMPORT and EXPORT and scripts out the all the objects from production. This method is useful for the table which has the identity column and Values in PROD and QA are identical. Below are the few steps.

(I’m not going into details steps to perform import/export and understand even novice DBA can perform and understand import/export)

STEPS,

1)      Drop all the objects in QA server or drop the database and create it with same configuration.

2)      Go to Production server and click on the database. Where you want to create scripts.

a)      Right click on Database à Tasks àGenerate Scripts

b)      In Generate script wizards select all the objects à Save to file and click advance option à change script version to SQL 2005

c)       Finish the wizard. (Script will have create objects as well alter object steps)

3)      Go to QA server and on the blank database

a)      Run the generated scripts only create statements (Crate table/Create sp/Create Function/create Views. At this time do not run alter statements. Alter statements does creates FK or constraint)

b)      This would create all blank objects.

4)      Go to production servers.

a)      Right Click on the databaseà Tasksà Export. This will start the import/export wizard. Select correct source and designation servers and database.

b)      Select tables/View àselect the table you want to export and select Edit Mappings and click Enable identity insert. Click ok and finish the wizard.  Enable identity insert can be set for non-identity tables. It will apply only for identity columns, so no need to check is table has identity column or not. This is easy if database has thousands of tables.

Advertisements

5 Responses

  1. out standing its a very good idea and was helpful me ……

    thax a lot

  2. This article saved my hundres of minutes.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: