Problem with database backup restore

Question

I am having a problem restoring a table in one of our databases.

The table suddenly one day appeared empty, all records lost, reason still unknown.

So I have tried to restore the contents of the table by using our daily database backups, however, no matter what point in time I choose to restore to, the table always restores as an empty table (all other tables in the database are restored with content without any problem).

The table is used as a sort of a logging table, hence it stores a growing number of records over time. It is normally not purged, and the 3-monthly snapshots we have taken of the database confirms that the table indeed contained a growing number of rows. (Luckily we had the quarterly snapshots, otherwise the data would have been completely lost)

So how can it be that the table is restored as a blank table?

Any ideas are appreciated!

Answer 1

You will need to first find a full backup which has data in the table. Once you have found that backup, restore it again with NORECOVERY. Then you can apply transaction logs to that backup. If think you could use the STANDBY option for the logs you apply, so you that you can peek into the the database as readonly. Keep on doing this until you find the table empty. Then you will have to start over to restore the log before the table was cleared.

Keep in mind that BACKUP and RESTORE do not work with tables. They work with pages, and do not really know what are on these tables.

(I'm a little bit out of my league here. I trust Sashank or Tibor to correct me if I say something completely wrong.)

Answer 2

Just to confirm the backup has table "with" data. Can i suggest you to restore the backup on different server let us say as DB1. Now post restore connect to database DB1 and check if the table has data. If so script out the table structure of DB1 and use this to create table on old db, if the table does not exists. Now use import export wizard to move data from DB1 to old table.

Answer 3

Hi @Mats ,

First, you need to make sure that the full database backup used for restore was created before this table/data was deleted. So as Shashank-Singh said, you need to confirm the backup has table "with" data.

It is certain that your database is running under full or bulk-logged recovery models, because you tried the Point-in-Time recovery. If the full database backup used for restore is confirmed to have tables and data, so the cause of your problem may be that you did not restore to the correct point in time.

---

Hot issues in October—Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet

Answer 4

Thanks for the two suggestions, which however did not get me closer to a solution.

Answer 5

Thanks Erland.

From the quarterly snapshots of the database (as well as some copies of the databases made for development purposes in the past) I was able to find the point in time when the first records were added to the table (the timestamp columns in the table were helpful here), and this was on 21 Feb 2020. Using the Database Restore wizard in Ms SQL Management Studio I have then identified the first full backup taken after that date (full backups are made on a weekly basis), and this was on 23 Feb 2020. At that point in time the table is known for sure to have contained a small set of records. (The data loss must have occurred sometime in October 2020.)

So I have just made another attempt to recover the backed up database to that point in time. Unfortunately the table is appearing empty also in this attempt.

I have verified that there are no triggers or permissions set on this table which might have caused the table to behave in an unexpected manner, but there is nothing obvious which makes this table any special or different from any of the other tables in the database (which all restore successfully with data as at the date specified in the recovery).

Still puzzled...