复制数据库的最佳方式(SQLServer2008)

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

我做过备份-恢复，但是我听说过分离-复制-附加，甚至有人告诉我他只是在文件系统之间复制数据文件... ..。

这是三种(或两种，最后一种听起来有点可疑)被接受的方法吗？

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

此外，在这种情况下(想要在开发服务器上获得产品的精确副本)传输登录的公认做法是什么，等等？我应该只备份和恢复用户数据库 + 主 + msdb？

103950 次浏览
小开

很难分离您的生产 dB 或其他正在运行的 dB 并处理停机时间，所以我几乎总是使用备份/恢复方法。

如果您还想确保您的登录同步，请使用存储过程 Sp _ help _ revlogin检查 MSKB 文章。

小开

如果要获取活动数据库的副本，请执行 Backup/Restore 方法。

[在 SQLS2000中，不确定2008: ]只要记住，如果你在这个数据库中使用的是 SQL Server 帐户，而不是 Windows 帐户，如果开发服务器上的主数据库是不同的或不同步的，用户帐户将不会翻译当你还原。我听说过一种 SP 可以重新映射它们，但我不记得是哪一个了。

小开
最佳答案

复制数据库的最快方法是分离-复制-附加方法，但是在分离 prod db 时，生产用户将无法访问数据库。如果您的生产数据库是一个销售点系统，没有人在夜间使用，您可以这样做。

如果无法分离生产数据库，则应使用备份和还原。

如果登录名不在新实例中，则必须创建登录名。我不建议您复制系统数据库。

可以使用 SQLServerManagementStudio 创建创建所需登录名的脚本。右键单击需要创建的登录名，并选择 ScriptLoginAs/Create。

这将列出孤儿用户:

EXEC sp_change_users_login 'Report'

如果您已经为这个用户设置了登录 ID 和密码，请执行以下操作来修复它:

EXEC sp_change_users_login 'Auto_Fix', 'user'

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

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

我运行 SP 来删除这些表，然后使用 DTS 包将最新的生产表导入到我的开发框中。 然后我回家，第二天早上再回来。这不是很优雅，但对我很有效。

小开

更新:
下面的建议告诉您如何使用 SQLServerManagementStudio 编写数据库脚本，但 SSMS 中的默认设置忽略了数据库的所有关键部分(如索引和触发器!)出于某种原因。因此，我创建了自己的程序来正确地编写一个数据库脚本，其中包括您可能已经添加的几乎所有类型的 DB 对象。我建议用这个代替。它被称为 SQL Server Scripter，可以在这里找到:
Https://bitbucket.org/jez9999/sqlserverscripter

我很惊讶没有人提到这一点，因为它真的很有用: 您可以使用 SQLServerManagementStudio 将数据库(其模式 还有数据)转储到脚本中。

右键单击数据库，选择“ Tasks | Generate Scripts...”，然后选择脚本特定的数据库对象。选择要复制到新 DB 的数据库(您可能至少要选择 Tables 和 Schema)。然后，在“设置脚本选项”屏幕上，单击“高级”，向下滚动到“脚本的数据类型”，并选择“模式和数据”。单击 OK，完成脚本的生成。您将看到，现在已经为您生成了一个长脚本，用于创建数据库的表 还有将数据插入到表中！然后可以创建一个新数据库，并更改脚本顶部的 USE [DbName]语句，以反映要将旧数据库复制到的新数据库的名称。运行脚本，旧数据库的模式和数据将被复制到新数据库！

这允许您在 SQLServerManagement 工作室中完成整个操作，而且不需要触及文件系统。

小开

分离/复制/附加方法将关闭数据库。

备份/恢复只有在您对生产服务器具有写权限时才能工作。我和亚马逊 RDS 合作，但是我没有。

导入/导出方法实际上不能工作，因为有外键——除非您按照表之间引用的顺序逐个执行表。可以对新数据库执行导入/导出操作。它将复制所有表和数据，但不复制外键。

这听起来像是需要对数据库进行的常见操作。为什么 SQLServer 不能正确地处理这个问题？每次我不得不这么做的时候都很沮丧。

也就是说，我遇到的唯一的 没有痛苦解决方案是由社区维护的 SQL Azure 迁移工具。它也适用于 SQLServer。

小开

最简单的方法就是写剧本。

在生产环境下运行:

USE MASTER;


BACKUP DATABASE [MyDatabase]
TO DISK = 'C:\temp\MyDatabase1.bak' -- some writeable folder.
WITH COPY_ONLY

这个命令将数据库的完整备份到单个文件中，而不会影响生产可用性或备份计划等。

要还原，只需在开发人员或测试 SQLServer 上运行此命令:

USE MASTER;


RESTORE DATABASE [MyDatabase]
FROM DISK = 'C:\temp\MyDatabase1.bak'
WITH
MOVE 'MyDatabase'   TO 'C:\Sql\MyDatabase.mdf', -- or wherever these live on target
MOVE 'MyDatabase_log'   TO 'C:\Sql\MyDatabase_log.ldf',
REPLACE, RECOVERY

然后在每个服务器上保存这些脚本。

编辑:
如果在还原逻辑名称时出现错误，您可以这样得到它们:

RESTORE FILELISTONLY
FROM disk = 'C:\temp\MyDatabaseName1.bak'

如果您使用的是 SQL Server 登录名(而不是 Windows 身份验证) ，那么您可以在每次恢复(在 dev/test 机器上)之后运行:

use MyDatabaseName;
sp_change_users_login 'Auto_Fix', 'userloginname', null, 'userpassword';
小开

下面是我如何将数据库从 production env 复制到本地 env:

  1. 在本地 sql 服务器中创建一个空数据库
  2. 右键单击 new database-> asks-> import data
  3. 在 SQLServer 导入和导出向导中，选择产品 env 的 servername 作为数据源。并选择新数据库作为目标数据。

提交答案