Restore SQL Server to an earlier version

  • Article

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’

@dest_path= ‘\\sqlserverdemo\data\’

@filename= ‘syslogins.dat’

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.

Technorati Tags: SQL Server,versions,backup,restore,orphaned users