Restore SQL Server 2012 backup to a SQL Server 2008 database?
How to migrate a SQL Server database to a lower version

Problem

After recently upgrading a SQL Server instance to SQL Server 2012 a few days ago, you noticed that your application is not functioning properly. You decided to roll back the upgrade by downgrading the SQL Server database engine to SQL Server 2008 R2.  After the downgrade of the database engine, you are unable to attach the databases or restore the backups of the databases, even though the database compatibility level is set to the downgraded version of SQL Server. You receive the following error message, when you attempt to restore the database:

Msg 1813, Level 16, State 2, Line 1
Could not open new database 'DatabaseName'. CREATE DATABASE is aborted.
Msg 948, Level 20, State 1, Line 1
The database 'DatabaseName' cannot be opened because it is version 655. This server supports version 611 and earlier. A downgrade path is not supported.

This error message is generated because SQL Server automatically upgrades the database, when you restore or attach the database from lower version to higher version.  SQL Server does not allow you to restore or attach a database from a higher version of SQL Server to a lower version  of SQL Server. In this tip, we will look at a one time procedure which we can follow to downgrade the database from a higher version (SQL Server 2012) of SQL Server to a lower version (SQL Server 2008 R2) of SQL Server.

Option A : Script out database in compatibility mode using Generate script option :

Note : If you script out database with schema and data, depending on your data size, the script will be massive and wont be handled by SSMS, sqlcmd or osql (might be in GB as well).

Solution

The error message in the problem statement occurs because the SQL Server database files (*.mdf, *.ndf and *.ldf) and backups are not backward compatible.  Backward compatibility is why we cannot restore or attach a database created from a higher version of SQL Server to a lower version SQL Server. However, there are a few options that can help us to downgrade the database from a higher version of SQL Server to a lower version SQL Server. These options include:

• Generate Scripts wizard of SQL Server Management Studio
• SQL Server Integration Services
• Custom scripting and BCP

In this tip we will use the Generate Scripts wizard of SQL Server Management Studio.  Here are the basic steps we need to follow:

• Script the database schema in higher version of SQL Server by using the Generate Scripts wizard of SQL Server Management Studio interface.
• Connect to the lower version of SQL Server, and then run the SQL scripts that were generated in the previous step, to create database schema and copy data.

In the next section, I will demonstrate the steps for downgrading a SQL Server 2012 database to SQL Server 2008 R2 database.

Note: For demonstration purpose, I'll be downgrading the OUTLANDER database hosted on my SQL Server 2012 instance (IITCUK\DEV01) to SQL Server 2008 R2 instance (IITCUK\SQLSERVER2008).

Step-by-Step Demo: Downgrading a SQL Server 2012 database to SQL Server 2008 R2

Step-1: Script the schema of the OUTLANDER database on the SQL Server 2012 instance (IITCUK\DEV01) using theGenerate Scripts wizard of the SQL Server Management Studio interface.
In Object Explorer connect to IITCUK\DEV01, right-click OUTLANDER database, expand Tasks and choose"Generate Scripts...".

This launches Generate and Publish Scripts wizard. Click Next, to skip the Introduction screen and proceed to theChoose Objects page.

On the Choose Objects page, choose option "Script entire database and all database objects", and then click Nextto proceed to "Set Scripting Options" page.

On the Set Scripting Options page, specify the location where you want to save the script file, and then choose theAdvanced button.

In Advanced Scripting Options dialog box, set Script Triggers, Indexes and Primary Key options to True, Script for Server Version to SQL Server 2008 R2, and Types of data to script to Schema and Data.  This last option is key because this is what generates the data per table.

Once done, click OK, to close Advanced Scripting Options dialog box and return to Set Scripting Options page. InSet Scripting Options page, click Next to continue to Summary page.
After reviewing your selections on Summary page, click Next to generate scripts.

Once scripts are generated successfully, choose the Finish button to close the Generate and Publish Scripts wizard.

Step-2: Connect to the SQL Server 2008 R2 instance (IITCUK\SQLSERVER2008), and then run the SQL scripts that were generated in Step-1, to create the OUTLANDER database schema and copy its data.
In Object Explorer connect to IITCUK\SQLServer2008, then in SQL Server Management Studio, open the SQL Server script you saved in Step-1.

Modify the script, to specify the correct location for the OUTLANDER database data and log files. Once done, run the script to create the OUTLANDER database on IITCUK\SQLServer2008 instance.

Upon successful execution, refresh the Database folder in Object Explorer. As you can see in the following imageOUTLANDER database has been successfully downgraded.

Option B:

First script out tables first with all Indexes, FK's, etc and create blank tables in the destination database - option with SCHEMA ONLY (No data).

Use BCP to insert data

1. bcp out the data using below script. set SSMS in Text Mode and copy the output generated by below script in a bat file.

   -- save below output in a bat file by executing below in SSMS in TEXT mode
   
   -- clean up: create a bat file with this command --> del D:\BCP\*.dat 
   
   select '"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe" ' /* path to BCP.exe */
       +  QUOTENAME(DB_NAME())+ '.' /* Current Database */
       +  QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+'.'            
       +  QUOTENAME(name)  
       +  ' out D:\BCP\'  /* Path where BCP out files will be stored */
       +  REPLACE(SCHEMA_NAME(schema_id),' ','') + '_' 
       +  REPLACE(name,' ','') 
       + '.dat -T -E -SServerName\Instance -n' /* ServerName, -E will take care of Identity, -n is for Native Format */
   from sys.tables
   where is_ms_shipped = 0 and name <> 'sysdiagrams'                       /* sysdiagrams is classified my MS as UserTable and we dont want it */
   /*and schema_name(schema_id) <> 'unwantedschema'    */                             /* Optional to exclude any schema  */
   order by schema_name(schema_id)

2. Run the bat file that will generate the .dat files in the folder that you have specified.
3. Run below script on the

   --- Execute this on the destination server.database from SSMS.
   
   --- Make sure the change the @Destdbname and the bcp out path as per your environment.
   
   declare @Destdbname sysname
   set @Destdbname = 'destinationDB' /* Destination Database Name where you want to Bulk Insert in */
   select 'BULK INSERT ' 
   /*Remember Tables must be present on destination database */ 
   + QUOTENAME(@Destdbname) + '.' 
   + QUOTENAME(SCHEMA_NAME(SCHEMA_ID)) 
   + '.' + QUOTENAME(name) 
   + ' from ''D:\BCP\' /* Change here for bcp out path */ 
   + REPLACE(SCHEMA_NAME(schema_id), ' ', '') + '_' + REPLACE(name, ' ', '') 
   + '.dat'' with ( KEEPIDENTITY, DATAFILETYPE = ''native'', TABLOCK )' 
   + char(10) 
   + 'print ''Bulk insert for ' + REPLACE(SCHEMA_NAME(schema_id), ' ', '') + '_' + REPLACE(name, ' ', '') + ' is done... ''' 
   + char(10) + 'go'
      from sys.tables
      where is_ms_shipped = 0
   and name <> 'sysdiagrams' /* sysdiagrams is classified my MS as UserTable and we dont want it */
   and schema_name(schema_id) <> 'unwantedschema' /* Optional to exclude any schema */
       order by schema_name(schema_id) 

4. Run the output using SSMS to insert data back in the tables.

This is very fast bcp method as it uses Native mode.

Option C:

SQL Server versions:

The internal database versions for SQL Server aren't clearly documented in the Microsoft documentation.

SQL Server Version	Internal Database Version	Database Compatibility Level
2014	782	120
2012	706	110
2012 CTP1 (2011 Denali)	684	110
2008 R2	660 / 661	100
2008	655	100
2005 SP2+ with VarDecimal enabled	612	90
2005	611	90
2000	539	80
7.0	515	70
6.5	?	65
6.0	?	60

Legend: ? = still investigating, RTM = Release to manufacturing, SPn = Service Pack n

SQL Server Version	Compatibility Level	Internal Database Version	Build
SQL Server 2012 RTM	110	706	11.0.2100.60
SQL Server 2008 R2	100	665	10.50.xxxx
SQL Server 2008	100	661	10.00.xxxx
SQL Server 2005  SP2	90	612	9.00.xxxx
SQL Server 2005	90	611	9.00.xxxx
SQL Server 2000	80	539	8.00.xxx(x)
SQL Server 7	65	515	7.00.xxx(x)

Six Ways to Find SQL Server Internal Version Number (SQL Server 2012 Version 706)

The Internal version number of the SQL Server code with which the database was created. It is not a supported value from Microsoft. Future compatibility is not guaranteed but it works for SQL Server 2008 and 2012.

Howerver, many users are coming across this value in error message when they were trying to restore/copy database from a lower version instance. You may not find a good source for documentation about this value.  I compiled six ways(T-SQL query) to get this value for your database: the first two are to restore from the the bak file Header and the four other ways are  to query online database to get this Internal Version Number.

The quickest way to get this Version value for an online database is to query the sysdatabase:

SELECT name,version FROM sys.sysdatabases

 The details of these six methods:

A. Extract this value from backup file Header Infomation:

1. Run the following command from most recent SSMS ( if you run from lower version SSMS, you may not get this value):

RESTORE HEADERONLY FROM DISK=N’c:\backup\sampleDB.bak’

Check these columns from the result panel:DatabaseName,DatabaseVersion,DatabaseCreationDate

The DatabaseVersionison (Internal Version Number) are one of these values:

706(SQLServer 2012), 665/661(SQLServer 2008/R2),611/612(SQLServer 2005/SP2) and539(SQLServer 2000)

2. Run this script

sp_configure ‘show advanced options’,1

GO

reconfigure

Go

sp_configure ‘Ad Hoc Distributed Queries’, 1

GO

reconfigure

GO

SELECT * INTO #myVersion

FROM OPENROWSET(‘SQLNCLI’,’Server=USWDM2ML5054498\SQL08R2;Trusted_Connection=yes’,

‘EXEC(”SET FMTONLY OFF RESTORE HEADERONLY FROM DISK =””C:\backupserver\sql_backups\mytest1.bak”””)’) AS t

SELECT DatabaseName,DatabaseVersion,DatabaseCreationDate FROM #myVersion

DROP TABLE #myVersion

B. Database Online methods:

1. Get the version value from DATABASEPROPERTYEX: (DATABASEPROPERTY still works but deprecated)
Run the following query from SSMS for your database:

SELECT SERVERPROPERTY(‘productversion’) as [Product version],
SERVERPROPERTY (‘productlevel’) as [Product level] ,
SERVERPROPERTY (‘edition’) as [Edition],
DATABASEPROPERTYEX((select DB_Name()),’Version’) as [Internal Version Number]
–,DATABASEPROPERTY((select DB_Name()),’Version’) as [Internal Version Number--Deprecated]
,(select DB_Name()) as DBName,
(SELECT COMPATIBILITY_LEVEL FROM SYS.DATABASES WHERE NAME = (select DB_Name())) as compatibilityLevel

2.Retrieve the value from sysdatabase

DECLARE @dbName VARCHAR(128)
SET @dbName=(SELECT Db_name())
SELECT name, version FROM sys.sysdatabases WHERE name = @dbName

3.Use DBCC Command DBINFO (yourdbName)

3.1 Get the values from the result of  DBINFO (yourdbName)
You need to turn on undocumented trace flag 3604 to run DBINFO and check the value ofdbi_version from the message screen.
DBCC TRACEON (3604)
GO
–get your database name
declare @dbName varchar(128)
Set @dbName=(select DB_name())

DBCC DBINFO (@dbName)
GO

DBCC TRACEOFF (3604)
GO

There is another value for this database that may be useful to your: dbi_createVersion
You will know the version this database is created from.

3.2 Get these values from a temp table

You can run the following query to get the value in atable:

DBCC TRACEON (3604)

GO

CREATE TABLE #t (ParentObject VARCHAR (255), [Object] VARCHAR (255), Field VARCHAR (500), [Value] VARCHAR (500))

––get your database name

declare @dbName varchar(128)

Set @dbName=(select DB_name())

insert into #t

exec (‘DBCC DBINFO (‘+@dbName+’) WITH TABLERESULTS’)

GO

select [dbi_dbname],[dbi_version],[dbi_createVersion] from (SELECT Field, Value FROM #t) src

PIVOT (Max(Value) For Field IN ([dbi_dbname],[dbi_version],[dbi_createVersion])) pvt

drop table #t

DBCC TRACEOFF (3604)

GO

3.3 Get these values on the fly:

<code>

EXEC sp_configure ‘show advanced options’,1

GO

reconfigure

Go

EXEC sp_configure ‘Ad Hoc Distributed Queries’, 1

GO

reconfigure

GO

DBCC TRACEON (3604)

GO

declare @dbName varchar(128) = (select DB_name())

declare @cmd varchar(1000);

select @cmd = ‘SET FMTONLY OFF;SET NOCOUNT ON; EXEC(”” DBCC DBINFO (‘+@dbName+’) WITH TABLERESULTS””)';

EXEC(‘SELECT * INTO myVersion from OPENROWSET(”SQLNCLI”,”Server=USWDM2ML5054498\SQL08R2;Trusted_Connection=yes;”,”’ + @cmd + ”’) AS t;’ )

SELECT Field, Value FROM myVersion

WHERE Field =’dbi_createVersion’ or Field =’dbi_crdate’ or Field =’dbi_Version’ or Field =’dbi_dbname’

DROP TABLE myVersion;

DBCC TRACEOff (3604)

GO

</code>

4.Use DBCC Command PAGE (yourdbName,1,9,3)

4.1 Get the value from the result of PAGE (yourdbName,1,9,3)

DBCC TRACEON (3604)
GO
–get your database name
declare @dbName varchar(128)
Set @dbName=(select DB_name())
DBCC PAGE (@dbName, 1, 9, 3);
GO
DBCC TRACEOFF (3604)
GO

You can get the same information(dbi_version  and  dbi_createVersion) for the database from this DBCC command.

4.2 Get the values from a temp table

You can run another query to get the values in a table:

DBCC TRACEON (3604)

GO

CREATE TABLE #t (ParentObject VARCHAR (255), [Object] VARCHAR (255), Field VARCHAR (255), [Value] VARCHAR (255))

––get your database name

declare @dbName varchar(128)

Set @dbName=(select DB_name())

insert into #t

exec (‘DBCC PAGE(‘+@dbName+’,1,9,3) WITH TABLERESULTS’)

GO

select [dbi_dbname],[dbi_version],[dbi_createVersion] from (SELECT Field, Value FROM #t) src

PIVOT (Max(Value) For Field IN ([dbi_dbname],[dbi_version],[dbi_createVersion])) pvt

–select * from #t

drop table #t

DBCC TRACEOFF (3604)

GO

4.3 Get the values on the fly:

EXEC sp_configure ‘show advanced options’,1

GO

reconfigure

Go

EXEC sp_configure ‘Ad Hoc Distributed Queries’, 1

GO

reconfigure

GO

DBCC TRACEON (3604)

GO

declare @dbName varchar(128) = (select DB_name())

declare @cmd varchar(1000);

select @cmd = ‘SET FMTONLY OFF;SET NOCOUNT ON; EXEC(”” DBCC PAGE (‘+@dbName+’, 1,9,3) WITH TABLERESULTS””)';

EXEC(‘SELECT * INTO myVersion from OPENROWSET(”SQLNCLI”,”Server=USWDM2ML5054498\SQL08R2;Trusted_Connection=yes;”,”’ + @cmd + ”’) AS t;’ )

SELECT Field, Value FROM myVersion

WHERE Field =’dbi_createVersion’ or Field =’dbi_crdate’ or Field =’dbi_Version’ or Field =’dbi_dbname’

DROP TABLE myVersion;

DBCC TRACEOff (3604)

GO