Copy database tables with constraints

Hello,
I am trying to create a copy of a database in sql server 2016 from  database from sql server 2014 with import/export wizard.
The problem is it does not copy the constraints of the table.
i have tried Enable IDENTITY insert.

Regards
RIASAsked:
Who is Participating?
 
Deepak ChauhanSQL Server DBACommented:
I am trying to create a copy of a database in sql server 2016 from  database

Option 1 - If you want to copy all tables and data from SQL server 2014 to 2016, then backup and restore is best option.
1. Take the backup of SQL 2014 database
2. restore the backup file into 2016 database.

Option 2- f you are copying few tables then you can generate schema only script and execute this script into sql 2016 database.
1. Right click on database > task . generate script > next > click on Advance button > here you have multiple option to generate script for Data only , for Schema only , for Both data and schema... this is up to you.

Generate Script
Option 3. Generate script only using above steps , create tables and other objects into sql 2016 database and export data using Import \Export wizard.
1
 
HuaMin ChenProblem resolverCommented:
Hi,
If you're already exported the whole database/schema, you should be able to get also the constraints.

Did you try to export the schema instead?
0
 
RIASAuthor Commented:
Thanks,where is the export the schema
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
HuaMin ChenProblem resolverCommented:
Rias,
Here is the way to do Backup, from existing schema
19q.png
Afterwards, you can choose restore like
19r.png
to further choose your Backup file created, to retrieve the whole schema, within another machine.
1
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I would recommend going for Deepak's option 3.
Option 2 isn't bad for small tables. For large tables it will take longer so go for option 3.
1
 
RIASAuthor Commented:
Thanks Experts! Thanks Vitor !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.