July 4, 2012 at 9:08 am
Hi
Is there any possibility to take the incremental backup in sql server 2005.
What is the use of it ?
Which situations(platforms) we are using this one?
July 4, 2012 at 9:21 am
SQL doesn't have incremental backups, it has differential (yes, they are different)
Main use would be to speed up restores when you can't take full backups every day (due to available maintenance windows).
Consider the scenario where full backups are taken weekly (on sundays) then log backups every 15 minutes. If it would be necessary to restore that database to Thursday afternoon, you'd need to restore the full backup, then around 4 days of log backups (at 96 log backups a day). That's a lot of restores. Now consider full backups on sundays, differential backups every night and log backups every 15 minutes. Now to restore to Thursday afternoon, you'd restore full, last differential and less than 1 day of log backups. Very likely much quicker.
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
July 4, 2012 at 9:22 am
Completely agree with Gilamonster. Differentials save time in recovery, and save space on your disk with smaller backups than fills.
However you need the full backup, and if you implement differentials, make sure you have 2 or 3 copies of your full backup in case you lose one.
July 4, 2012 at 9:23 am
SQL Server does not support incremental backups. A differential backup would be capturing data that has changed since the last full backup. No matter how many diff backups are created, it will still follow that guideline.
July 4, 2012 at 9:27 am
I am agree with you
as per you it will take less time and space.In this case how can i take this backup
July 4, 2012 at 9:30 am
gantavasu (7/4/2012)
I am agree with youas per you it will take less time and space.In this case how can i take this backup
backup database YourDatabaseName
to disk = 'C:\YourBackupDir\YourDiffBackup.bak'
with differential;
go
July 4, 2012 at 12:55 pm
Things to be aware of with differential backups:
A major re-indexing operation will change virtually all the pages in the database, so any differential backup made after that will be the same size as a full backup. Make sure you take your weekly full backup after any major re-indexing completes.
Any nonstandard backups will impact the differential backup chain, so you have to have that full backup to be able to do the restore of the differential. An example would be a DBA or developer making a full backup in the middle of the week to be able to restore to a test server. For this reason, you should make certain that you are also running transaction log backups and keeping then long enough to be able to restore forward from your last weekly backup.
July 4, 2012 at 1:12 pm
And if you do need to take ad-hoc full backups, take them WITH COPY_ONLY, that way they don't mess with the differential backups
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
July 4, 2012 at 1:51 pm
GilaMonster (7/4/2012)
And if you do need to take ad-hoc full backups, take them WITH COPY_ONLY, that way they don't mess with the differential backups
I was thinking more of a situation where the person making the ad-hoc full backup didn't understand the implication of what they were doing and probably wouldn't know or think of doing a copy only backup.
If they did understand, they they would probably realize that it would be faster to restore from the weekly full and the latest differential.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply