Restore SQL Server to an earlier version
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
Comments
Anonymous
November 20, 2008
PingBack from http://www.safewordfordforcitrix.com/andrew-fryers-blog-restore-sql-server-to-an-earlier-versionAnonymous
November 21, 2008
You can also periodically script out the logins using a procedure called sp_help_revlogins that will extract the commands necessary to recreate your logins. It won't capture default DB but a small modification will take care of that. This could be run every night as an instance level recovery tool, kin of handy. Using DBArtisan will even extract the SID for you, so no need to worry about remapping.