validate complete backup chain full--diff--log---log---log etc. etc.
-
I’m looking for a way to check if it’s okay to make a differential backup or a log backup.
(For example it’s not okay to make them when there is no valid full backup present)
For instance let’s say I have the following already done:
02:00 full
09:00 log
10:00 log
11:00 log
12:00 diff
13:00 log
14:00 log
Now at 14:15 somebody by accident changes the recovery mode of this database from full to simple and back to full again.
What happens is, my script will create a log backup at 15:00, SQL will give a informational warning saying that the log is useless since no full backup is there to apply it to. But the log file will still be created.. we all think everything is fine, because log files 15:00, 16:00 17:00 are there in the directory, and then at 17:15 bang the server dies and only THEN we find out that we can only recover till 14:00.
Now without going into the discussion why the hell somebody was capable of changing te recovery mode on a production database, lets just assume it was me, changing the wrong database
---QUESTION------
How can a check to see if a created log backup is useless or not. I know in a log chain I can check msdb..backupset and see that the first_lsn of the last log backup should be equal to the last_lsn of the previous log backup. But the tricky part is linking the full backup to the first log backup created after this full backup.
---END--QUESTION----
The same applies to differential backups.
What I want to do is, create a script that loops true the whole chain to check if the entire chain is valid.
Edward Dortland
-
My script won't let you do a log backup if the mode of the db is in simple mode. Other than restoring the db and running through the logs I don't know how you would verify the backup.
Wes
-
Edward,
Make a disaster recovery test and observe time to restore database.
How much time you have to restore database?
If time to restore is good for you keep that, if not, change backup.
size database = time to backup
time to restore
Vinicius B Cobra Vivas
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply