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.)
From http://postgresql.1045698.n5.nabble.com/DELETE-and-UPDATE-triggers-on-parent-table-of-partioned-table-not-firing-td5683717.html:
The DELETE and UPDATE triggers need to be on the child tables. An operation on a child doesn't fire the triggers of the parent.
As such, the following code must also be executed:
CREATE TRIGGER ${name}_delete BEFORE DELETE ON $name
FOR EACH ROW EXECUTE PROCEDURE audit_delete();
The audit_delete() function won't work as written in the question: setting the deleted date is not possible using OLD.deleted = current_timestamp. Instead, assuming that the table has a primary key field of id:
CREATE OR REPLACE FUNCTION audit_delete()
RETURNS trigger AS
$BODY$
BEGIN
EXECUTE
'UPDATE ' || TG_TABLE_NAME || ' SET deleted = current_timestamp WHERE id = ' || OLD.id;
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 1;
Best Answer
Unfortunately, there is no easy way to determine the DML command used without having some kind of auditing setup prior to the action.
You might be able to infer whether the action was a
DELETEor aTRUNCATEby looking at the transaction log to see if pages were deallocated or if row data was written to the log. If the trnasaction log shows pages were deallocated you can be certain that the table was truncated.If you had SQL Server trace or an Extended Events session running on the server at the time the delete occurred, and that trace/session is capturing DML statements, you'd be able to see exactly what happened.
Probably the more important consideration would be to ensure you have a Recovery Point Objective that serves the business by limiting the amount of time that can go by without a backup happening that you can use for recovery. So, with full recovery, you could be running log backups every 5 minutes, which would mean you could recover the table to a point just before the delete took place.
This blog post by Paul Randal has an excellent tutorial for inspecting a log backup.