Sql-server – SQL 2012: Import DB changes original data scheme

sql serversql-server-2008-r2sql-server-2012

I'm migrating data from SQL 2008 R2 Servers to SQL 2012. Therefore I'm using SSMS Import Data Task on the SQL 2012 machines.

As a test I copied the data files of one of the DBs, did an "Attach Database" and a schema compare with the latest SQL Server Data Tools.

The diff from the attached vs. the imported-one seems just wrong.
As an example:

Attached:

CREATE TABLE [dbo].[DbEventMessages] (
[EventID]         INT            IDENTITY (1, 1) NOT NULL,
[EntityType]      NVARCHAR (MAX) NOT NULL,
[ContactId]       INT            NOT NULL,
[Action]          NVARCHAR (MAX) NOT NULL,
[DateTime]        DATETIME2 (7)  NOT NULL,
[InnoVersionType] ROWVERSION     NULL,
CONSTRAINT [PK_DbDbEventMessages] PRIMARY KEY CLUSTERED ([EventID] ASC)
);
GO

Imported:

CREATE TABLE [dbo].[DbEventMessages] (
[EventID]         INT            NOT NULL,
[EntityType]      NVARCHAR (MAX) NOT NULL,
[ContactId]       INT            NOT NULL,
[Action]          NVARCHAR (MAX) NOT NULL,
[DateTime]        DATETIME2 (7)  NOT NULL,
[InnoVersionType] ROWVERSION     NOT NULL
);
GO

The imported scheme differs in

  • the IDENTITY property on the key column
  • the ROWVERSION was changed from NOT NULL to NULL
  • the primary key constraint is missing

What is wrong here? Or is it working correctly?
Thanks for any comments..

Best Answer

Take a backup of the SQL Server 2008 databases and restore it on SQL Server 2012. It is a much faster and reliable approach to migrating a database. Do not attach files between the instances, the SQL Server 2012 will upgrade them to a new format and you will loose the ability to use them again on SQL Server 2008.

As a general advice try to follow the procedures and guidelines, including the pre-migration validation steps from Upgrade to SQL Server 2012

Make sure you migrate all login information and SQL Agent jobs relevant for your database(s).