January 15, 2018 at 12:33 pm
Currently I have a test environment that has databases in SQL 2016. The test program that uses SQL is not supported using SQL 2016 so 1 thing does not work. I need to backup the data (5 databases) and uninstall and install 2012 or 2014 then restore the data. I know this is not just a backup and restore but am wondering what the options are. Thanks.
January 15, 2018 at 12:52 pm
You can't restore (or attach database files) from a more recent version of SQL Server to an older version.
Most likely you'll need to script/dump the schema & data for each db, uninstall SQL 2016, install SQL 2014/2012, then recreate the databases from your scripts/dump.
One example here: https://www.mssqltips.com/sqlservertip/2810/how-to-migrate-a-sql-server-database-to-a-lower-version/
January 15, 2018 at 2:57 pm
Unfortunately there is no options to restore a backup from a newer version of SQL Server to an older one. In this case you should consider export and import database objects. Try export/import wizard in MS SQL Server management studio - https://docs.microsoft.com/en-us/sql/integration-services/import-export-data/start-the-sql-server-import-and-export-wizard
January 16, 2018 at 7:29 am
Evgeny - Monday, January 15, 2018 2:57 PMUnfortunately there is no options to restore a backup from a newer version of SQL Server to an older one. In this case you should consider export and import database objects. Try export/import wizard in MS SQL Server management studio - https://docs.microsoft.com/en-us/sql/integration-services/import-export-data/start-the-sql-server-import-and-export-wizard
OK, I have 2 - 2016 SQL servers. One production, one test. Can I:
1. Export the test database to the production server under a different database name - Yes I know I can do this.
2. Downgrade SQL on the test server. (Uninstall SQL 2016 and install 2012 or 2014).
3. Export from production (SQL 2016) back to test server (SQL 2012 or 2014).
Sounds possible yes?
January 16, 2018 at 10:34 am
Why use the prod server?
Rather do a side-by-side install on the test server, and then export/import the data from one DB to the other.
January 16, 2018 at 10:59 am
DesNorton - Tuesday, January 16, 2018 10:34 AMWhy use the prod server?Rather do a side-by-side install on the test server, and then export/import the data from one DB to the other.
was not aware I could have several versions of SQL on 1 box.
January 16, 2018 at 12:24 pm
bswhipp - Tuesday, January 16, 2018 10:59 AMDesNorton - Tuesday, January 16, 2018 10:34 AMWhy use the prod server?Rather do a side-by-side install on the test server, and then export/import the data from one DB to the other.
was not aware I could have several versions of SQL on 1 box.
If you did not know what, then you probably didn't know that there can be only 1 default instance. the extra instances will have to be named. For instance, the default instance is usually named after the hostname, i.e. you can connect to it using SSMS by specifying hostname. When you install an additional instance, you will need to append an instance name after the hostname. For example, MyHost\SQL2012 or MyHost\SQL2014. This named instance will not be listening on the default port 1433.
Regardless, you cannot attach or restore SQL2016 database to an instance running earlier versions. So you have got some work on your hand.
January 16, 2018 at 12:30 pm
RandomStream - Tuesday, January 16, 2018 12:24 PMbswhipp - Tuesday, January 16, 2018 10:59 AMDesNorton - Tuesday, January 16, 2018 10:34 AMWhy use the prod server?Rather do a side-by-side install on the test server, and then export/import the data from one DB to the other.
was not aware I could have several versions of SQL on 1 box.
If you did not know what, then you probably didn't know that there can be only 1 default instance. the extra instances will have to be named. For instance, the default instance is usually named after the hostname, i.e. you can connect to it using SSMS by specifying hostname. When you install an additional instance, you will need to append an instance name after the hostname. For example, MyHost\SQL2012 or MyHost\SQL2014. This named instance will not be listening on the default port 1433.
Regardless, you cannot attach or restore SQL2016 database to an instance running earlier versions. So you have got some work on your hand.
Right, I know the instances thing but was not aware you could install different versions except for SQL lite... I am running the scripts and will just uninstall 2016 and install 2012. May take longer but I have time. Thanks all.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply