Seeing that the recovery model is set to simple and msdn states that the simple recovery model does not support point-in-time recovery - Does this mean that I won't be able to use my transaction log backups to restore the database in a disaster to an hour before it happened?
Even taking a transaction log backup is not supported for databases using the SIMPLE recovery model. This is a restriction of the database engine based on how this recovery model works, and the recovery features it doesn't support, as you mentioned.
A transaction log backup maintenance plan task automatically skips databases in SIMPLE recovery to avoid causing errors.
Which backup should be done first, the database backup or the transaction log backups? Articles that I'm busy reading say I should do the database backup first and then the transaction log backup else I will get maintenance plan errors, but I'm currently first backing up my transaction logs and then data databases and I'm not getting any errors.
For the reasons I mentioned above, it won't matter for databases using SIMPLE recovery, as they will be skipped by the transaction log backup task.
For databases in the other two recovery models, a full backup must exist before you start taking transaction log backups (just the first time), or you will get an error -- this is probably what the articles refer to.
Point-in-time recovery ability is normally driven by business need -- in other words, you determine how critical the data is and how much you can afford to lose, then set the appropriate recovery model to meet those needs, and finally create a backup solution.
Even though SIMPLE recovery does not support point-in-time recovery, if an hour of data loss is okay, perhaps a differential backup solution could work for you. (There are far too many variables that go into developing this kind of solution to give you a complete picture with what was provided in the question.)
@Alan Please check once by disabling the audit.
That server has an AUDIT on a database. If the audit is enabled, you
will get that error. If you disable the audit,query will run fine.
Best Answer
Welcome to DBA.SE. Sorry to hear you're having a bad time.
Handling Your Current Situation
If FULL Recovery model is enabled for this database do the following:
Stop all transactions/connections to the database. NOW !!
Think about your next steps.
Take the database OFFLINE and make a copy of the *.MDF and *.LDF files.
Bring the database back ONLINE.
Perform a Transaction Log Backup (TLOG backup) with the option
WITH ... NO_TRUNCATE .... This ensure the data isn't deleted from the TLOG. This step will only work if the database has previously been backed up with a FULL backupRestore database to point-in-time on a different database server using the initial FULL backup (before step 1.) and TLOG backup from the second step. The time should be before the accident happened.
Search for the modified data.
Extract data.
Import data into production server after confirming it is correct.
Bring production back online.
If you didn't have the database in FULL recovery model, then changing it now will have no impact. You won't be able to rescue the data, because it probably has already been committed to the database and is no longer available in the TLOG file. If no initial FULL backup has been performed, then these steps won't work.
Cleaning Up
DBA Tips
Here a couple of basic tips:
FULLrecovery model enabled and Transaction Log backup is being performed.