It’s a simple matter to restore SQL Server backups to later versions, in fact you don’t need to do anything different but what about the other way around?
I first got caught out with this back in the days of SQL Server 7 and 2000, and the basic answer is the same today for SQL Server 2008 as it was then – you can’t restore a backup from a newer version of SQL Server to an older one. Attach and detach will also fail.
The simplest approach I can think of is to transfer database objects in Integration Services. And don’t forget to bring over any logins as well to avoid orphaned users for example:
- to get them out of the current system use SSIS or this BCP command
Bcp master..syslogins out \\sqlserverdemo\data\syslogons.dat -N -S . -T
- To import them
EXEC sp_resolve_logins @dest_db= ‘personnel’
The only reason I can think of that you would need to do this would be to back out of a migration to a later version of SQL Server, and my advice is to do everything possible to avoid the need for this to happen by carefully planning the migration.