☰

SQL Server 备份 - 在实例之间传输

tag-icon • tag-icon sql-server database-backup database-restore
SQL Server 备份 - 在实例之间传输

我在一家开发商店工作，我们主要使用 SQL Server 进行日常开发和测试。

有时，某人的机器（通常是笔记本电脑）上会出现问题，我想在我的机器上重现这些问题。因此，我想将他们的数据库传输到我的机器上。这种传输通常是离线的，笔记本电脑在其他地方，因此我们会转储数据库，通过 FTP 传输并恢复它们，因此通常没有在线传输机制。通常有 5 个以上的转储文件需要恢复。

当前过程是使用backup database+restore database并传递转储文件。这可以正常工作，但由于备份中存储了物理名称，因此非常麻烦。在执行还原时，我必须将with move逻辑文件映射到备份文件中，以映射到我的机器上的 mdf/ldf 文件的物理位置。

鉴于 SQL Server 知道创建这些文件的默认位置，像create database这样工作，我有什么办法可以想出一种更智能的方法将这些数据库从一个实例移动到另一个实例？如果数据库名称完全相同（如果可以从转储文件中抓取）并且物理位置可以从目标实例默认值推断出来，我会非常高兴。

笔记：顺便说一句，Management Studio 恢复选项（GUI）似乎足够聪明，可以管理这一点，而无需手动详细说明物理位置。

答案1

SQL 实例的默认文件夹存储在服务器的注册表中

HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\[your specific instance ID]\MSSQLServer

您可以使用xp_regread此值提取数据，然后在恢复脚本中使用它。以下是示例：

DECLARE @DataDirectory VARCHAR(255)
DECLARE @LogDirectory VARCHAR(255)

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
  @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer',
  @value_name='DefaultData',
  @DataDirectory=@DataDirectory OUTPUT

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
  @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer',
  @value_name='DefaultLog',
  @LogDirectory=@LogDirectory OUTPUT

RESTORE DATABASE MyDB
FROM DISK = 'X:\SQLBackups\MyDB.bak'
WITH MOVE 'MyDB_Data' TO @DataDirectory + 'MyDB.mdf',
MOVE 'MyDB_Log' TO @LogDirectory + 'MyDB.ldf';

您需要将 MSSQL10_50.MSSQLSERVER 替换为您要执行还原的服务器实例的实例 ID。

更简单的方法是使用xp_instance_regread自动识别您正在处理的服务器实例的实例 ID。因此，上面的代码可以重写如下：

DECLARE @DataDirectory nvarchar(255)
DECLARE @LogDirectory nvarchar(255) 

EXEC master..xp_instance_regread
    @rootkey=N'HKEY_LOCAL_MACHINE',
    @key=N'Software\Microsoft\Microsoft SQL Server\MSSQLServer',
    @value=N'DefaultData', 
    @DataDirectory=@DataDirectory OUTPUT

EXEC master..xp_instance_regread
    @rootkey=N'HKEY_LOCAL_MACHINE',
    @key=N'Software\Microsoft\Microsoft SQL Server\MSSQLServer',
    @value=N'DefaultLog', 
    @LogDirectory=@LogDirectory OUTPUT

RESTORE DATABASE MyDB
FROM DISK = 'X:\SQLBackups\MyDB.bak'
WITH MOVE 'MyDB_Data' TO @DataDirectory + 'MyDB.mdf',
MOVE 'MyDB_Log' TO @LogDirectory + 'MyDB.ldf';

（注意：我在这里使用的是 SQL Server 2008 R2）

关于移动数据库，您可以将数据库从源分离，然后将物理数据和日志文件复制到目标位置，然后将它们重新附加在那里（以及重新附加到源计算机上）。我个人更喜欢备份/恢复过程，特别是当你需要 ftp 进行文件传输时。

您甚至可以设置复制、镜像或日志传送，但我认为对于您来说，这样做有点过头了。我坚持使用备份/恢复方法。

相关内容