May 16, 2019

Backup and Restore Database SQL Server to Lower Version

How to Restore Database SQL server to lower version?

If you want to migrate a database from environment A with SQL Server version A to environment B with SQL Server version A, you can use the script or backup and restore function. But if you want to migrate the database from environment A with SQL Server version A to environment B with SQL Server version B, when running the backup function in environment A and you will get problems when running the restore function in environment B.

Upgrade in one environment? I think it’s not an efficient way. What do you do? Here is an alternative if you want to migrate a database with a different environment.

Environment A

  1. Open SQL Server Management Studio.
  2. Right click on the database you are working with.
  3. Choose Tasks > Generate Scripts
  4. You will be presented with a wizard screen, on the introduction you can click Next.
  5. Choose whether to script the entire database and objects or to select them individually. I like to leave it as the first option, everything. Click Next.
  6. In the output type, choose Save scripts to a specific location.
  7. Save to file Click on Advanced:
  • find Script for Server Version, choose the version you want, mine is 2012.
  • find Types of data to script, choose Schema and data.
  • Leave the other settings as they were.
  • Click on OK.
  • Files to generate - Single file.
  • File name - give the script a name with the extension .sql.
  • Save as Unicode text.
  1. Click Next.
  2. Open out options using the plus sign, check the settings are all ok in the summary - you are mainly looking at the version and the types of data to script.
  3. Click Next.
  4. Wait for it to run, you should see green ticks all the way down if it was successful.
  5. Click on Finish
  6. Then go to the location where you saved the script, and copy file to environment B.

Environment B

  1. Open the script
  2. Verify that the MSSQL data file path (.mdf and log.ldf files) is correct. Example, if you back up a database from MSSQL 2014 and want to restore to MSSQL 2012, you need to update the path that it was originally from C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\ganteng_log.ldf to C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\ganteng_log.ldf. Do the same for the *.mdf file.
  3. Run the script
  4. You may get some errors like this: Cannot add the Principal ‘CodeShare’ because it does not exist or you do not have permission. Just ignore it, the message. This does not cause any problems.

Note: pay attention to the mssql instance name and please change it according to the instance name used. In this example, at MSSQL 2014 I used the SQLEXPRESS instance name and at MSSQL 2012 I used the MSSQLSERVER instance name. To find out the name of the instance, please open the C:\Program Files\Microsoft SQL Server directory and find the directory named MSSQL<version>.xxx which xxx is the name of the instance.

That’s it!

You’re all done. You should be able to start using the database now.