Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
idxtb
Starting Member
1 Post |
 Posted - 2007-06-29 : 03:22:47
|
| Hi, After some advice - I have a SQL Server 2005 database which is part of an anti-virus setup. The main database is 25Gb is size, and it is running in simple recovery mode. There are two backup jobs in place, one to do a differential backup each Mon-Sat, and one to do a full backup on Sun. Although the backups do get done they are taking 5 hours to do. Any wiz out there care to suggest what the problem is, I would've though that maybe an hour was more acceptable ?Cheers,Gordon |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-29 : 04:06:51
|
| "Any wiz out there care to suggest what the problem is"Well, a full backup may take a while. And a Differential Backup will take a while if a high percentage of the database changes - how dot eh size of the Full and Differential Backup files compare? If the differential backup file is, say, more than 50% the size of the Full backup it would probably be better to do Full Backups.The anti-virus may well be causing problems ...... and if the server is very heavily used, that may be slowing down the backup too. Although presumably the backup is happening at a "quiet" time during the night? If so I would suspect the anti-virus software being set up wrongly, or running a virus scan at the same time as the backup is running.Kristen |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-29 : 05:17:09
|
| If you have a batch job that changes a lot of rows the next diff will be large - and so will all diffs up to the the next full backup.You need to take a full after any large batch jobs. So if you do a lot every night then diffs probably aren't worthwhile.There are things yo can do to speed up the backups probably - make sure it's to a different disk, partition the data to read only and updatable, stripe the backups across drives, change your database/process design so that less pages are changed,use a 3rd party backup utility that compresses the data but first of all look at what is being backed up and what you are backing up.==========================================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. |
|
|
|
|
|
|
|
|
SQL Server 2005 Full/Differential Backup Issues