Database: SQL Server 2012, Size 2TB
Hi, I am new to SQL server. Would be grateful if I could get some assistance on below query: We are planning to consolidate multiple databases.dbo.* objects under a single database as different schemas (databaseN.SchemaN.*). Thus we need to prepare scripts to move database1.dbo.* objects in another database as a different schema (e.g. database2.schema2.*) including all the dependent objects (need exact replica). This needs to be done without using any tools(SSMS, ApexSQL etc). Kindly guide me how should I go about scripting this. I was thinking on the lines of below approach:
1. Extract complete metadata (including all constraints/triggers/indexes/keys/partitions etc)
2. Extract data
3. Execute metadata scripts on target
4. disable all relational constraints and triggers
5. insert all the extracted data
6. enable all the relational constraints and triggers.
If this approach is fine, can I get some assistance in how to go about scripting this (is there a standard way of scripting this). Also, please suggest if any other approach is more suited. Some tables are partitioned and relatively huge in size (50-100GB). Thanks in advance.
