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