复制数据库的最佳方法 (SQL Server 2008) [英] Best way to copy a database (SQL Server 2008)

问题描述

愚蠢的问题 - 在我想用生产服务器中的实例刷新开发服务器的环境中复制实例的最佳方法是什么?

Dumb question - what's the best way to copy instances in an environment where I want to refresh a development server with instances from a production server?

我已经完成了备份-恢复，但我听说过分离复制附加，一个人甚至告诉我他只会在文件系统之间复制数据文件....

I've done backup-restore, but I've heard detach-copy-attach and one guy even told me he would just copy the datafiles between the filesystems....

这三种(或两种，最后一种听起来有点可疑)是公认的方法吗?

Are these the three (or two, the last one sounds kind of suspect) accepted methods?

我的理解是第二种方法更快，但由于分离方面的原因，需要在源上停机.

My understanding is that the second method is faster but requires downtime on the source because of the detach aspect.

此外，在这种情况下(想要在开发服务器上获得准确的生产副本)转移登录名等的公认做法是什么?我应该只备份和恢复用户数据库 + master + msdb 吗?

Also, in this situation (wanting an exact copy of production on a dev server) what's the accepted practice for transferring logins,etc.? Should I just backup and restore the user databases + master + msdb?

推荐答案

复制数据库的最快方法是 detach-copy-attach 方法，但是生产用户在 prod db 分离时将无法访问数据库.例如，如果您的生产数据库是一个无人在夜间使用的销售点系统，您可以执行类似的操作.

The fastest way to copy a database is to detach-copy-attach method, but the production users will not have database access while the prod db is detached. You can do something like this if your production DB is for example a Point of Sale system that nobody uses during the night.

如果你不能分离生产数据库，你应该使用备份和恢复.

If you cannot detach the production db you should use backup and restore.

如果登录名不在新实例中，您将必须创建它们.我不建议你复制系统数据库.

You will have to create the logins if they are not in the new instance. I do not recommend you to copy the system databases.

您可以使用 SQL Server Management Studio 创建用于创建所需登录名的脚本.右键单击您需要创建的登录名，然后选择脚本登录为/创建.

You can use the SQL Server Management Studio to create the scripts that create the logins you need. Right click on the login you need to create and select Script Login As / Create.

这将列出孤立用户:

EXEC sp_change_users_login 'Report'

如果您已经有此用户的登录 ID 和密码，请执行以下操作来修复它:

If you already have a login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user'

如果您想为此用户创建新的登录 ID 和密码，请执行以下操作进行修复:

If you want to create a new login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

这篇关于复制数据库的最佳方法 (SQL Server 2008)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！