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://docs.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.
Best Answer
As long as your recovery model is
Simpleand 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
Simplerecovery are discarded whenSQL Serverdoes not need them for crash recovery.The answer to your second question - yes, you have to switch to
Fullrecovery. Then with the help ofApexsoftware (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 toFullrecovery - it has gotchas. Make sure to learn this Q/A.