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
- Open SQL Server Management Studio.
- Right click on the database you are working with.
- Choose
Tasks
>Generate Scripts
- You will be presented with a wizard screen, on the introduction you can click
Next
. - 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
. - In the output type, choose
Save scripts to a specific location
. - 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
.
- Click
Next
. - 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.
- Click
Next
. - Wait for it to run, you should see green ticks all the way down if it was successful.
- Click on
Finish
- Then go to the location where you saved the script, and copy file to environment B.
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.ldf
toC:\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.
That’s it!
You’re all done. You should be able to start using the database now.