انتقال دیتابیس از نسخه جدید به نسخه قدیم توسط SQL Server

این عملیات برای تمام نسخه های SQL Server امکان پذیر می باشد.


  • SQL Server 2005
  • SQL Server 2008
  • SQL Server 2008 R2
  • SQL Server 2012
  • SQL Server 2014
  • SQL Server 2016
  • SQL Server 2017
  • SQL Server 2019




1. Use Generate Scripts wizard of SQL Server Management Studio in Higher version

In this step, we will first script the schema of the desired Database on SQL Server 2012 instance to migrate the database to SQL Server 2008 R2 using Generate Scripts wizard of the SQL Server Management Studio.

  • Launch Microsoft SQL Server Management Studio (SSMS) and Go to Object Explorer.
  • Select the desired database and right-click on it.
  • Click on Tasks and choose ‘Generate Scripts’
  • Generate and Publish Scripts wizard will be opened.
  • Click on Next & proceed to Choose Objects page
  • On the page, choose the option ‘Script entire database and all database objects’ and click on Next to move to next Tab. i.e. ‘Set Scripting Options’
  • Define the location to save the script file on the Set Scripting Options Tab
  • Click on Advanced button to specify scripting options
  • In Advanced scripting options, we will modify some of the options
    Script for Server Version →SQL Server 2008 R2
    Types of data to script → Schema and Data
    Set Script primary key, Triggers, Indexes options True
  • Click on OK after making the changes in Advanced Scripting Options and go back to Set Scripting Options Page. Enter ‘Next’ to proceed to Summary Tab
  • Click on Next on Summary Page after review the selections made.
  • Lastly, click on Finish button to close the Generate and Publish Scripts Wizard after the successful completion of script generation process.

2. Connect to lower version, run the SQL scripts to create database schema & copy data

In the second part of the procedure, we will connect to the lower version i.e. SQL Server 2008 R2 Instance and run the scripts generated earlier in the first part to create the database schema and copy all its data.

  • We will go to Object Explorer and Connect to the SQL Server 2008.
  • Open the script created by going to File in SQL Server Management Studio →OpenFile
  • Select the script file created and click on open to use the script in SSMS.
  • Change the script to define the location of the Database data and log files.
  • Execute the script on create the database on SQL Server 2008 instance
  • After the scripts have been executed successfully, refresh the database folder in Object Explorer of SSMS.
  • Click on the database to check if all the data has been successfully downgraded from SQL Server 2012 to SQL Server 2008 R2.

منبع:

https://www.sqlserverlogexplorer.com/migrating-database-to-lower-version/




برچسب‌ها


ارسال پیام