SQL Server :- “Cannot open database [database] version 852. Upgrade the database to the latest version.”

 

Background

Tried accessing a database and ran into an error.

Error

Error Text

Msg 946, Level 14, State 1, Line 1
Cannot open database 'WideWorldImportersDW_DBCCClone' version 852.
Upgrade the database to the latest version.

Error Image

error.2019020.0121PM

Metadata

sys.databases

Code


 declare @tblDatabaseInternalVersion TABLE
(
      [id] int
    , [version] varchar(60)
)

insert into @tblDatabaseInternalVersion
(
      [id]
    , [version]
)
select 515, '7'
union all
select 539, '2000'
union all
select 611, '2005'
union all
select 612, '2005'
union all
select 655, '2008'
union all
select 661, '2008-R2'
union all
select 663, '2008-R2'
union all
select 706, '2012'
union all
select 782, '2014'
union all
select 852, '2016'
union all
select 869, '2017'

select
          tblSD.[name]

        , [internalDatabaseVersion]
            = DATABASEPROPERTYEX
              (
                  tblSD.[name]
                , 'Version'
              )

        , tblDIV.[version]

        , [isClone]
            = DATABASEPROPERTYEX
            (
                  tblSD.[name]
                , 'IsClone'
            )

        , tblSD.[is_read_only]

        , tblSD.[snapshot_isolation_state]

        , tblSD.[recovery_model_desc]

        , tblSD.[is_cleanly_shutdown]

        --, tblSD.*

from   sys.databases tblSD

left outer join @tblDatabaseInternalVersion tblDIV

        on DATABASEPROPERTYEX
              (
                  tblSD.[name]
                , 'Version'
              ) = tblDIV.id

where  (

                ( tblSD.[database_id] <= 4 )
            or
                ( tblSD.[name] like 'Wide%' )

       )

order by

    lower(tblSD.[name]) asc

Output

sys.databases.20190209.0107PM

Explanation

  1. DatabasePropertyex
    • Version
      • Database Internal Version
        • v2017
          • All System Databases are v2017
          • WideWorldImportersDW is 2017
        • v2016
          • Cloned Database
            • WideWorldImportersDW_DBCCClone
    • isClone
      • Database was cloned using DBCC CLONE
  2. sys.databases
    • is_read_only
      • Cloned Database is readonly

Remediation

Outline

Cloned Databases are read only.

When we upgraded our SQL Server Instance from v2016 to v2017, read only databases such as cloned databases are skipped.

All other databases are brought up to the version of the SQL Server Instance.

If we really do need a cloned database, we will create it.

Create Cloned Database

SQL


DBCC CLONEDATABASE 

    (
          [WideWorldImportersDW]
        , [WideWorldImportersDW_DBCCClone_v2017]
    );   

GO

Output

Output – Text


Database cloning for 'WideWorldImportersDW' has started with target as 'WideWorldImportersDW_DBCCClone_v2017'.
Database cloning for 'WideWorldImportersDW' has finished. Cloned database is 'WideWorldImportersDW_DBCCClone_v2017'.
Database 'WideWorldImportersDW_DBCCClone_v2017' is a cloned database.
This database should be used for diagnostic purposes only and is not supported for use in a production environment.
DBCC execution completed. If DBCC printed error messages, contact your system administrator

Output – Image

dbcc.cloneDatabase.20190209.0231PM

 

Lineage

  1. SQL Server – Clone Database – Schema & Statistics
    Link