In SQL Server 2005, if you have a database with no backup - but it is running in full recovery mode - is it possible to "roll back" some deletes?
If yes - how?
A:
I would say no, even if a database is in full recovery mode, until the first full backup is taken it is actually running in a pseudo full mode and not the true full recovery mode.
This causes you a problem since without the first backup to replay the logs upon, you will not be able to replay the log.
Andrew
2009-10-22 19:28:12
+3
A:
No. The recover process always starts from a full database backup and then it applies the log backups.
If your database has no backups then it is not in full recovery mode. Untill you actually take a backup it will always run in simple, no matter the intented recovery mode (aka 'pseudo simple', see New script: is that database REALLY in the FULL recovery mode?)
If your database really is in FULL recovery mode, then a backup was tacken and the recovery can start with that.
There are third party tools that can analyze the log and recover stuff, but they work only if the log was not recycled, so is back to the discussion whether the recovery model is really full or simple.
Remus Rusanu
2009-10-22 19:32:00
+1
A:
Not with standard tools. You'd normally restore.
Some 3rd party log analyzer might be able to help to pick out what you want, but you've lost your data most likely.
gbn
2009-10-22 19:35:08
A:
For future reference: ApexSQL was actually able to recover the deleted data from the log. Pretty amazing.
Kjensen
2009-10-26 19:32:21