Automatic backup of SQL Server 2008

Question

We want our test server databases to be updated from our production server databases at night to ensure that we are developing the latest data. However, we want to ensure that any fn, sp, etc. that we are currently working in the development environment are not overwritten by the backup process. What we thought to do was to have a prebackup program that saves the objects selected by our developers and a postbackup program to add them back after the backup process is complete.

I was wondering what other developers are doing in this situation. Do we have an existing tool for this that can be launched automatically every day and allow us to set objects that cannot be overwritten (without the need to run a system administrator daily).

Answer 1

All objects in our databases are stored in code tables, views, triggers, stored procedures, all - if we expect to find it in the database, then it should be in DDL in the code that we can run. Actual changes to the scheme are versioned - therefore, the database has a table that says that this is the version of the scheme "n", and if this is not the current version (in accordance with the update code), we make the necessary changes.

We are trying to separate triggers and views — not necessary, although we probably have a lot to do with SP and FN — removing and re-creating the code that is valid for the current version of the circuit. Accordingly, it should be “safe” to drop and recreate everything that is not a table, although there will be problems with sequencing both with the fall and with the creation, if there are dependencies between the objects. The best part is that we can confidently bring the circuit from new to current and make sure that any instance of the circuit is consistent.

Expanding to your business, if you have the opportunity to run the schema update code, including the code to recreate all database objects in accordance with the current definitions, your problem should disappear significantly ... backup, restore, run the maint logic scheme. This will have the added benefit that you can introduce schema (table) changes in dev servers and still maintain the same update logic.

I know that this is not a very general solution. And it is worth noting that it probably works better with a database for each developer (I am an old-fashioned programmer, so I see all problems as having solutions based on code (- :), but as a general approach, I think that it has significant advantages, because it gives you a consistent mechanism for solving a number of problems.