I have a high availability
production server that runs SQL Server. It is constantly being read/write to by hundreds of users.
I have an exact copy of the database on a
testing server and do all the development in that environment. Every few weeks, there will be major changes made to the front-end application and the database itself (e.g. new stored procedures, new columns, new tables).
Because the data on the
production server is always the latest, I don't want to lose that data when I want to make changes to the database schema. The current process for deploying changes is:
- backup the
productionserver and pause it
- restore it into the
- make all database changes in the
- Backup the
- Restore the new version to the
productionserver and un-pause it
The steps seem convoluted and is causing unnecessary downtime on the production server. What I would like to do is update the
production server database in the background while still allowing users to continue reading/writing the database.
So far I can see that you can use copy database wizard or use scripts to make changes, but they seem to want to drop/create the database on the
production server to make changes. How could I achieve a less severe way of updating the
production server's database?