November 4, 2008 at 4:54 pm
Hi,
Could plz clarify me how exactly the restore of database backup and log backup works with respect to mdf n ldf files.
The datafile location of Database 'SPProd_Cfg' is E:\SQLData\SPProd_Cfg.mdf
The logfile location of Database 'SPProd_Cfg' is F:\SQLog\SPProd_Cfg.ldf
Backup location: 'z:\Backup\SPProd_Cfg.bak'and 'z:\Backup\SPProd_Cfg.trn'
Backup plan:
full 1am
diff 8am and 4pm
log every hour starting from 1am
Scenario:
one table from 'SPProd_Cfg' deleted at 10:30am
Well, when I restore full backup(z:\Backup\SPProd_Cfg.bak at 1am), the mdf file of SPProd_Cfg.mdf will be replaced with the newly restored .bak file and creates the new mdf file?
and when I restore diff backup(z:\Backup\SPProd_Cfg.bak 8am), the mdf file of SPProd_Cfg.mdf will be replaced with the newly restored .bak file and creates the new mdf file again?
when I restore log backup(z:\Backup\SPProd_Cfg.trn at 9am), the ldf file of SPProd_Cfg.ldf will be replaced with the newly restored .trn file and creates the new ldf file?
when I restore log backup(z:\Backup\SPProd_Cfg.trn at 10am), the ldf file of SPProd_Cfg.ldf(created at 9am by restoring the log backup at 9am) will be replaced with the newly restored .trn file and creates the new ldf file? or appends to the previous ldf file?
How actually it works?
and if the database crashed at 10:59am, what is the way to recover the data till 10:59?
If Iam talking hourly loog backups that mean we must lose 1 hr data if a disaster occurs?
Thanks in Advance
November 4, 2008 at 5:41 pm
Mani (11/4/2008)
Well, when I restore full backup(z:\Backup\SPProd_Cfg.bak at 1am), the mdf file of SPProd_Cfg.mdf will be replaced with the newly restored .bak file and creates the new mdf file?
Yes, because you're restoring the DB from scratch and replacing the existing one
and when I restore diff backup(z:\Backup\SPProd_Cfg.bak 8am), the mdf file of SPProd_Cfg.mdf will be replaced with the newly restored .bak file and creates the new mdf file again?
No. Diff contains changes and those changes will be applied directly to the appropriate data pages
when I restore log backup(z:\Backup\SPProd_Cfg.trn at 9am), the ldf file of SPProd_Cfg.ldf will be replaced with the newly restored .trn file and creates the new ldf file?
No. The log records contained in the backup will be replayed against the database
when I restore log backup(z:\Backup\SPProd_Cfg.trn at 10am), the ldf file of SPProd_Cfg.ldf(created at 9am by restoring the log backup at 9am) will be replaced with the newly restored .trn file and creates the new ldf file? or appends to the previous ldf file?
Neither. The log records contained in the backup will be replayed against the database
and if the database crashed at 10:59am, what is the way to recover the data till 10:59?
Depends. If you can backup the tail of the log, then yes. If the ldf file is destroyed in the disaster, then no
Backup log <Database name> with no_truncate -- log tail backup. Works even if the DB itself is damaged.
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
November 4, 2008 at 11:30 pm
Thank you veru much Gail..
You clarified me many things..I have still some questions..
As you said that The log records contained in the backup will be replayed against the database.Here I did not understand 'replayed'against the database?
niether replace nor append is fine but what is replayed?
And regarding log backups, if we schedule log back every 15 min, means we are ready to lose 15 min data if a disaster occures is that right?
are there is any chance to recover that 15 mins data too?
as you said we can use tail log backup to get the data to point of failure but it is not always possible. why it is not possible always?
is there any limitations to take the taillog backup?
Thanks again
November 5, 2008 at 12:10 am
Mani (11/4/2008)
As you said that The log records contained in the backup will be replayed against the database.Here I did not understand 'replayed'against the database?niether replace nor append is fine but what is replayed?
The log backups contain information about what data changes occurred in the database. When the log file is restored, SQL goes through those log records and redoes those changes. If you imaging the log as a recording of all changes then when the log backup is restored, that recording is played, much like recording a movie and then playing it back later. (maybe a poor analogy, but it suffices)
as you said we can use tail log backup to get the data to point of failure but it is not always possible. why it is not possible always?
Well, if the ldf file is no longer around (because of catastrophic server destruction, or the failure of the log drive), then it would be kinda difficult to back it up.
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
November 5, 2008 at 12:26 am
Thank You..
I understand like this....
So, if log backups run every 1hr and database crashed at 10:59am. That means whatever the activity happened between 10:00am to 10:59am will exist in ldf file.If We have the good ldf,we can take the tail log backup at 10:59am and restore the 10:00am log backup and then 10:59am tail log, then everything is ok..
if either of the ldf n mdf file is corrupted we have no chance to restore data point in time..is that correct?
November 5, 2008 at 1:02 am
Mani (11/5/2008)
So, if log backups run every 1hr and database crashed at 10:59am. That means whatever the activity happened between 10:00am to 10:59am will exist in ldf file.If We have the good ldf,we can take the tail log backup at 10:59am and restore the 10:00am log backup and then 10:59am tail log, then everything is ok..
Yup.
if either of the ldf n mdf file is corrupted we have no chance to restore data point in time..is that correct?
Almost. With a log tail backup, the mdf can be damaged or completely missing and it will still be possible to do the backup.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply