SQL SERVER – How to Recover SQL Database Data Deleted by Accident
- by Pinal Dave
In Repair a SQL Server database using a transaction log explorer, I showed how to use ApexSQL Log, a SQL Server transaction log viewer, to recover a SQL Server database after a disaster. In this blog, I’ll show you how to use another SQL Server disaster recovery tool from ApexSQL in a situation when data is accidentally deleted. You can download ApexSQL Recover here, install, and play along.
With a good SQL Server disaster recovery strategy, data recovery is not a problem. You have a reliable full database backup with valid data, a full database backup and subsequent differential database backups, or a full database backup and a chain of transaction log backups. But not all situations are ideal. Here we’ll address some sub-optimal scenarios, where you can still successfully recover data.
If you have only a full database backup
This is the least optimal SQL Server disaster recovery strategy, as it doesn’t ensure minimal data loss.
For example, data was deleted on Wednesday. Your last full database backup was created on Sunday, three days before the records were deleted. By using the full database backup created on Sunday, you will be able to recover SQL database records that existed in the table on Sunday. If there were any records inserted into the table on Monday or Tuesday, they will be lost forever. The same goes for records modified in this period. This method will not bring back modified records, only the old records that existed on Sunday.
If you restore this full database backup, all your changes (intentional and accidental) will be lost and the database will be reverted to the state it had on Sunday. What you have to do is compare the records that were in the table on Sunday to the records on Wednesday, create a synchronization script, and execute it against the Wednesday database.
If you have a full database backup followed by differential database backups
Let’s say the situation is the same as in the example above, only you create a differential database backup every night.
Use the full database backup created on Sunday, and the last differential database backup (created on Tuesday). In this scenario, you will lose only the data inserted and updated after the differential backup created on Tuesday.
If you have a full database backup and a chain of transaction log backups
This is the SQL Server disaster recovery strategy that provides minimal data loss. With a full chain of transaction logs, you can recover the SQL database to an exact point in time.
To provide optimal results, you have to know exactly when the records were deleted, because restoring to a later point will not bring back the records.
This method requires restoring the full database backup first. If you have any differential log backup created after the last full database backup, restore the most recent one. Then, restore transaction log backups, one by one, it the order they were created starting with the first created after the restored differential database backup.
Now, the table will be in the state before the records were deleted. You have to identify the deleted records, script them and run the script against the original database.
Although this method is reliable, it is time-consuming and requires a lot of space on disk.
How to easily recover deleted records?
The following solution enables you to recover SQL database records even if you have no full or differential database backups and no transaction log backups.
To understand how ApexSQL Recover works, I’ll explain what happens when table data is deleted.
Table data is stored in data pages. When you delete table records, they are not immediately deleted from the data pages, but marked to be overwritten by new records. Such records are not shown as existing anymore, but ApexSQL Recover can read them and create undo script for them.
How long will deleted records stay in the MDF file? It depends on many factors, as time passes it’s less likely that the records will not be overwritten. The more transactions occur after the deletion, the more chances the records will be overwritten and permanently lost.
Therefore, it’s recommended to create a copy of the database MDF and LDF files immediately (if you cannot take your database offline until the issue is solved) and run ApexSQL Recover on them. Note that a full database backup will not help here, as the records marked for overwriting are not included in the backup.
First, I’ll delete some records from the Person.EmailAddress table in the AdventureWorks database.
I can delete these records in SQL Server Management Studio, or execute a script such as
DELETE FROM Person.EmailAddress
WHERE BusinessEntityID BETWEEN 70 AND 80
Then, I’ll start ApexSQL Recover and select From DELETE operation in the Recovery tab.
In the Select the database to recover step, first select the SQL Server instance. If it’s not shown in the drop-down list, click the Server icon right to the Server drop-down list and browse for the SQL Server instance, or type the instance name manually.
Specify the authentication type and select the database in the Database drop-down list.
In the next step, you’re prompted to add additional data sources. As this can be a tricky step, especially for new users, ApexSQL Recover offers help via the Help me decide option.
The Help me decide option guides you through a series of questions about the database transaction log and advises what files to add.
If you know that you have no transaction log backups or detached transaction logs, or the online transaction log file has been truncated after the data was deleted, select No additional transaction logs are available.
If you know that you have transaction log backups that contain the delete transactions you want to recover, click Add transaction logs. The online transaction log is listed and selected automatically.
Click Add if to add transaction log backups. It would be best if you have a full transaction log chain, as explained above.
The next step for this option is to specify the time range.
Selecting a small time range for the time of deletion will create the recovery script just for the accidentally deleted records. A wide time range might script the records deleted on purpose, and you don’t want that. If needed, you can check the script generated and manually remove such records.
After that, for all data sources options, the next step is to select the tables. Be careful here, if you deleted some data from other tables on purpose, and don’t want to recover them, don’t select all tables, as ApexSQL Recover will create the INSERT script for them too.
The next step offers two options: to create a recovery script that will insert the deleted records back into the Person.EmailAddress table, or to create a new database, create the Person.EmailAddress table in it, and insert the deleted records. I’ll select the first one.
The recovery process is completed and 11 records are found and scripted, as expected.
To see the script, click View script. ApexSQL Recover has its own script editor, where you can review, modify, and execute the recovery script.
The insert into statements look like:
INSERT INTO Person.EmailAddress( BusinessEntityID,
EmailAddressID,
EmailAddress,
rowguid,
ModifiedDate)
VALUES( 70,
70,
N'[email protected]' COLLATE SQL_Latin1_General_CP1_CI_AS,
'd62c5b4e-c91f-403f-b630-7b7e0fda70ce',
'20030109 00:00:00.000' );
To execute the script, click Execute in the menu.
If you want to check whether the records are really back, execute
SELECT * FROM Person.EmailAddress
WHERE BusinessEntityID BETWEEN 70 AND 80
As shown, ApexSQL Recover recovers SQL database data after accidental deletes even without the database backup that contains the deleted data and relevant transaction log backups. ApexSQL Recover reads the deleted data from the database data file, so this method can be used even for databases in the Simple recovery model.
Besides recovering SQL database records from a DELETE statement, ApexSQL Recover can help when the records are lost due to a DROP TABLE, or TRUNCATE statement, as well as repair a corrupted MDF file that cannot be attached to as SQL Server instance.
You can find more information about how to recover SQL database lost data and repair a SQL Server database on ApexSQL Solution center. There are solutions for various situations when data needs to be recovered.
Reference: Pinal Dave (http://blog.sqlauthority.com)Filed under: PostADay, SQL, SQL Authority, SQL Backup and Restore, SQL Query, SQL Server, SQL Tips and Tricks, T SQL
