| Author |
Topic |
|
stephe40
Posting Yak Master
218 Posts |
 Posted - 2007-05-10 : 14:18:48
|
We have a pretty large SQL Server database, just shy of 400 GB. It is divided up into 24 different data files. The previous DBA here said it is faster to backup the backup individual data files to seperate files. So what he did was issue 6 backup statements like the one below as a step in an agent job.BACKUP DATABASE MYDATABASE FILE = 'Data', FILE = 'data2', FILE = 'data3', FILE = 'data4'TO Data1_4WITH FORMAT Next step:BACKUP DATABASE MYDATABASE FILE = 'data5', FILE = 'data6', FILE = 'data7', FILE = 'data8'TO Data5_8WITH FORMAT etc.. in sequence.What I question is how this is faster than just issuing a backup for the whole database? Has anyone ever ran into this before?- Eric |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-10 : 14:22:29
|
| I've got no experience of this approach, but how difficult is it to get a restore to a consistent point?Kristen |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-05-10 : 14:24:41
|
| http://www.sql-server-performance.com/backup_restore_tuning.aspTara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
stephe40
Posting Yak Master
218 Posts |
Posted - 2007-05-11 : 08:55:17
|
| Thanks for the link. I should have known to check their first. The only thing I found related was this: You can speed all backups, including disk backups, by backing up to multiple backup devices at the same time. SQL Server creates a separate backup thread for each backup device, allowing backups to be done in parallel to multiple backup devices. This feature is very important for very large databases (VLDB), although this technique can be used with databases of any size. [6.5, 7.0, 2000] Updated 3-20-2006Somehow I don't think that this will increase the speed of the backups because right now I am probably bottlnecked by the disk IO anyway. Ill give it a shot.- Eric |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-05-11 : 09:24:15
|
| I don't see how what you have would be faster than a single backup.Well it would because it would back up a bit the database then have less changes to add to the backup at the end because that bit would have taken less time.But this is at the expense of not having a restorable backup and the saving probably woudn't be much depending on how active the database was.Try just doing a full backup - on 400G I wouldn't expect it to take long but that depends on your system and what is going on at the time.The backup you have is backing up multiple files to a single device - I don't think that would give any performance improvement.If you backed up to multiple files (in the "to" clause) and those files were on different disks, controllers etc then I would expect it to be faster - but if you had a single disk then not much.Usually the reason for splitting the database into separate files is to put them on different disks and to make the internal structures per file smaller and hence faster.I prefer a single file unless there is a good reason not to as it is easier to administer and allows you to do an attach with just the .mdf.Was at a company where the (new) dba read about performance and decided to split the database into about 50 different files (without asking anyone - even pointed to the book as justification. He soon decided that was a bad idea and had fun merging them all again.Have you run a test restore?==========================================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. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-11 : 09:57:57
|
Nigel, have you got advice about putting Indexes into (a) separate file(s) for performance?Would it be possible to NOT back them up, and plan to "rebuild" them instead in the event of a restore?Or am I talking tripe?  Kristen |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-05-11 : 10:02:14
|
| >> Nigel, have you got advice about putting Indexes into (a) separate file(s) for performance?Nope.I have a feeling I'm going to do some testing on file backups and restoers this weekend - thanks very much.I've always tried to keep things as simple as possible with backups and don't like trying to restore just some of the objects in a database. The idea of partial restores has always worried me.==========================================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. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-11 : 10:07:02
|
"I have a feeling I'm going to do some testing on file backups and restoers this weekend"Really? That's fantastic, thanks very much  "The idea of partial restores has always worried me."Yup, me to. Its often overlooked in "We've got this data which is common to all our databases, we've moved it to a central database, we've got Referential Integrity issues after a restore of the central database ..."Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-05-11 : 12:12:25
|
| For a 400GB database, I'd recommend SQL Litespeed. It'll decrease your backup time tremendously and the file size will be about 75% smaller then a native backup. We use SQL Litespeed for all of our systems with large databases. It allows us to back them up faster and have huge disk savings on our SAN.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-05-11 : 12:23:55
|
quote:
Originally posted by KristenYup, me to. Its often overlooked in "We've got this data which is common to all our databases, we've moved it to a central database, we've got Referential Integrity issues after a restore of the central database ..."Kristen
People often miss that the reaon for keeping things in the same database is security and restores. It's quite easy to automatically synchronise two databases one way but if both have to be updated or the other way round then it's usually a pain - documented as "a manual process".==========================================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. |
|
|
|
|
Backup individual files vs whole database