主页 / dba / 问题 / 291877
Accepted
Java
Asked: 2021-05-20 10:36:03 +0800 CST

一次使用多个日志文件（共 24 个）恢复数​​据库的策略

  • 772

我想看看如果我运行多行 SQL 命令来一次恢复多个（总共 24 个）日志文件，这是否是安全有效的方法。

这些文件以bak文件格式格式化，但它们都是每小时事务日志文件。

我有这些 SQL 语句一起运行：

RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051800.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051801.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051802.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051803.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051804.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051805.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051806.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051807.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051808.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051809.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051810.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051811.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051812.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051813.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051814.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051815.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051816.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051817.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051818.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051819.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051820.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051821.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051822.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051823.bak' WITH NORECOVERY

底部是每个文件的屏幕截图及其大小：

在此处输入图像描述

因为每个文件的大小在某一时刻可能非常大（例如：“ .._ 2021051804.bak ”文件比其他文件大得多，我不确定一次运行整个 SQL 语句是否是一个好方法。

还是应该将这些整个过程分解为单独的 SQL Server 代理作业步骤？

我不确定当这些整个 SQL 语句一次执行时 SQL 的行为如何。

它是否从顶部一个接一个地运行（停止直到一个完成然后转到下一行）并且不会跳过该行的任何进程（如果一个进程比另一个进程花费更长的时间）？

restore transaction-log

1 个回答

  1. Best Answer

    你的方法是正确的。

    SQL Server 在开始下一个语句之前运行批处理中的每个语句直到完成。

    序列中的最后一次还原应指定STANDBY您是否希望生成的数据库可读，并可选择在将来应用更多日志。

    .bak不是文件格式，它是普通的 Windows 文件扩展名。SQL Server 不关心这一点。不同的人使用不同的约定。一种流行的方法是.bak用于完整备份和.trn事务日志，但这只是一种约定。

    • 3

相关问题