Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

Restore SQL Server to an earlier version

Restore SQL Server to an earlier version

  • Comments 2
  • Likes

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.

Comments
  • PingBack from http://www.safewordfordforcitrix.com/andrew-fryers-blog-restore-sql-server-to-an-earlier-version

  • 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.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment