Sql-server – “Cannot find data type” restoring a SQL Server 2014 backup on SQL Server 2008 R2

sql server 2014sql-server-2008-r2

I have been passed a SQL Server 2014 backup that needs to be imported onto one of my SQL Server 2008 R2 servers. The backup is valid as I can restore it onto one of Dev 2014 SQL servers.

I have done the script out task and that works fine for the most part. However one issue I face is that the SQL Server 2014 database had tables using data compression and I get errors when I try to import these to 2008 R2 such as this.

Column, parameter, or variable #50: Cannot find data type dbo.tCompressedBinary.

What options do I have for this issue? Does anyone recommend any other approach (apart from setting up a SQL Server 2014/2016 prod box) that I could follow?

Best Answer

Actually, tCompressedBinary sounds like it might be a true UDT (a user-defined type implemented using CLR) rather than a UDDT (a user-defined data type, which are just aliases for other types created with syntax like CREATE TYPE creditcardno FROM CHAR(11) NOT NULL;

See the answers to https://stackoverflow.com/questions/35494575/sql-server-udt-versus-uddt for more information and links to MS documentation.

If tCompressed is a UDT then it won't be included in the database scripts you have generated. You will need to install and activate that CLR module in the new SQL Server instance before you can run the scripts to build this schema. Otherwise go with my original answer, below.

If you really can't do that (you don't have permission to turn on CLR on the new instance, the module is a 3rd party one that doesn't support 2008r2 at all, etc.) then you might get away with replacing all references to the type in column and parameter definitions with with what-ever type the application code is compatible with (VARBINARY(MAX) would be my first guess).

If you are using 2008r2 Enterprise Edition then compression is supported so that would not be the problem. In that case, check the definition of the user-defined type tCompressedBinary. Are there errors when it is created? Or is it present in the wrong order (defined in the scripts after it is first used)?

If you are using an edition other then Enterprise, then search the scripts and remove all references to [WITH] COMPRESSION. Of course, there may be other compatibility issues once you have removed all the compression directives which will become apparent once you've tidied up this one.

Related Question