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
