HOW TO Recover SQL information from a born table while not backups

If you’ve accidentally dead a DROP statement and you have got a full information backup, on condition that no changes occurred when the table was born, you’ll be able to merely recover the information by overwriting the first information with the backup. However, if that’s not the case, either as a result of the information objects and information are modified when the DROP SQL statement was dead or as a result of you don’t have a legitimate information backup, there’s still the simplest way to recover all of your lost information

You’ll be needing some tools for the process which can be found at

Namely, every DROP operation is logged to the SQL Server dealings log. this implies that the dealings log stores the data needed to roll back the dealings and, on condition that the information is fully recovery mode, supports point-in-time recovery from a DROP operation

Since DROP is logged to the dealings log, it are often rolled back if it’s encapsulated inside a certain, active dealings. for example, the subsequent SQL query:



FROM Customer

Will end in the monosodium glutamate 208, Level 16, State one error message because the client table has been born.

However, the subsequent SQL code:

FROM Customer

Will come back all of the rows within the client table because the DROP operation are going to be rolled back

However, if the dealings containing the DROP operation isn’t any longer active, for example since it’s been committed, the born table can not be rolled back, and conversely the information it hold on are going to be lost in addition. will that mean that you just can’t recover the information lost because of a DROP operation if no full information backups area unit available?

Fortunately, no. this is often wherever ApexSQL Recover comes into play

ApexSQL Recover may be a recovery tool for SQL Server databases that recovers deleted, born or truncated information. It recovers objects and information lost because of drop operations and restores each deleted and on-line BLOBs as files creating it ideal for SharePoint recoveries

To recover information lost because of a DROP operation while not backups:

  1. Start ApexSQL Recover

Select the Recover lost information because of a DROP table operation choice within the main ribbon

  1. In the choose the information to recover step:
  2. Specify the SQL instance wherever {the information the info the information}base you would like to recover the data to resides from the
  3. Server drop-down box
  • Specify the authentication technique for that SQL instance (and a legitimate set of credentials if you selected SQL Server authentication)
  • Specify the name of the information that contained the born tables within the information drop-down box.

and click Next In the area unit extra information sources available? step, choose the No extra dealings logs area unit on the market choice

5. In the choose a recovery action step, choose the Save recovery script to file choice and specify the trail wherever a T-SQL script that may insert all the lost information once dead are going to be saved

In the final step of the recovery wizard, select whether or not to recover solely information, solely table structure, or each table structure and information

  1. Click end to end the method

And similar to that, you’ll get your lost information back while not having one SQL information backup. ApexSQL Recover can show the results of the eminent recovery and permit examination or maybe changes to be created on the recovery script that solely has to be dead from inside the tool itself or SQL Server Management Studio (or similar tool) to complete the recovery.

Looking for more regarding SQL server database recoveries? Check these :

Leave a Reply

Your email address will not be published. Required fields are marked *

Enter Captcha Here :