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
