Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.