Charles Kincaid (11/9/2009)
My mileage varies here. For me unplanned server restarts are the big one. Having a customer call to say that the cleaning person unplugged the SQL server box to use the vacuum, again, and there was no data loss is kind of a big thing. The point-in-time thing can come close if you restore you last full and then the differentials up to the point in time. How frequent are your backups? Is a full every day and logs every 15 minutes enough? Is one per minute way too much?
Here is a question for someone who has the time: Can I run a query that shows if there is any un-backed-up changes in my database?
Ok, now don't make fun of this query please...I made this all up as I just went along trying to see if I could come up with something along the lines of what you are wondering if is even possible, Charles...I'm sure a TSQL whiz on this site can really clean this up and make much more efficient...with that said, try this out...
SELECT * FROM #LogInfo
WHERE ([CURRENT LSN]) > (SELECT [Current LSN]
FROM #LogInfo
WHERE [Checkpoint Begin] = (SELECT TOP(1) [Checkpoint Begin]
FROM #LogInfo
WHERE [Checkpoint Begin] IS NOT NULL
ORDER BY [Checkpoint Begin] DESC))
Basically what this is doing is copying the LOG file contents into a temporary table (#LogInfo). Then it will find the last "Checkpoint Begin" date and use that LSN against the table and provide you with all the LSNs that are after that LSN.
The concept here is that
A) if you get back 10 or less rows then your pretty much have a completely backed up database/log (# of rows will change depending on type of backup returned, also these few "default" rows appear to be header type rows for the next portion of the log), and
B) if you've done backups of the database (FULL or DIFF) then the log creates a new "Checkpoint Begin" entry...so you are only concerned with the latest data from after that LSN.
Now, I did all of this on a database using FULL backup...so, could not be same for SIMPLE. Also, I did not test this out very well, so I'm sure there are some bugs with the logic and/or TSQL. But, it should get the ball rolling...to a script that would give details of what you are wondering.
Hope this helps!
James
P.S...I created this on SQL 2008; should work just fine on SQL 2005 as I don't see anything that isn't 2005 compatible...SQL 2000 users probably need find another method (I'm not sure but I think there was a table called "syslog" that contained this data in SQL2000...anyone confirm?)
[EDIT] Also, this script needs to be ran from within the database the log contents being inspected are. In case it needs to be said, and to CMA here, this script is untested and should only be ran from within development environments. The speed of the script is dependant on the size of the log file; in otherwords if your log file is Gigabytes in size it will take a long time to run! [/EDIT]
~ Without obstacles, you cannot progress ~
http://sqln.blogspot.com/