Downgrading SQL server 2008 to SQL Server 2005

Question

Are there any issues backing up a database from SQL Server 2008, and then importing it into SQL Server 2005? I had upgraded to SQL Server 2008, but 2008 seems to be too problematic so now I want to downgrade. Are there any problems in doing this?

Answer 1

Well, you won't be able to restore a 2008 backup to a SQL 2005 server.

Easiest way to do it is to use the "Generate SQL Server Scripts" wizard to create a script (including schema and data) that you can run on SQL 2005 to create the database. See http://www.devx.com/dbzone/Article/40531

But back to the original issue: what problems are you having with SQL 2008?

Answer 2

The other way to do this is to connect to both servers via the object explorer and import data from one into the other.

When doing this, some things don't transfer correctly, however. For example, I had to manually reset the primary and foreign key settings, as well as the auto-increment setting. That was a small price to pay, though, for getting all the data to work.

Answer 3

I would agree what is the issue ? can you not run the database in SQL 2005 compatability mode if it is something about the application. If you must move to 2005 you cant just restore the database from 08 to 05.

If you have a DB that is bigger than 50mb or so I would script your database schema using the script generation wizard, script all your keys, indices, triggers, collation and any full text indices you have without the data. Just remember to check the script for sql 2005 version option. Run that script to create a new database on your sql server 2005.

If you have triggers on your tables you will want to disable them all before you import the data. These scripts will do that for you. These will do that for you

-- Disable Triggers
While @i <= @MaxI
Begin
      Select @dsql =  'DISABLE Trigger ' + TriggerName + ' ON ' + TableName
      From @DisableTriggers
      Where MyRowCount = @i
      Exec sp_executesql @dsql
      Select @i = @i + 1
End

-- Enable Triggers
While @i <= @MaxI
Begin
      Select @dsql =  'Enable Trigger ' + TriggerName + ' ON ' + TableName
      From @DisableTriggers
      Where MyRowCount = @i
      Exec sp_executesql @dsql
      Select @i = @i + 1
End

Then use the import / export wizard to import the data into your database; this is much quicker than script out script in my experience.