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.
Answer by goelprashant03 ·
Hey @Jeff Moden,
Thank you for your response on this.
We have over 100 customers where we need to consolidate this, some of them do not have much experience with SSMS. I would like to benchmark the use case for medium to small size customers at the least and for some of the big ones, we can see how SSMS works out. Scripting is the easiest way for us to achieve this at this scale.
Request you to kindly assist on this if you can.