I have a 2 months old DB & I want to fetch new Schema for that DB without deleting the data in it using ALTER statements. Is it possible?

Currently I am taking a backup of table data of Old DB in some temporary DB & then replacing the Old DB with the new one & Inserting the backup data from the temporary DB. What could be the best solution to migrate the Schema?

  • 1
    The easiest solution would be to use a dedicated tool such as Redgate SQL compare, or Microsoft Visual Studio SQL Data Tools. Both can perform a comparison of two database schemas and generate an update script.
    – John M
    Oct 16 '20 at 9:15
  • ^ yes please don't reinvent the wheel. There are so many edge cases you won't consider when writing your own, and so many bugs you don't even realize you'll have that they've already solved. See this and this. Oct 16 '20 at 12:55

I would write something like this, and first run in on the new database - to get the migration code, and then running resulting migration code on the old database - to alter the schema.

It's just a part to add missing columns and not complete. You should also check for is_identity, constraints, other data types and etc.

Maybe there is a complete code in the internet...

SELECT 'IF NOT EXISTS(SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('''+t.name+''') AND name='''+c.name+''') 
ADD ' + QUOTENAME(c.name) + ' ' + tt.name
       + CASE WHEN tt.name IN ('varchar','nvarchar') THEN '(' + CAST(c.max_length as varchar(10)) + ')' 
              ELSE '' END
       + ' '
       + CASE WHEN c.is_nullable = 1 THEN 'NULL' ELSE 'NOT NULL' END + char(13)+char(10)+char(13)+char(10)
FROM sys.tables t
JOIN sys.columns c ON c.object_id = t.object_id
JOIN sys.types tt ON tt.user_type_id = c.user_type_id

Result - migration code:

IF NOT EXISTS(SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('users') AND name='id') 

IF NOT EXISTS(SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('users') AND name='login') 
ADD [login] varchar(100) NOT NULL 

IF NOT EXISTS(SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('users') AND name='email') 
ADD [email] varchar(100) NULL 
  • This is will be a huge script but definitely I will try this, Thanks. Oct 16 '20 at 7:49
  • Another solution could be creating a database trigger on the new database, and storing all DDL statements in a table (like ddl_statements_history) and then get it all and execute on the old database. Oct 16 '20 at 7:54
  • Be careful about blindly replaying all DDL history, if I change from smallint to int to bigint you want to just apply smallint to bigint, or if I change from char to nchar and then change back, that should be a no-op. Oct 16 '20 at 17:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.