These past weeks the number of errors I have been getting when attaching a database has been far too many. But the best part of this learning experience is that I get to write about them one after another. There are no right or wrong answers sometimes, but am learning every time. Here is an error which is related to a downgrade path not supported.
Fix Error 948 – A Downgrade path is not supported
Here is the error I received while I was trying to attach database using below command:
USE [master] GO CREATE DATABASE [SQLAuthority] ON ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\SQLAuthority.mdf' ), ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\SQLAuthority_log.ldf' ) FOR ATTACH GO
Error message:
Msg 1813, Level 16, State 2, Line 3
Could not open new database ‘SQLAuthority’. CREATE DATABASE is aborted.
Msg 948, Level 20, State 1, Line 3
The database ‘SQLAuthority’ cannot be opened because it is version 852. This server supports version 782 and earlier. A downgrade path is not supported.
The second part of the message was strange and was the first time I was seeing. I found a command on the internet to read MDF file version:
DBCC CHECKPRIMARYFILE('C:\Program Files\Microsoft SQL
Server\MSSQL13.SQL2016\MSSQL\DATA\SQLAuthority.mdf',2)
So, we know that version is 802 which is for SQL Server 2016. When I ran select @@version on my server I found that I was trying to attach it to lower versions of SQL.
In short, this error is expected when we try to attach file from higher version of SQL to lower version of SQL. We cannot attach/detach or backup/restore a database from a newer version of SQL Server down to an older version – the internal file structures are just too different to support backwards compatibility.
Here is what I was able to find on the internet.
| SQL Server Version | Internal Database Version |
| SQL Server 2016 | 852 |
| SQL Server 2014 | 782 |
| SQL Server 2012 | 706 |
| SQL Server 2008 R2 | 660/661 |
| SQL Server 2008 | 655 |
| SQL Server 2005 | 611/612 |
| SQL Server 2000 | 539 |
| SQL Server 7 | 515 |
Do you know the steps to move to older version? What have you done before for such errors? Do feel free to share with all via the comments section below.
Reference: Pinal Dave (https://blog.sqlauthority.com)
4 Comments. Leave new
I think there is no any way to restore DB from higher version to lower version except creating it manually
I script out schema and data inserts either using SSMS or another tool.
The best, easy and fast option is to use a combination of ‘Generate Script’ and good old BCP. Use the former to generate all the necessary scripts and run them in the proper order after creating the Target database as an empty database. Do a BCP OUT from the higher version Database and perform a BCP IN into the lower version Database. There are options to retain the value of Identity Property of Tables using the flag “-E” while doing a BCP IN (this is required if Identity property is used in foreign key relationship). If one is conversant with batch files, BCP is the best and easiest option to handle cases like this. Generating Schema with Data is another option, but if the data is huge, SQL will crash while running via SSMS
i was a similar problem, but i do not understood why, because my SQL version was the most recent
if i run “select @@version” i got:
“Microsoft SQL Server 2016 (SP1) (KB3182545) – 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows 10 Pro 6.3 (Build 16299: ) ”
when i try your commands:
“USE [master]
GO
CREATE DATABASE [DataClient] ON
( FILENAME = N’G:\SQL\Data\DataClient.mdf’ ),
( FILENAME = N’G:\SQL\Data\DataClient_log.ldf’ )
FOR ATTACH
GO” i got:
Msg 1813, Level 16, State 2, Line 4
Could not open new database ‘DataClient’. CREATE DATABASE is aborted.
Msg 948, Level 20, State 1, Line 4
The database ‘DataClient’ cannot be opened because it is version 869. This server supports version 852 and earlier. A downgrade path is not supported.
Then i get verification on source version:
“DBCC CHECKPRIMARYFILE(‘G:\SQL\Data\DataClient.mdf’,2)”
i got:
“property value
Database name DataClient
Database version 869
Collation 872468488”
Could you help ? thanks in advance.