我不小心删除了一些行 b ,我能够使用日志或 SSMS 恢复它们吗?
我不小心删除了一些行 b ,我能够使用日志或 SSMS 恢复它们吗?
SQL Server会为每个已删除的记录保存日志。 您可以通过fn_dblog SQL Server函数查询这些日志。
Select [RowLog Contents 0]
FROM sys.fn_dblog(NULL, NULL)
WHERE AllocUnitName = 'dbo.TableName'
AND Context IN ( 'LCX_MARK_AS_GHOST', 'LCX_HEAP' )
AND Operation in ( 'LOP_DELETE_ROWS' )
但是这个日志是十六进制格式的。你需要将这个十六进制格式转换为实际数据。
下面的文章将帮助你以与上述相同的方式恢复已删除的记录。
http://raresql.com/2011/10/22/how-to-recover-deleted-data-from-sql-sever/
如果您的数据库处于简单恢复模式,则可能无法找回数据。您可以恢复到最近的备份,但如果备份时间较早,则可能不包含您删除并希望重新插入的行的副本。同时,很可能已经插入了其他数据。您可以恢复到新的数据库,然后进行SQL手术以恢复消失的数据。
如果您的数据库处于完全恢复模式,则:
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.
The restoration process will look something like this:
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;
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.
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 数据库和日志文件应该放置的位置。