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
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)