locked
Sql server migration from 2008 to 2012 RRS feed

  • Question

  • Hi,

    I m working on the migrating 2008 to 2012 sql server and we are actually migrating the database not upgrading. means, we are rewriting the sp's and recreating tables with new architecture.

    so please suggest me  

    1. what are things i need to consider before migration.

    2. suggest me best way to migrate the database from 2008 to 2012.

    Thanks

    Nagarajan. V



    Wednesday, December 21, 2016 5:05 PM

Answers

  • Hello,

    Script the structure and the data of those tables you want to migrate as explained  on below article:

    http://dirkstrauss.com/script-table-data/


    Choose “Schema and data”.

    Alternatively, use SSMS - Import/Export Wizard.



    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com



    Wednesday, December 21, 2016 7:48 PM
  • What you should do then is script the source tables out and create a new database on the new server called something like staging. Run the table scripts there.

    Then export the data using bcp and then import it in like this

    bcp "SourceDatabaseName.dbo.SourcetableName" out Source.dat -SSourceServer -T -n

    push it into the new server like this

    bcp "Staging.dbo.SourceTablename" in Source.Dat -SDestinationServer -T -n -E -b10000

    once all the data is in you can push it into the new tables like this

    set identity_insert TableName on

    insert into DestinationDatabaseName.dbo.TableName(Col1, Col2, Col3) ...etc

    select Col1, Col2, Col3 from Staging.dbo.SourcetableName

    set identity_insert tableName off

    This  way you can migrate selective columns.

    • Marked as answer by Nagarajan Venu Wednesday, December 21, 2016 7:57 PM
    Wednesday, December 21, 2016 7:56 PM

All replies

  • I suggest that you first migrate your database from 2008->2012. This is done by following the process during SQL 2012 installation or even a simple restore a 2008 DB into 2012 should work.  It doesn't work from 2005 -> 2012 for example as far as I know.

    Then once you are on 2012 look into redesigning your database, schema, other enhancements. Not the other way around you mentioned. My opinion anyway.

    Also see this for more technical:

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/283ccb21-215d-434e-92fb-dc8f14df91ad/migration-prerequisites-from-2008-to-2012-sql-server?forum=sqldatabaseengine

    Panos.



    • Edited by panlondon Wednesday, December 21, 2016 6:16 PM
    Wednesday, December 21, 2016 6:08 PM
  • run the Upgrade Advisor for SQL 2012 against the SQL 2008 databases - this will pick up most deprecated features and upgrade blockers.

    The problem is that there will likely be some false positives and it does not have the ability to sniff dynamic SQL Statements and evaluate them to determine if they will be supported o SQL 2012.

    Ensure that you run dbcc checkdb statements before you move the databases over to catch any corruption.

    The best way to migrate the databases is through a backup and restore. A detach and re-attach is second best (as corruption may manifest itself in the detach and reattach). If you do a detach and re-attach copy the database files do not move them. you want the database files to stay where they are in case you lose them. This way you will have something to fail back to.

    You will need to move all dependencies - ie logins, linked servers, xp's, cls, linked services, file system references, user jobs, etc as well.

    Wednesday, December 21, 2016 6:13 PM
  • Hello,

    Please read the following technical guide before upgrading/migrating to SQL Server 2012:

    https://msdn.microsoft.com/en-us/library/dn144931.aspx?f=255&MSPPError=-2147217396

    Backup/restore is my suggestion. However, you can use detach/Copy/attach.



    The following resources are recommended reading:

    https://msdn.microsoft.com/en-us/library/cc707789(v=sql.110).aspx

    https://msdn.microsoft.com/en-us/library/cc707782(v=sql.110).aspx

    https://msdn.microsoft.com/en-us/library/cc707784(v=sql.110).aspx

    https://msdn.microsoft.com/en-us/library/cc707785(v=sql.110).aspx



    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com



    Wednesday, December 21, 2016 6:26 PM
  • Hi,

    Thanks for your reply. I dont want migrate straight a way thru using tools are some existing methodologies.

    say for example.

    I have 10 table in existing databases and i have only 5 tables with new database with all columns. In new database the architecture is totally different.

    so in this case how will we migrate?

    Thanks

    Nagarajan. V

    Wednesday, December 21, 2016 6:49 PM
  • Hello,

    Script the structure and the data of those tables you want to migrate as explained  on below article:

    http://dirkstrauss.com/script-table-data/


    Choose “Schema and data”.

    Alternatively, use SSMS - Import/Export Wizard.



    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com



    Wednesday, December 21, 2016 7:48 PM
  • What you should do then is script the source tables out and create a new database on the new server called something like staging. Run the table scripts there.

    Then export the data using bcp and then import it in like this

    bcp "SourceDatabaseName.dbo.SourcetableName" out Source.dat -SSourceServer -T -n

    push it into the new server like this

    bcp "Staging.dbo.SourceTablename" in Source.Dat -SDestinationServer -T -n -E -b10000

    once all the data is in you can push it into the new tables like this

    set identity_insert TableName on

    insert into DestinationDatabaseName.dbo.TableName(Col1, Col2, Col3) ...etc

    select Col1, Col2, Col3 from Staging.dbo.SourcetableName

    set identity_insert tableName off

    This  way you can migrate selective columns.

    • Marked as answer by Nagarajan Venu Wednesday, December 21, 2016 7:57 PM
    Wednesday, December 21, 2016 7:56 PM
  • Ok Thanks.
    Wednesday, December 21, 2016 7:57 PM