取消删除最近删除的行 sql server [英] Undelete recently deleted rows sql server

问题描述

我错误地删除了一些行 b ，我可以使用日志或 SSMS 恢复它们吗

I mistakenly deleted some rows b , can I recover them back using logs or SSMS

推荐答案

如果您的数据库处于简单恢复模式，那么您可能就不走运了.您可以恢复到最近的备份，但如果是很久以前的备份，它可能不包含您删除并希望重新插入的行的副本.也很可能在此期间插入了其他数据.您可以恢复到一个新的数据库，然后进行 SQL 手术，将消失的数据重新找回.

If your database is in simple recovery mode, then you are possibly out of luck. You can restore to the most recent backup, but if it was a long time ago it may not contain copies of the rows you deleted and wish to reinsert. It is also very likely that other data has been inserted in the meantime. You could restore to a new database and then do SQL surgery to get the vanished data back in.

如果您的数据库处于完全恢复模式，则:

If your database is in full recovery mode, then:

1. 查找上次完整备份、此后的任何增量备份以及自上次增量备份或完整备份以来的所有日志备份文件，并将它们恢复到正确的时间点.如果可以接受，您可以覆盖您的数据库，或者您可以恢复到新数据库并执行 SQL 手术.

1. Find the last full backup, any incrementals since then, and all log backup files since the last incremental or full, and restore them up to the correct point in time. You could overwrite your database if that is acceptable, or you can restore to a new database and perform SQL surgery.

恢复过程如下所示:

BACKUP DATABASE YourDB TO DISK = 'D:\MSSQL\Data\YourDB\YourDB Pre-Repair.bak'
-- It is CRUCIAL you take a new backup before doing ANYTHING so you don't
-- make another mistake you can't reverse.

RESTORE DATABASE YourDB FROM DISK
   = 'D:\MSSQL\Data\YourDB\YourDB 20121208 110000.bak' WITH REPLACE, NORECOVERY;
-- be very careful with REPLACE as needing it proves there is un-backed-up data
RESTORE LOG YourDB FROM DISK
   = 'D:\MSSQL\Data\YourDB\YourDB 20121208 111500.log' WITH NORECOVERY;
RESTORE LOG YourDB FROM DISK
   = 'D:\MSSQL\Data\YourDB\YourDB 20121208 113000.log' WITH NORECOVERY;
-- restore more log files, all of them, in order, with none skipped
RESTORE LOG YourDB FROM DISK
   = 'D:\MSSQL\Data\YourDB\YourDB 20121209 020000.log'
   WITH STOPAT = '20121209 01:57:00', RECOVERY;

请注意，我在这里使用了 WITH STOPAT，它允许您将数据库恢复到特定时间点.在我的示例中，每 15 分钟进行一次日志备份，并在 2012 年 12 月 9 日凌晨 1:57:15 发出致命查询.

Note that I used WITH STOPAT here, which allows you to restore your database up to a specific point in time. In my example, log backups were taken every 15 minutes, and the fatal query was issued at 1:57:15 AM on 2012-12-09.

如果要将数据库恢复到新数据库，则必须执行以下操作:

If you want to restore a database to a new one, you have to do something like this:

RESTORE DATABASE NewDB FROM DISK
   = 'D:\MSSQL\Data\YourDB\YourDB 20121208 110000.bak'
WITH
   MOVE 'YourDBLogicalName' TO 'D:\MSSQL\Data\NewDB.mdf',
   MOVE 'YourDBLogicalName_Log' TO 'L:\MSSQL\Logs\NewDB.ldf';

使用 RESTORE FILELISTONLY 找出备份中的内容.如果同一个文件中有多个备份，则可以使用更多语法来读出该信息，然后指定要使用的备份.使用 sp_helpdb 'YourDB' 找出放置 NewDB 数据库和日志文件的位置.

Use RESTORE FILELISTONLY to figure out what's in the backup. If there are multiple backups in the same file, there's more syntax to read out that info and then specify which one you want to work with. Use sp_helpdb 'YourDB' to find out where to put your NewDB database and Log files.

如果需要，还有更多脚本可以重命名逻辑文件(我总是这样做).

Then there's more script to rename the logical files, if you want (which I always do).

当然，愚蠢的我，我现在才意识到您可以使用 SSMS GUI 来完成大部分工作.但是如果你想开始理解所有这些东西并变得非常擅长，我建议你编写脚本.我是一名开发人员，但可以恢复数据库 lickety-split，而无需寻求官方"DBA 的帮助.

Of course, silly me, I'm just realizing now that you could use the SSMS GUI to do most of this. But if you want to start understanding all this stuff and becoming really good at it, I recommend writing the script. I am a developer, but can restore databases lickety-split without having to ask for the "official" DBA's help.

这篇关于取消删除最近删除的行 sql server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！