2

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

Lin-Art
  • 133
  • 5

1 Answers1

2

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

Remus Rusanu
  • 51,846
  • 4
  • 95
  • 172
  • thanks for your answer! "faster and reliable approach" means that you wouldn't trust SSMS Import Data Task? It's mentioned as a possible migration szenario under your linked article.. "Do not attach files"- Attaching wouldn't change the database compatibility level, would it? – Lin-Art Jun 11 '12 at 11:56
  • Attach upgrades the database to the instance version. Once upgraded, there is no downgrade path. If you attached the SQL 2008R2 DBs to a SQL 2102 those are now SQL 2012 files and you cannot change them back to 2008R2, ever. Compatibility level is a completely different thing. – Remus Rusanu Jun 11 '12 at 12:02
  • 1
    As an aside I highly recommend backup/restore over detach/attach. See my reasons here. – Aaron Bertrand Jun 11 '12 at 12:06
  • SSMS data migration level is an migration option, I would never consider it though, specially if backup/restore is available as an option. But if I would have to use SSMS I would use the Copy Database wizard, not the Export/Import Data wizard. – Remus Rusanu Jun 11 '12 at 12:11
  • Tried it according to your comments + scheme compare each time: Import/Export doesn't seems to work as "expected" for this scenario and results as written in the inital question. Backup/Restore, SSMS Copy DB, Detach/Copy/Attach worked well. Backup/Restore is unquestioned the most secure and reliable way. Thanks for sharing your knowledge – Lin-Art Jun 11 '12 at 13:21
  • @Lin-Art Also for keeping them in sync (e.g. assuming you will continue developing on the original server and deploy changes over time to the new server), I recommend a 3rd party tool for this. I use Red-Gate SQL Compare but there are other alternatives I mentioned in a blog post about re-inventing the wheel. – Aaron Bertrand Jun 11 '12 at 13:24
  • @Aaron Bertrand thanks, used to use open dbdiff (on your list too ;) and my own scripts packed into my app-upgrades. have you already tried the latest SQL Server Data Tools? Nice to handle for any EF developer – Lin-Art Jun 11 '12 at 13:31