SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Are incremental backups possible
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gmetaj
Starting Member

33 Posts

Posted - 05/19/2004 :  10:07:11  Show Profile  Reply with Quote
Are incremental backups efficient without a full backup. Below is my scenario. I have a database that is fairly large that I want to backup. Let's say the size of the DB is 100 M and I apply differential backups every night. THe size of the DB grows by 1M every day due to changes/updates and new additions to the db.

In BOL it says that differential backups are backups since last full backup, however i am thinking about using WITH INIT, DIFFERENTIAL option when I do the backup.

I would like to know if my logic is correct.

Day 1 Db Size = 100M
Day 2 Db Size = 101M Incremental Backup Size = 1 M
Day 3 Db Size = 102M Incremental Backup Size = 1 M
Day 4 DB size = 103M Incremental Backup Size = 1 M

...
....
Day 29 DB size = 128M Incremental backup size = 1 M
Day 30 DB size = 129M Incremental backup size = 1 M

I was wondering if apply
BACKUP DbName to Disk = "Location"
WITH INIT, DIFFERENTIAL

Will my backup file grow larger and larger every day, that is on day 30 will the backup file be about 1M or 29M. I apply the changes to another database every night and do not want to do a full backup. can i continue doing
BACKUP DbName to Disk = "Location"
WITH INIT, DIFFERENTIAL

without having to do only a full backup ever.

Thank you!

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 05/19/2004 :  10:16:03  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
You could as long as you have the original full backup. I would recommend a full backup at least once a week though. One strategy I've used is this:

Full Backup - Sunday
Differential Backup - Monday through Saturday
Transaction Backup - Every 30 minutes

The differential saved a lot of space and time. The good thing about this strategy is the restore phase. I could get current by restoring:

Full---->Diff (for Wednesay as example)---->Transaction (to 1:00pm for example)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

daveman6610
Starting Member

8 Posts

Posted - 05/19/2004 :  10:17:45  Show Profile  Reply with Quote
Differential backups will backup every database page that has been changed since the last full backup.

So if your scenario, if your 100M database adds 1MB on day one, the size of the differential will be a minimum of 1MB, but will be larger if you change existing data and indexes as well.

Differentials are not a replacement for full backups. They merely act as a stop-gap for faster recovery between full backups.
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 05/19/2004 :  10:21:21  Show Profile  Visit nr's Homepage  Reply with Quote
init only affects the file that is being written - it overwrites rather than adds. I always create a new file including the datatime for backups

>> Will my backup file grow larger and larger every day
It will contain all changes since the last full backup so will keep getting bigger until the next full backup. How much bigger depends on the changes that are being made to the data.

You can keep doing differentials but they will eventually take longer than the full.

You would usually keep more than one differential just in case.
If you never take another full backup then you will lose the database if that backup gets corrupted.
I would suggest taking a full backup at least weekly.


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

gmetaj
Starting Member

33 Posts

Posted - 05/19/2004 :  12:53:02  Show Profile  Reply with Quote
The problem that I have is that I am trying to use backups to implement updates on a database (Read Only) on the internet. I need to send incremental changes from Original Database in the inhouse server to that same database on the internet. The size of the database is rather large and i cannot afford (timewise) to transfer the whole backup file over the internet even weekly. I need to trasnfer only the changes that have happened to the database inhouse to the database on the internet. I apply the differential backup files to the database located on another server by trasnfering the file via ftp. If my backup file grows indefinetly due to the lack of a full backup then i need to look for another solution.

Any recommendations for this scenario?

Thanks a lot in advance.



quote:
Originally posted by nr

init only affects the file that is being written - it overwrites rather than adds. I always create a new file including the datatime for backups

>> Will my backup file grow larger and larger every day
It will contain all changes since the last full backup so will keep getting bigger until the next full backup. How much bigger depends on the changes that are being made to the data.

You can keep doing differentials but they will eventually take longer than the full.

You would usually keep more than one differential just in case.
If you never take another full backup then you will lose the database if that backup gets corrupted.
I would suggest taking a full backup at least weekly.


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 05/19/2004 :  13:10:47  Show Profile  Visit tkizer's Homepage  Reply with Quote
Use transaction logs instead then.

Tara
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 05/19/2004 :  14:21:33  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
Regardless of whether you use transaction logs or diffs though, you still should do a "baseline" restore once a week or so from a full backup. This will become very important to you if you ever have a disaster. You don't want to have to recover a full backup from two months ago, then go through two months of transaction log backups to catch up.

Look at the schema I posted earlier and think about that. It uses a combination of full, diff, and tran backups.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000