Copy database tables with constraints

RIAS
RIAS used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
HuaMin ChenProblem resolver

Commented:
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?

Author

Commented:
Thanks,where is the export the schema
Commented:
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.
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

HuaMin ChenProblem resolver
Commented:
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.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
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.

Author

Commented:
Thanks Experts! Thanks Vitor !

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial