| Author |
Topic |
|
kmfarooqi
Starting Member
7 Posts |
 Posted - 2008-04-21 : 02:51:33
|
| when i take the differential backup, its size is size is same as full backup size. can you please help me to solve this problem.note: i have several different databases which are doing good when i take their differential back up (less in size) except only one database.thanking you in advance.Khader |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-04-21 : 22:19:05
|
| Diff. backup doesn't just backup rows changed since last full backup. if you change a rows, sql will backup whole extent where the row exists. And will backup related extents in other tables if source table has relationship to them. |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-04-21 : 22:41:46
|
| Are you running a batch job that updates a lot of rows?You would be better off doing another full backup.==========================================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. |
|
|
|
steel
Posting Yak Master
107 Posts |
Posted - 2008-04-24 : 06:22:22
|
quote:
Originally posted by kmfarooqi
when i take the differential backup, its size is size is same as full backup size. can you please help me to solve this problem.note: i have several different databases which are doing good when i take their differential back up (less in size) except only one database.thanking you in advance.Khader
when the full backup of the database is taken by you |
|
|
|
kmfarooqi
Starting Member
7 Posts |
Posted - 2008-04-24 : 06:36:43
|
| one day before the differential backup |
|
|
|
steel
Posting Yak Master
107 Posts |
Posted - 2008-04-24 : 06:57:55
|
quote:
Originally posted by kmfarooqi
one day before the differential backup
there might be some stored proceedure or batch script running try to take full backup and then after that immediately take differential backup and see the results. |
|
|
|
kmfarooqi
Starting Member
7 Posts |
Posted - 2008-05-07 : 02:37:51
|
| I performed complete backup and after updating the database with one day of data i performed differential backup, the file size should be relatively small but its getting larger and larger so i expelicitly terminate the command from query analyzer. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-05-07 : 02:49:19
|
How do you backup?Do you have a script you can post or do you use EM?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
kmfarooqi
Starting Member
7 Posts |
Posted - 2008-05-07 : 03:20:35
|
| I performed complete bakup using EM and differential backup using "backup database OldConsolidatedDatabase to disk = '\\192.168.0.25\F\differential backup\Diff Cons on 06 May 08.bak' with differential" |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2008-05-07 : 10:17:59
|
| The backups are appending to the device, unless the date in the filename is unique to each backup. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-05-07 : 15:06:53
|
| Use with init option to overwriye backup file. By the way, diff backup file size will get bigger since it contains all changes since last full backup. |
|
|
|
kmfarooqi
Starting Member
7 Posts |
Posted - 2008-05-08 : 01:28:03
|
| when i took complete backup, the file size was 48 GB and i named it is "Comp OldConsolidated 5 May 08" and next day new data is updated in database and then i took the diff backup and i named it as "Diff OldConsolidated 6 May 08" and the file size should be small very beacuse only one day of data is updated in the database. This is what not happenining the file size is growing and hence i had to explicitly terminate the command.What happens with init and how should i use it. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-05-08 : 22:15:39
|
| Can you post your backup statements? |
|
|
|
kmfarooqi
Starting Member
7 Posts |
Posted - 2008-05-10 : 02:06:49
|
| backup database OldConsolidatedDatabase to disk = '\\192.168.0.25\F\differential backup\Diff Cons on 06 May 08.bak' with differential |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-05-10 : 11:57:14
|
| Keep in mind that diff backup not just backup rows changed, sql will backup whole extent if row in it is changed and all extents involved in fkey relationship. |
|
|
|
number2jcb
Starting Member
1 Post |
Posted - 2008-06-09 : 12:26:49
|
| I am also having this issue. Instead of using a full solution, I was just using a Full Backup at 2am, and then Diff Backups every 20 mins from 8am to 8pm. I know it isn't the best, but when I need to restore to one of the spots it is quick and easy. Anyway, my differential backups used to be pretty small and get bigger throughout the day. About a month ago, the daily diff backup started to grow by the size of the database every twenty minutes. I didn't change any settings, it just stopped working as usual. When I look at the sql server logs, it shows the full backup happening, and then the next event is the first differential backup. I do all maintenance items before the full backup just to prevent this issue. Any ideas? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-06-09 : 22:19:43
|
| I prefer log backup, it gives you point of time recovery. |
|
|
|
|
differential back and full back up sizes are same