生产数据库的夜间可导入或可附加副本
我们希望能够每晚制作生产数据库的副本/备份/快照，以便我们可以将其导入开发环境中。
我们不想将日志传送到开发环境，因为它需要我们可以在任何时候重置到生产数据库的最后一个副本。
我们需要能够清除某些日志记录和/或其他无用或沉重的表，这些表只会使副本膨胀。
我们更喜欢附加/分离方法，而不是诸如 sql server 发布向导之类的方法，因为附加比导入快得多。
我应该提到我们只有 SQL Server Standard，因此某些功能将不可用。
最好的方法是什么？
We would like to be able to nightly make a copy/backup/snapshot of a production database so that we can import it in the dev environment.
We don't want to log ship to the dev environment because it needs to be something we can reset whenever we like to the last taken copy of the production database.
We need to be able to clear certain logging and/or otherwise useless or heavy tables that would just bloat the copy.
We prefer the attach/detach method as opposed to something like sql server publishing wizard because of how much faster an attach is than an import.
I should mention we only have SQL Server Standard, so some features won't be available.
What's the best way to do this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我猜您已经在定期备份生产数据库了？ 如果不是，请停止阅读此回复并立即进行设置。
我建议您编写一个自动运行的脚本（例如每天一次）：
您可以编写一个简单的脚本来执行此操作，并使用 isql.exe 命令行工具执行它。
I'm guessing that you are already doing regular backups of your production database? If you aren't, stop reading this reply and go set it up right now.
I'd recommend that you write a script that automatically runs, say once a day, that:
You can write a simple script to do this and execute it using the isql.exe command line tool.
MSDN
我想说在 SQL 代理作业中使用这些过程（使用 master.xp_cmdshell 执行复制）。
MSDN
I'd say use those procedures inside a SQL Agent job (use master.xp_cmdshell to perform the copy).
您可能希望将大型表放在自己的分区上，并让该分区属于不同的文件组。 您将备份然后备份并恢复主文件组。
您可能还想考虑进行增量备份。 比如说，每个周末进行一次完整备份，每晚进行一次增量备份。 我还没有做过文件组备份，所以我不知道这些是否可以很好地协同工作。
You might want to put the big huge tables on their own partition and have this partition belong to a different file group. You would backup then backup and restore the main file group.
You might want to also consider doing incremental backups. Say, a full backup every weekend and an incremental every night. I haven't done file group backups, so I don't know if these work well together.