March 28, 2013 at 9:22 am
I think I'm confused ...
I set DB to Full Recovery Model. I have a DB backup once a day and log backups every 30 minutes using the following:
Backup database xyz to disk = blah blah directory with init, nounload, name=xyz, no skip, stats = 10, noformat
Backup log xyz to disk = blah blah directory with init, nounload, name=xyz, no skip, stats = 10, noformat
Am I correct to say the log backups are incremental? Since the log files are truncated every 30 minutes and it is my understanding that in the event of recovery, I will need to restore every single log files since the last full backup. Also, I was able to shrink log file size after backups. Does that not meant the log backups are incremental?
If so, does that mean SQL Server has incremental backups in FULL recovery model?
March 28, 2013 at 9:39 am
MSSQL_NOOB (3/28/2013)
I think I'm confused ...I set DB to Full Recovery Model. I have a DB backup once a day and log backups every 30 minutes or so using the following:
Backup database xyz to disk = blah blah directory with init, nounload, name=xyz, no skip, stats = 10, noformat
Backup log xyz to disk = blah blah directory with init, nounload, name=xyz, no skip, stats = 10, noformat
I hope you are not initialising the file every time you write to it? If you are you wont be able to restore as you need the whole collection of log backups from the last full/diff backup.
Am I correct to say the log backups are incremental? Since the log files are truncated every 30 minutes and it is my understanding that in the event of recovery, I will need to restore every single log files since the last full backup. Also, I was able to shrink log file size after backups. Does that not meant the log backups are incremental?
If so, does that mean SQL Server has incremental backups in FULL recovery model?
Are the log files incremental? Typically to most people an incremental backup will take the snapshot/delta of the file. If you update the same row 1 million times then the incremental backup will only have the last value, whereas the log is the history of the file so will have all 1 million updates. You will need to have all the logs fbackups from the last full/diff backup for a point in time restore.
Its rare to have to shrink the log file afterwards. Just means that it will waste time and freeze transactions while its trying to regrow the log.
I suggest that you take a copy of the backup files and try a restore to a couple of random points in tiime.
March 28, 2013 at 9:56 am
I hope you are not initialising the file every time you write to it? If you are you wont be able to restore as you need the whole collection of log backups from the last full/diff backup.
That is the command being issued when the log backup runs. So yes, in the event of a restore, I will need to add in the whole collection of log files.
Are the log files incremental? Typically to most people an incremental backup will take the snapshot/delta of the file. If you update the same row 1 million times then the incremental backup will only have the last value, whereas the log is the history of the file so will have all 1 million updates. You will need to have all the logs fbackups from the last full/diff backup for a point in time restore.
This is where my confusions are. Let's say I have a full backup at 12:00am. And log backup at 12:30am and again at
1:00am. Will my 1:00am log backup have data from 12:00am - 1:00am or just 12:30am - 1:00am?
I am guessing just 12:30am - 1:00am - hence the need to apply log backups "collection" since full backup in the event of recovery. But, am I correct?
Is this what MSDN describes as differential incremental?
Its rare to have to shrink the log file afterwards. Just means that it will waste time and freeze transactions while its trying to regrow the log.
I don't shrink the log file afterwards all the time. I just shrank it for testing purposes to show that the log files are indeed being truncated after backups. And if it's being truncated, the following backup will not have the earlier transactions, correct?
March 28, 2013 at 10:15 am
MSSQL_NOOB (3/28/2013)
.............. [ed]
Let's say I have a
full backup at 12:00am.
log backup at 12:30am
log backup at 1:00am.
Will my 1:00am log backup have data from 12:00am - 1:00am or just 12:30am - 1:00am?
I am guessing just 12:30am - 1:00am - hence the need to apply log backups "collection" since full backup in the event of recovery. But, am I correct?
Is this what MSDN describes as differential incremental?
................
The log backup at 12:30am will have only the data modifications between 12:00:01 am and 12:30:00 am
The log backup at 1:00am will have only the data modifications between 12:30:01 am and 1:00:00 am
So to restore the database to 1:00am yuo will need to restore the full backup + 12:30am log backup + 1:00am log backup.
Is this what MSDN describes as differential incremental? No, a differential backup is a snapshot of the differences compared to the last full backup. An incrementtal backup can mean the differences between the last incremental backup and now.
eg: Backup a folder with 10 files in it (aka Full backup)
alter file A, file B
an incremental backup at this point in time would be fileA fileB
a differential backup at this point in time would be fileA fileB
alter fileC
an incremental backup at this point in time would be fileC
a differential backup at this point in time would be fileA fileB fileC
alter fileD
an incremental backup at this point in time would be fileD
a differential backup at this point in time would be fileA fileB fileC fileD
etc
The differntial is the same in SQL Where as the incremental in sql doesnt exist. The log backup would be the recording of all the keystokes of the editing of each of the documents!
Does this help??
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply