Lost data, how to recover (sql server)

Question

We currently have a db that the backup has been set to Simple that is being backed up every night (we have backups for last 7 days), is it possible to find records that were deleted in say June?

The db is fully functional so no corrupting or anything, we just want to be able to see if we can some how find deleted records in the current log file. I have used apexsql log but that shows there is nothing, assuming because when a backup is done in simple mode, it pretty much discards the log.

Another question is, say i want to be able to have the ability to get deleted records in the future, should i be setting my backup to "full" and is that good enough or do i need also do bulk log backups?

I guess is there a simple way that its explained on what mode means what and what type of recover I get. The ms article https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server?view=sql-server-2017 talks about this but It was not clear to me what needs to happen in diff scenarios when one needs to get specific deleted/changed records.

Answer 1

As long as your recovery model is Simple and full backups for only last 7 days you do not have any chance to see what was deleted in June.

Entries in the log file for Simple recovery are discarded when SQL Server does not need them for crash recovery.

The answer to your second question - yes, you have to switch to Full recovery. Then with the help of Apex software (or some other) you probably will be able to restore deleted in the past records if you have log backups for that period of time. Be careful when switching to Full recovery - it has gotchas. Make sure to learn this Q/A.

Answer 2

We currently have a db that the backup has been set to Simple that is being backed up every night (we have backups for last 7 days), is it possible to find records that were deleted in say June?

If you have backups for 7 days, then I'm afraid that's the amount of time you'll be able to go back. It is not possible to recover data, when you have no backups of it. Unless you scripted some kind of logging, or you have a backup stored somewhere, you're out of luck.

Another question is, say i want to be able to have the ability to get deleted records in the future, should i be setting my backup to "full" and is that good enough or do i need also do bulk log backups?

It depends on how long back in time, and how specific you want to fetch those records. If you're ok with only having 1 point per day for data retrieval, then you can stick with SIMPLE recovery mode, however if you want more specific point in time, then you'll have to switch to FULL recovery mode and use LOG backups frequently during the day. (example every 10-15 minutes). Just remember, if you want to restore data from x months ago, you need the FULL/DIFF backup + the log backups to get back to that specific point in time.

Your log backups backup your transactions between the last full/diff backup and make you able to restore to a specific point in time. If you have backup each day, you lose a bit less than 24 hours of data in the worst case. If you have log backups every 10 minutes, the worst case is that you lose a little bit less than 10 minutes of data.

Your backup strategy will be decided by the amount of data you can lose, and what your business requirements are. You should read up on RPO and RTO and those will be important numbers when deciding your backup strategy.

Answer 3

It sounds like "i want to be able to have the ability to get deleted records in the future" is a requirement....

In which case you might want to look at "soft" deleting data (setting a "hide/deleted" bit flag) or implementing temporal tables or change data capture, rather than hard deleting and keeping backups around

(wont help your immediate issue, but might be worth considering in the long run)