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?
1 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
