0

We are having a database without backup to restore. (we didn’t keep backups as client not having enough space in his SQL Server 2012). Unfortunately some data has been lost and we need to recover those. Please provide a way of doing this.

Improve this question
6
  • Was it the client's choice not to keep backups, or yours?
    – Mark Sinkinson
    Commented Jan 21, 2015 at 9:22
  • 1
    It is very unlikely you will get the data back. When you lack the space for backups you purchase more space rather than not bothering with backups. What would you do if the drive(s) holding the databases just died and refused to spin up again? There might be a small chance someone can help, but you'll have to describe in much much more detail what you mean by "some data has been lost". A database missing? Database corruption? A user accidentally issued a DELETE with an incorrect filtering clause so too much was deleted?
    – David Spillett
    Commented Jan 21, 2015 at 9:23
  • 2
    You need to specify what happened. In your original question in SO you mentioned that data was deleted by a script. What backup mode are you using (full, simple)? You may be able to perform a point-in-time restore if you are using full logging. Take a database snapshot before you attempt anything so you can revert is something goes wrong
    – Panagiotis Kanavos
    Commented Jan 21, 2015 at 9:24
  • Have you ever run DBCC CHECKDB before restored the DB. Because DBCC CHECKDB is a way to REPAIR_ALLOW_DATA_LOSS option is a supported feature of SQL Server.
    – Md Haidar Ali Khan
    Commented Jan 21, 2015 at 11:18
  • 4
    Don't run DBCC REPAIR_ALLOW_DATA_LOSS unless you are fully prepared to lose data.
    – Rob Farley
    Commented Jan 21, 2015 at 11:21

2 Answers 2

Reset to default
4

If you have "lost the data" because of corruption, you may be able to retrieve some of the data from nonclustered indexes. Or if the corruption is only in nonclustered indexes you may have lost nothing and can rebuild the indexes to recover the data. Search for DBCC PAGE and you may find advice from some experts on what to do.

If you have "lost the data" because someone has deleted it and you have no backup, then that data is simply gone. It doesn't exist anywhere.

I would recommend your client make an insurance claim.

Improve this answer
0
1

By default none of these two can be reverted but there are special cases when this is possible.

Truncate: when truncate is executed SQL Server doesn’t delete data but only deallocates pages. This means that if you can still read these pages (using query or third party tool) there is a possibility to recover data. However you need to act fast before these pages are overwritten.

Delete: If database is in full recovery mode then all transactions are logged in transaction log. If you can read transaction log you can in theory figure out what were the previous values of all affected rows and then recover data.

Recovery methods:

One method is using SQL queries similar to the one posted here for truncate or using functions like fn_dblog to read transaction log.

Another one is to use third party tools such as ApexSQL Log, SQL Log Rescue, ApexSQL Recover or Quest Toad, SysTools SQL Log Analyzer

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.