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

SQL Server – Clone Database – Schema & Statistics

Background

Working with my best friend to troubleshoot a non-performant query and so here we go.

Scenario

The database is too big and contains data that should be kept private and so what to do.

DBCC Clone

Outline

  1. Source SQL Instance
    • dbcc  clone
    • Backup resulting database
  2. Destination SQL Instance
    • Restore database
  3. Compare Queries

Source SQL Instance

DBCC Clone

SQL

DBCC CLONEDATABASE 

	(
		  [WideWorldImportersDW]
		, [WideWorldImportersDW_DBCCClone]
	);   

GO

Database Backup

SQL

exec master..xp_create_subdir 'E:\temp'
go

backup database [WideWorldImportersDW_DBCCClone]
to disk = 'E:\temp\WideWorldImportersDW_DBCCClone.bak'
with init, format, stats=1 

GO

Destination SQL Instance

Database Restore

SQL

USE [master]
go

exec master..xp_create_subdir 'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone'
go

exec master..xp_create_subdir 'C:\Microsoft\SQLServer\Logfiles\WideWorldImportersDW_DBCCClone'
go

RESTORE DATABASE [WideWorldImportersDW_DBCCClone] 

FROM  DISK = N'C:\Temp\WideWorldImportersDW_DBCCClone.bak' WITH  FILE = 1

	,  MOVE N'WWI_Primary' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_167133806.mdf'
	,  MOVE N'WWI_UserData' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_UserData_1341343279.ndf'
	,  MOVE N'fg_Partition_Year_Base__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_Base_file_001_4162930605.ndf'
	,  MOVE N'fg_Partition_Year_2010__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2010_file_001_3028494415.ndf'
	,  MOVE N'fg_Partition_Year_2011__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2011_file_001_474007300.ndf'
	,  MOVE N'fg_Partition_Year_2012__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2012_file_001_3961387451.ndf'
	,  MOVE N'fg_Partition_Year_2013__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2013_file_001_2554884830.ndf'
	,  MOVE N'fg_Partition_Year_2014__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2014_file_001_3195214954.ndf'
	,  MOVE N'fg_Partition_Year_2015__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2015_file_001_2796606126.ndf'
	,  MOVE N'fg_Partition_Year_2016__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2016_file_001_2922730419.ndf'
	,  MOVE N'fg_Partition_Year_2017__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2017_file_001_89564842.ndf'
	,  MOVE N'fg_Partition_Year_2018__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2018_file_001_2594545631.ndf'
	,  MOVE N'fg_Partition_Year_2019__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2019_file_001_102789485.ndf'
	,  MOVE N'fg_Partition_Year_2020__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2020_file_001_2559250543.ndf'
	,  MOVE N'fg_Partition_Year_Null__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_Null_file_001_3677708393.ndf'
	,  MOVE N'fg_Partition_Year_Next__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_Next_file_001_2414564952.ndf'
	,  MOVE N'WWI_Log' TO N'C:\Microsoft\SQLServer\Logfiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_3476128794.ldf'
	,  NOUNLOAD
	,  STATS = 5

GO

Compare Queries

Outline

  1. Compare Queries
    • Query 1

Compare Queries

Query 1

SQL
set nocount on
go

set statistics io on
go

declare @dateTS datetime
declare @date datetime

set @dateTS = getdate()
set @date = convert(varchar(10), @dateTS, 23)

select
          @dateTS as [@dateTS]
        , @date as [@date]

select top 10 *

from   [Dimension].[Date] tblD

select top 10 *

from   [Dimension].[Date] tblD

where  tblD.[Date] = @date
Output
Output – Source

query1_Source_20181115_0911AM

Output – Destination

query1_Destination_20181115_0912AM

Explanation
  1. We have data from original source
  2. And, not data from the resultant cloned database

 

Query Plan
Query Plan – Destination

queryPlan_Destination_20181115_0853AM

Query Plan – Source

queryPlan_Source_20181115_0905AM.PNG

Statistics IO
Image – Destination

statisticsIO.Destiation.2018115.0855AM

Image – Source

statisticsIO.Source.2018115.0856AM

Explanation
  1. Statistics IO
    • Scan Counts will match
    • Logical & Physical Reads will not
      • As Source has actual data, but destination does not

 

References

  1. Microsoft
    • Docs / SQL / T-SQL / Database Console Commands
      • DBCC CLONEDATABASE (Transact-SQL)