SQL Server 2008自动备份 [英] SQL Server 2008 Auto Backup

问题描述

我们希望每晚从生产服务器数据库中更新测试服务器数据库，以确保我们使用最新数据进行开发。但是，我们希望确保开发过程中当前正在处理的所有fn，sp等都不会被备份过程所覆盖。我们正在考虑做的事情是拥有一个备份程序，该程序可以保存开发人员选择的对象，而备份程序则可以在备份过程完成后将其添加回。

We want to have our test servers databases updated from our production server databases on a nightly basis to ensure we're developing on the most recent data. We, however, want to ensure that any fn, sp, etc that we're currently working on in the development environment doesn't get overwritten by the backup process. What we were thinking of doing was having a prebackup program that saves objects selected by our developers and a postbackup program to add them back in after the backup process is complete.

I想知道其他开发人员在这种情况下正在做什么。是否有一个现有的工具可以为我们执行此操作，该工具可以每天自动运行，并允许我们设置不覆盖的对象（无需系统管理员的注意每天运行它。）

I was wondering what other developers have been doing in a situation like this. Is there an existing tool to do this for us that can run automatically on a daily basis and allow us to set objects not to overwrite (without requiring the attention of a sysadmin to run it daily).

推荐答案

我们数据库中的所有对象都保存在代码中-表，视图，触发器，存储过程等所有内容-如果我们希望在数据库中找到它，则应该在我们可以运行的代码中使用DDL。实际的模式更改已版本化-因此数据库中存在一个表，该表称为模式版本 n，如果这不是当前版本（根据更新代码），则我们进行必要的更改。

All the objects in our databases are maintained in code - tables, view, triggers, stored procedures, everything - if we expect to find it in the database then it should be in DDL in code that we can run. Actual schema changes are versioned - so there's a table in the database that says this is schema version "n" and if this is not the current version (according to the update code) then we make the necessary changes.

我们努力将触发器和视图分开-尽管我们可能应该对SP和FN进行很多操作-通过拖放并重新创建对当前模式版本有效的代码。因此，删除并重新创建不是表的任何内容应该是安全的，尽管如果对象之间存在依赖关系，则放置和创建都会存在排序问题。通常，这样做的好处是，我们可以放心地将架构从新引入到当前，并确信该架构的任何实例都是一致的。

We endeavour to separate out triggers and views - don't, although we probably should, do much with SP and FN - with drop and re-create code that is valid for the current schema version. Accordingly it should be "safe" to drop and recreate anything that isn't a table, although there will be sequencing issues with both the drop and the create if there are dependencies between objects. The nice thing about this generally is that we can confidently bring a schema from new to current and have confidence that any instance of the schema is consistent.

根据您的情况扩展，如果您能够运行模式更新代码，包括根据当前定义重新创建所有数据库对象的代码，那么您的问题就应该消失了……备份，还原，运行模式维护逻辑。这将带来进一步的好处，即您可以在开发服务器中引入架构（表）更改，并且仍保持相同的更新逻辑。

Expanding to your case, if you have the ability to run the schema update code including the code to recreate all the database objects according to the current definitions then your problem should substantially go away... backup, restore, run schema maint logic. This would have the further benefit that you can introduce schema (table) changes in the dev servers and still keep the same update logic.

我知道这不是完全通用解决方案。并值得注意的是，每个开发人员都可以使用数据库更好地工作（我是一个老式的程序员，所以我认为所有问题都具有基于代码的解决方案（-:)，但作为一种通用方法，我认为它具有相当大的优点，因为它可以您可以采用一致的机制来解决许多问题。

I know this isn't a completely generic solution. And its worth noting that it probably works better with database per developer (I'm an old fashioned programmer, so I see all problems as having code based solutions (-:) but as a general approach I think it has considerable merit because it gives you a consistent mechanism to address a number of problems.

这篇关于SQL Server 2008自动备份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！