March 14, 2014 at 10:11 am
Just wanted to share my knowledge I have regarding transactional log and I am wondering if someone can answer me since I know just a little to get by.
In order for us to perform point-in-time recovery, we have to put the DB into FULL recovery mode and take transactional log every 15 minutes to an hour depending on how much data the client can afford to lose. If the DB is in full recovery mode and the transactional log is not set up then the log will grow and eventually, the drive its on will run out of space.
Scenario and question:
We take full backup every Sunday and take differential backup at midnight for the rest of the week, and transactional log every half an hour eg 12:30, 1:00, 1:30 etc...
In this case let's say the DB goes down at 1:55, how do we restore it to the point in time recovery? What's the procedure?
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
March 14, 2014 at 10:15 am
New Born DBA (3/14/2014)
Just wanted to share my knowledge I have regarding transactional log and I am wondering if someone can answer me since I know just a little to get by.In order for us to perform point-in-time recovery, we have to put the DB into FULL recovery mode and take transactional log every 15 minutes to an hour depending on how much data the client can afford to lose. If the DB is in full recovery mode and the transactional log is not set up then the log will grow and eventually, the drive its on will run out of space.
Scenario and question:
We take full backup every Sunday and take differential backup at midnight for the rest of the week, and transactional log every half an hour eg 12:30, 1:00, 1:30 etc...
In this case let's say the DB goes down at 1:55, how do we restore it to the point in time recovery? What's the procedure?
Restore the full backup, followed by the last diff then restore the logs up to the desired recovery time of 1:55
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 14, 2014 at 10:48 am
Perry Whittle (3/14/2014)
Restore the full backup, followed by the last diff then restore the logs up to the desired recovery time of 1:55
Thanks Perry
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
March 14, 2014 at 1:10 pm
New Born DBA (3/14/2014)
In this case let's say the DB goes down at 1:55, how do we restore it to the point in time recovery? What's the procedure?
Depends on the type of failure. If the DB's log is still available and SQL is still running then you can do what's called a tail log backup (BACKUP LOG... WITH NO_TRUNCATE). That should be the first thing you try.
Once you have succeeded in getting a tail log backup (or have identified that a tail log backup is not possible), then you start with the restore sequence.
Restore latest full backup WITH NORECOVERY
Restore the latest differential WITH NORECOVERY
Restore all log backups in sequence, starting with the first one which ran after the differential ending with the tail log backup which you took, all WITH NORECOVERY
Once you're sure you've restored everything needed, all log backups, run RESTORE DATABASE <database name> WITH RECOVERY
If you get a tail log backup in step 1, then your restore is to the point of failure (1:55pm). If not, then it's to the last log backup which ran before the failure (1:30pm)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 14, 2014 at 1:16 pm
Thanks for replying.
This is how I ended up setting the environment. Please let me know if its OK.
Since it's only 8 GB in size, I am taking full backup everyday and deleting the old backup which are a day old and Tlog backup every hour (No differential backup). I am also deleting old tlog files which are older than an hour so every hour I have a copy of full backup and a copy of a tlog backup. I hope its ok and I will be able to restore it if anything goes bad.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply