If database is exported to .bak file (Right click on db in SSMS>Tasks>Back Up…), importing it in any previous/new version of SQL Server might not work, because of compatibility issues (for example, importing SQL Server 2016 database to SQL Server 2014). That’s why plain old SQL (or T-SQL) that can be used to overcome these backward compatibility issues.
SQL Scripts can be generated by Right click on db>Tasks>Generate Scripts…
Select an option to “Script entire database and all database options”, after that, “Save scripts to specific location”. Select file destination. In “Advanced” don’t forget to enable scripting Triggers or Full-Text Indexes, if you have any. The most important thing is to set “Types of data to script” to “Schema and data”.
After .sql file is created, data can be imported in SQL Server 2014 by using SQL Server Management Studio. But SSMS has a limitation. It doesn’t work with large files because of Memory Overflow. The solution is to use sqlcmd utility to import large files. The pattern is:
sqlcmd -S serverName\instanceName -i C:\scriptName.sql
-S option is server specification. If server is localhost, “.” can be used instead of serverName\instanceName. -i option is script name and location and -E option is for trusted connection.
For example, connecting to local SQL Server instance would look like:
sqlcmd -S . -E -i C:\scriptName.sql