Summary for Accidental DBA Book / Chapter 9: Truncated Tables, Dropped Objects and Other Accidents Waiting to Happen

Chapter 9: Truncated Tables, Dropped Objects and Other Accidents Waiting to Happen

• Marked transactions can be used to create a known recovery point for significant changes to a database, or multiple databases when the same transaction mark is used in multiple databases, to establish a common recovery point for all of the affected databases. DBA can use them to create an easy recovery point, prior to deploying a large set of changes to a database, in cases where the potential for problems exists … P311-318.
• Point-in-time recovery can be used to apply the transaction logs up to a known point in time before the data loss occurred. If the exact time when the data loss occurred is unknown, one option is to restore a backup of the database in STANDBY mode. This allows further log backups to be restored but, unlike when using NORECOVERY, the database is still readable …P319-320.
• If the database is in SIMPLE recovery, there is no real hope of recovering with zero data loss, since the only recovery point is the latest full or differential backup. The same happens if the database is in FULL recovery but there has never been a full database backup, it won't be possible to recover the lost data. When a database is changed from the SIMPLE recovery model to the FULL recovery model, the transaction log continues to be truncated at CHECKPOINT as it would under SIMPLE recovery until a full backup of the database is taken, which restarts the log chain. If the database is in FULL recovery, and it has had a full backup since the database was switched to FULL recovery, but no log backups (and no one ever took a log backup and deleted it), then you can take a log backup and proceed as the process of restoring to a point-in-time …P321.
• Log recovery tools … P322.
• Default trace is active by default. It captures a number of important trace events that can be used to identify changes made to the database schema, and who made them. However, the default trace does not contain any information about data modification statements (INSERT, UPDATE, DELETE). Details of the default trace, including rollover characteristics, the file to which the trace is writing, and so on, can be found through the sys.traces dynamic management view (Listing 9.15). The full list of events that the default trace collects can be found by running the query in Listing 9.16. The contents of a trace file can be read using the sys.fn_trace_gettable system function (Listing 9.17) … P324-325.
• DML triggers can be created on a table or view and execute code in response to any data manipulation language event (INSERT, UPDATE, or DELETE) on the parent object. DML triggers can be used to provide audit tracking of all changes in a table, by writing information about the changes to a secondary table … P332-P336.
• DDL triggers can be used to log database changes as well as prevent the changes from occurring at all. Unlike DML triggers, DDL triggers can be scoped to a specific database, or at the server level, and they can be configured to fire in response to a much larger set of events. DDL events are grouped into a hierarchy to allow a trigger to fire for multiple events while simplifying the trigger's definition … P337.

Reference: http://www.amazon.com/Troubleshooting-SQL-Server-Guide-Accidental/dp/1906434786