We help IT Professionals succeed at work.

Import older database into newer version of mssql server

Hi

Im upgrading from mssql 2012 to 2017. When i export from 2012 and import the databases into 2017, are they automatically upgraded to the new database engine or can i choose to upgrade? If its possible to not upgrade when importing (some programs do not support 2017) can i choose later to upgrade the database?
If its possible to run databases on different versions on the same server, is there any query where i can see the database version?
Im planning to move around 10 different databases into the same database instance

BR
Carlos
Comment
Watch Question

AlexA lack of information provides a lack of a decent solution.

Commented:
10 different databases into the same database instance

Before you do that, check the databases, any larger databases or heavily used ones should go into their own instance. If you're throwing everything into the same instance make sure you split out the memory allocation correctly.

Realistically, you have 10 databases, I'd check each of those first to find the compatibility and then decide if you can move them over.

There is a wizard that will allow you to select specific options for what happens to the database on import. I'd discuss the best option with the suppliers of the 10 databases and then go with what they recommend.
What means "some programs do not support 2017"?
SQL Server backward compatibility is exceptional so you don't need to aware of some missing feature. Some queries can provide different performance but that's not so big deal in standard applications because you may still tune them later.

If you restore backup from 2012 to 2017 then everything works obviously. Just remember the restore in an opposite way is impossible (except the cases when you script all the structures and data). Everything you may do after the restore is to set different compatibility level for your databases on 2017 but it can also wait. Higher compatibility level means the possibility to use new 2017 features.

You may check the compatibility level by issuing
SELECT * FROM sys.databases

If you are unsure about your programs compatibility then create a test instance of 2017 and test restored databases in this test instance whereas the production will still run on 2012.

More important compatibility aspect than the compatibility level setting is to have the same collation on system databases in 2017 as they were set on 2012. You may see collations in the above query results.
Senior DBA
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:

The internal tables / system views are automatically upgraded to the SQL 2017 format.  There's no way to avoid that.


But you can leave the dbs in compatibility_level 110 (SQL 2012), and SQL will continue to be translated and processed by SQL 2012 rules.  You can upgrade a db(s) to the 2017 level whenever you want to by changing the compatibility level.



Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:

As to 10 dbs in one instance, that should be no problem.  I have ~1000 dbs in a dev/qa instance at our site!

carlos sotoIT Administrator

Author

Commented:
hi

thanks for all the answers, its really helping me grip this.

so when importing a database of an older version, sql manager will ask me to upgrade it or is an option i have to check in for it to do it?
Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:

SQL will automatically upgrade the db, you don't have any choices to make there.  You're not allowed to choose anything different for updating the internal structures of the dbs.