无需事务日志即可恢复 SQL Server 数据库
给定一个SQL Server 2008.bak文件，有没有办法从.bak恢复数据文件 > 文件，没有事务日志？
我问的原因是该数据库的事务日志文件大小很大 - 超出了我可用的磁盘空间。
我对交易日志不感兴趣，对任何未完成的交易也不感兴趣。通常，一旦恢复数据库，我就会将日志缩小到零。但是，当我没有足够的磁盘空间来创建日志时，这并没有帮助。
我需要的是一种方法来告诉 SQL Server 仅恢复 .bak 文件中的数据，而不是事务日志。有什么办法可以做到这一点吗？
请注意，我无法控制 .bak 文件的生成 - 它来自外部源。在生成 .bak 文件之前缩小事务日志是不的选择。
Given a SQL Server 2008 .bak file, is there a way to restore the data file only from the .bak file, without the transaction log?
The reason I'm asking is that the transaction log file size of this database is huge - exceeding the disc space I have readily available.
I have no interest in the transaction log, and no interest in any uncompleted transactions. Normally I would simply shrink the log to zero, once I've restored the database. But that doesn't help when I have insufficient disc space to create the log in the first place.
What I need is a way to tell SQL Server to restore only the data from the .bak file, not the transaction log. Is there any way to do that?
Note that I have no control over the generation of the .bak file - it's from an external source. Shrinking the transaction log before generating the .bak file is not an option.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
这可能不起作用，因为您无法控制 .bak 文件的生成，但如果您可以说服源分离数据库，然后直接向您发送 .mdf 文件的副本，则您可以附加 .mdf 并您的服务器将自动创建一个新的空事务日志文件。
请参阅 sp_detach_db 和 sp_attach_db（或 CREATE DATABASE database_name FOR ATTACH，具体取决于您的 sql server 版本）。
This may not work since you have no control over the generation of the .bak file, but if you could convince your source to detach the database and then send you a copy of the .mdf file directly, you could then attach the .mdf and your server would automatically create a new empty transaction log file.
See sp_detach_db and sp_attach_db (or CREATE DATABASE database_name FOR ATTACH depending on your sql server version).
我知道这是一个旧线程，但我在遇到事务日志损坏问题时偶然发现了它，以下是我在没有任何数据丢失的情况下解决它的方法（不过我确实有停机时间！）
这就是我所做的：- -
停止sql server实例服务
复制受影响的数据库 .mdf 文件和 .ldf 文件（如果您有 .ndf 文件，也复制它！） - 可以肯定的是，如果它不适合您，您可以随时将它们放回去。
重新启动服务。
登录sql management studio，将数据库模式更改为简单，然后进行完整备份。
再次更改数据库类型并再次进行完整备份，然后进行事务日志备份。
分离数据库。
右键单击数据库，然后单击还原，从下拉列表中选择数据库名称，选择稍后创建的完整数据库备份（不是从简单模式中获取的备份），并选择事务日志备份。
单击恢复，它应该将其全部恢复，并且日志文件中没有任何损坏。
这对我来说没有错误，而且我的备份之后都工作正常，并且没有更多的事务日志错误。
I know this is an old thread now, but i stumbled across it while I was having transactional log corruption issues, here is how I got around it without any data loss (I did have down time though!)
Here is what I did:--
Stop the sql server instance service
make a copy of the affected database .mdf file and .ldf file (if you have an .ndf file, copy that as well!) - Just to be sure, you can always put these back if it doesn't work for you.
restart the service.
Log into sql management studio and change the database mode to simple, then take a full backup.
Change the database type back again and once again take a full backup, then take a transactional log backup.
Detach the database.
Right click on databases and click on restore, select the database name from the drop down list, select the later full database backup created (not the one taken from the simple mode) and also select the transactional log backup.
Click restore and it should put it all back without any corruption in the log files.
This worked for me with no errors and my backups all worked correctly afterwards and there were no more transactional log errors.
事务日志是备份的一个组成部分。你不能告诉SQL Server忽略事务日志，因为没有办法同时恢复和收缩事务日志文件。但是，您可以查看 DBA 帖子 来破解该过程，尽管根本不建议
您这样做可以尝试一些第三方工具进行恢复，特别是虚拟恢复过程，可以节省大量空间和时间。查看 ApexSQL 还原、RedGate 虚拟还原、Idera 虚拟数据库。
免责声明：我在 ApexSQL 担任支持工程师
The transaction log is an integral part of the backup. You can't tell SQL Server to ignore the transaction log, because there is no way to let's say restore and shrink the transaction log file at the same time. However, you can take a look at DBA post to hack the process, although it is not recommended at all
Alternatively you could try some third party tools for restoring, particularly virtual restoring process that can save a lot of space and time. Check out ApexSQL Restore, RedGate Virtual Restore, Idera Virtual Database.
Disclaimer: I work for ApexSQL as support engineer
不，需要事务日志。
选项 1：
一个选项可能是将其恢复到您确实有足够空间的计算机上。然后在恢复的副本上将日志记录更改为批量记录或简单，缩小日志，在此新副本上执行另一个备份操作，然后使用它来恢复到具有现在小得多的事务日志的目标计算机。
选项 2：
或者，外部源的联系人可能会在将事务日志发送给您之前缩小它（如果日志由于大量大事务而很大，则这可能不起作用）。
有关收缩日志文件的命令的文档可以在此处获取。
No, the transaction log is required.
Option 1:
An option may be to restore it to a machine that you DO have enough space on. Then on the restored copy change the logging to either bulk logged or simple, shrink the logs, do another backup operation on this new copy and then use that to restore to the target machine with the now much smaller transaction log.
Option 2:
Alternatively, perhaps the contact at the external source could shrink the transaction log before sending it to you (this may not work if the log is large due to a lot of big transactions).
Docs on the command to shrink the log file are available here.
对于 ServerFault 或 DBA 站点来说，这确实是一个问题，但简短的答案是否定的，您只能恢复完整的 .bak 文件（不考虑文件组或分段恢复等“异国情调”场景）。你没有说“巨大”是什么意思，但磁盘空间很便宜；如果添加更多确实不是一个选择，那么您需要找到一种从外部源获取数据的替代方法。
This is really a question for the ServerFault or DBA sites, but the short answer is no, you can only restore the full .bak file (leaving aside 'exotic' scenarios such as filegroup or piecemeal restores). You don't say what "huge" means, but disk space is cheap; if adding more really isn't an option then you need to find an alternative way of getting the data from your external source.