I have a database currently in SQL Server 2008 to be moved to SQL Server 2005. I would like to backup the 2008 db to a bak file and import it to 2005, but couldn't find any options in SSMS 2008 while taking backup. Has anyone had a similar need in the past? How did you manage this through?
A:
It's easier than that, since (assuming your db isn't using any 2008-only features) they use the same format (*.mdf). Simply detach your database from your 2008 server, copy the mdf file to the 2005 server, and attach the copy to the 2005 server. Look for the Attach/Detach option when you right click on a database in sql server management studio.
Joel Coehoorn
2010-05-07 20:49:11
Moving from a 2008 database to a 2005 server? I don't think this will ever work - or will it?? You definitely **CANNOT** restore a 2008 backup on a 2005 server
marc_s
2010-05-07 20:50:42
+1
A:
You cannot do this. You never could restore a more recent SQL Server database to an older version.
Your only option is to script out the data you need to insert, or use a data comparison tool like Red-Gate SQL Data Compare to compare the two databases (and update your older one from the newer). SQL Data Compare will also work comparing a backup (any version) to a live database on a server.
marc_s
2010-05-07 20:50:04
+2
A:
You could try the Publish Database Wizard.
From the link:
The source database must be on an instance of SQL Server 2005, SQL Server 2005 Express Edition, or SQL Server 2008. The target database must be on an instance of SQL Server 2000, SQL Server 2005, SQL Server 2005 Express Edition, or SQL Server 2008.
Austin Salonen
2010-05-07 20:52:31
+1
A:
I don't think you can restore 2008 backup on 2005 server. Try 3rd party schema/data migration tool, like SQL Examiner Suite or RedGate's SQL Compare
SQLDev
2010-05-12 16:50:27