SQL Server 2012 backup restore to earlier version (or dettach/attach) –Not working


It is by design to disallow a SQL Server 2012 version database to be restored on an earlier version.
You cannot use dettach and attach to do the same. It is not a good idea to develop at higher version database and deploy to a lower version server. If you have to migrate your database to a lower version, you need to script both objects and data from your SQL Server 2012 for your target databas (2008,2005,2000) and run the script at your target server.
Here is the error message if you try dettach/attach a SQL Server 2012 DB from SQL Server 2008R2:
Attach database failed for Server myPCServer’. (Microsoft.SqlServer.Smo)
The database ‘myDBtest’ cannot be opened because it is version 706. This server supports version 661 and earlier. A downgrade path is not supported.
Could not open new database ‘myDBtest’. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 948)

If you try to restore a SQL Server 2012 DB backup from SQL Server 2008R2 instance:
You will get this error from SQL Server Management Studio (SSMS):
Specified cast is not valid. (SqlManagerUI)

If you are wondering about the version number 706/661, you can find more information from another post:

https://jingyangli.wordpress.com/2012/03/10/six-ways-to-find-sql-server-internal-version-number/



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s