We have a development database, a test database, and production. The only way for data to enter one of these three systems is via an SSIS package, which means that for the last month of development, production has been stable, with no additions made. We keep, as far as data is concerned, the three systems in sync. However; our data modeler made suggestions that I rolled into development on the existing structure of our tables. I rolled this into test, with the new data we will be using going forward. We'd like to roll these tables from Test directly into production. What I'm wondering is if the constraints that I've set up in Test will go with the table or will I have to set them up in Production?
SQL Server Migration – Do Constraints Move Between Databases?
migrationsql server
Related Question
- Sql-server – Creating a test database from Production database (different machines & servers)
- Sql-server – Best practise for copying a database from production
- SQL Server – Restoring Database with Data Compression
- Migrate small sets of data from production to staging/sandbox
- Sql-server – SQL Server AlwaysOn from multiple origins to a single secondary replica server
- MySQL – How to Migrate Schema Structure from One Server to Another
- Sql-server – Moving Databases to New Datacenters
Best Answer
Yes, in SQL Server constraints are defined within each database within the table definition. So if you physically promote a database to a higher environment through backup and restore, the constraints will remain within the database. If you script out objects, the default behaviour will be for the constraints to be scripted out with the table definition.
Microsoft's TechNet has a decent explanation: https://technet.microsoft.com/en-us/library/ms189862(v=sql.105).aspx