How to export Sql Server DB schema as an ALTER statements?

Question

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?

• John M: 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.
• Aaron Bertrand: ^ 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.

Answer 1

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+''') 
ALTER TABLE ' + QUOTENAME(t.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') 
ALTER TABLE [users]
ADD [id] int NOT NULL IDENTITY
IF NOT EXISTS(SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('users') AND name='login') 
ALTER TABLE [users]
ADD [login] varchar(100) NOT NULL 
IF NOT EXISTS(SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('users') AND name='email') 
ALTER TABLE [users]
ADD [email] varchar(100) NULL 

• Pawan Kumar: This is will be a huge script but definitely I will try this, Thanks.
• Jan Madeyski: 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.
• Aaron Bertrand: 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.