Ellbar
0
What is practical difference between altering schema and altering database in SQL Server?
I have production database. There is also the schema which is newer and contains new elements (views, stored procedures, some changes in tables).
What is the easiest way to update production to be the same as new schema. I don't know all the changes so is there an easy way to do this, without breaking the data?
What is practical difference between altering schema and altering database in SQL SERVER?
In SQL Server, a Schema are the logical objects within the database (tables, files, etc.) while a Database is the main container where schemas live within. So altering a schema (like table) is to change that object and only affecting that object. While altering a database is changing the database's property, whatever that may be. This is different than other RDBMS like MySQL where schema is synonymous with database.
Updating production database should come with a tested plan. Capture a backup of the database before doing any upgrade or change.
Views and procs (and function, triggers) are simply drop and create, or alter. But handling the data properly on upgrade will be the difficult part but would also be the most important. I would suggest generating table upgrade scripts to handle table changes to minimize/eliminate data issue. There are tools out there that can generate it for you like Red Gate Compare/Data Compare. Or you can handle the script writing yourself if there are specialized logic to be done when upgrading records.
I would suggest too to keep a copy of the old database accessible either within the same server or another server incase you need to reference the prior data.