dcsimg
Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed


Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Database User and Programming Tips

Posted July 2, 2018

WEBINAR:
On-Demand

The California Consumer Privacy Act: What You Need to Know


Placing a Mark in SQL Server's Transaction Log for Fall Back

By Greg Larsen

How many times have you had a programmer come to you and say they accidentally ran an adhoc UPDATE, INSERT, or DELETE statement against their database, and now they want you (the DBA) to restore their database to sometime prior to when they accidentally corrupted their DB?  If you have been a DBA for very long you probably have gotten this kind of request more than once.  If you are doing FULL transaction logging you can do a point in time recovery to restore the database to just prior to when the corruption occurred.  But in order to do that you need to know exactly when the programmer corrupted the data.  Which in a lot of cases is not known down to the second.

When the programmer only generally knows when they corrupted the database the DBA might have to go through a number of restore operations in order to find the exact time when the database was corrupted.  Having to restore the database over and over again until you find the right time to stop the restore can be a trial and error process.  There is a better way!

I suggest that you train programmers to create a marked transaction prior to running any adhoc update process.  By doing this all you need to do is know the name of the transaction in order to recover their database prior to when the programmer ran the adhoc process that corrupted the database. 

It is quite simple to create a marked transaction.  To do that all the programmer needs to do is to wrap their adhoc code inside a named transaction, like below:

BEGIN TRANSACTION Start_Adhoc_Update
    WITH MARK 'Running my Adhoc update process';
<insert adhoc code here
COMMIT TRANSACTION;

Here I have created a marked transaction named “Start_Adhoc_Update”, with a description of “Running my Adhoc update process”.  If I find out later that I corrupted the database I can then run a RESTORE LOG statement where I use the  STOPBEFOREMARK option to restore my database to just prior to the “Start_Adhoc_Update” mark in the transaction log.     

See all articles by Greg Larsen



Database User and Programming Tips Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.