Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Monday 17 November 2014

Copy Database from SQL 2005 to SQL 2008 ( Lower to Higher Version)

We cant able to copy higher version(SQL 2008 Secondbase) to lower version(SQL 2005 Secondbasedev)

Using attach/detach or backup/restore Method.

But We can Achieve using 

Generate Scripts(with Data) / SQL Server Import AND Export Wizard available in 

SQL 2008 on Small Databases.

before doing this we have to disable all constraints and make it copy then enable constraints

-- Disable all the constraint in databaseEXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
Generate Scripts(with Data):



save to an script window or text file and make it easy.
-- Enable all the constraint in databaseEXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"


SQL Server Import AND Export Wizard:

before doing this we have to disable all constraints and make it copy then enable constraints

-- Disable all the constraint in databaseEXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"




  • Then SQL Server Import AND Export Wizard window will be opened. Press Next
  • Choose a Data source (in your case from SQL Server 2008). Choose a Destination( in your case from SQL Server 2005).
  • Select Copy data from one or more tables or view
  • Select the source's tables and destination's tables
  • Click Next & Finish
-- Enable all the constraint in databaseEXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"


No comments:

Post a Comment