Migrating data between different versions of MS SQL

MS SQL server has built-in methods to migrate data from one version to another. Two of these methods are described below:

1. MS SQL DB Migration using Detach and Attach Method

Recommended for: Full DB backup, moving DB data to another drive, moving DB data between different versions of SQL server (Ex: SQL Server 2008 R2 to SQL Server 2012).

  • Login to Microsoft SQL Server Management Studio.
  • Right click on the DB that you want to move (Eg: adap). Select Task --> Detach.
  • In the Detach DB wizard --> Select both the check boxes (Drop Connections and Update Statistics) --> Ok.
  • Go to the DB storage path (Ex: C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA) --> Copy the .mdf and .LDF files (Ex: adap.mdf and adap_log.LDF).
  • Save both of the above files in a desired location.
  • Login to Microsoft SQL Server Management Studio.
  • Right click on the destination database and select Task --> Attach.
  • In the Attach DB wizard, click Add button.
  • Select the .mdf file from where it has been saved --> Ok (the .LDF file will get selected automatically).
  • Go to \conf folder --> Open database_params --> Change the SQL server location and DatabaseName, so that the product is pointed to the new DB.

2. MS SQL DB Migration using Import Data Method

Recommended for: Moving particular table data from one DB to another.

  • Login to Microsoft SQL Server Management Studio.
  • Right click on the DB that you want the data moved to (Ex: adap_new). Select Tasks --> Import Data.
  • The SQL Server Import and Export wizard opens up --> Click Next
  • In the Data Source wizard, that opens up, specify: (In the Data source drop down, select SQL Server Native Client if it's not selected already.)
    • Server Name.
    • Authentication (Used to login to SSMS).
    • Database.
    • Click Next.
  • Under the Destination wizard, enter the following: (In the Data source drop down, select SQL Server Native Client if it's not selected already. )
    • Server Name (will get automatically detected, you can change the destination, if you want).
    • Authentication.
    • Database (will get automatically detected, you can change the destination, if you want).
    • Click Next.
  • Under Specify Table Copy or Query --> Choose Copy Data From One or More Tables/Views --> Next.
  • Under Select Source Tables and Views --> Choose the tables to be copied from source, select the tables from the left column using Edit Mappings --> Select particular table operations --> Next.
  • Check the Run Immediately box--> Next.
  • Click Finish.
Copyright © 2019, ZOHO Corp. All Rights Reserved.
ManageEngine
Get download link