How Can I Transfer the Database from MS SQL 2008 R2 to a Newer MS SQL Version


This guide explains how to transfer the MS SQL 2008 R2 database to a newer version of MS SQL (and can be applied to all newer versions).

The transfer can be performed by using MS SQL Studio, the latest version of which can be found here: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15.


To transfer the database, do the following:

1. Open MS SQL Studio, and click the Connect button in the top left corner, and then select the Database Engine option. After entering the credentials, the MS SQL server details can be found in the Object Explorer menu on the left.

   

2. Right-click on the required database (e.g. SERVER.EkranManagementDatabase) > Tasks > Back Up.

   

3. The backup folder or the name of the backup file can be changed, by clicking the Add button > "...", and then clicking OK when finished.  

NOTE: The location of the backup should be noted for use in Step 5.

   

4. Connect to the destination MS SQL Server (as shown in Step 1), and create a new database by right-clicking on the Databases folder, and then select the New Database option.

   

    Specify the database name (it must be the same as the backed up one), and click OK.

   

5. Right-click on the database created, and select Tasks > Restore > Database.

   

    In the Source menu, select the Device option, and click the "..." button > Add, and then select the database backed up previously.

   

    Open the Files page (by using the corresponding button in the top left corner), and select the checkbox next to the Relocate all files to folder option, and then click OK after completing all steps.

   

    Open the Options page, and select the checkbox next to the Overwrite the existing database (WITH REPLACE) option. 

   

    Click OK after completing all the steps above, and if the restoration is successful, the following message will be displayed: