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.
- Open SQL Server Management Studio.
- Right click on the database you are working with.
- You will be presented with a wizard screen, on the introduction you can click
- 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
- In the output type, choose
Save scripts to a specific location.
- Save to file Click on
Script for Server Version, choose the version you want, mine is 2012.
Types of data to script, choose Schema and data.
- Leave the other settings as they were.
- Click on
- Files to generate -
- File name - give the script a name with the extension .sql.
- Save as
- 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.
- Wait for it to run, you should see green ticks all the way down if it was successful.
- Click on
- Then go to the location where you saved the script, and copy file to environment B.
- Open the script
- 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.ldfto
C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\ganteng_log.ldf. Do the same for the *.mdf file.
- Run the script
- 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.
You’re all done. You should be able to start using the database now.