Geek Answers Handbook

Undelete recently deleted sql server rows

I mistakenly deleted several lines b, can I recover them using logs or SSMS

+4
source share
2 answers

If your database is in easy recovery mode, you may be out of luck. You can restore the most recent backup, but if it was a long time ago, it cannot contain copies of deleted rows and re-entry. It is also very likely that other data was added at the same time. You can restore a new database and then perform an SQL operation to return the missing data.

If your database is in full recovery mode, then:

  • Find the last full backup, any increments since then, and all the log backup files since the last increase or complete completion and return them to the right point in time. You can overwrite your database if appropriate, or you can restore a new database and perform an SQL operation.

  • The recovery 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 , which allows you to restore the database to a specific point in time. In my example, log backups were performed every 15 minutes, and the fatal request was published at 1:57:15 AM in 2012-12-09.

If you want to restore the database to a new one, you should 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';

Use RESTORE FILELISTONLY to find out what is in the backup. If there are several backups in one file, there is more syntax for reading this information, and then specify which one you want to work with. Use sp_helpdb 'YourDB' to find out where to place your NewDB database and log files.

Then another script to rename the logical files if you want (which I always do).

Of course, stupid, I just understand that you can use the SSMS GUI to do most of this. But if you want to begin to understand all these things and become really good at it, I recommend writing a script. I am a developer, but I can restore lickety-split databases without having to request the "official" help of the database administrator.

+8
source

SQL Server keeps a log for each remote record. You can request these logs using the sql server function fn_dblog.

 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' )

But this magazine is in hexadecimal format. and you need to convert this hex format to your actual data.

Below is an article to help you recover deleted records in the same way as above.

http://raresql.com/2011/10/22/how-to-recover-deleted-data-from-sql-sever/

+9
source

More articles:


All Articles