Topic: 一个 9000GB 的SQLServer OLTP数据库，有什么好的备份方法。俺采用一周一次FULL备份，备份文件被平均分割成30个230GB compressed 文件，分布在不同drive上。每天做differetial BACKUP和日志 backup。有无更好的办法？数据库每天archive，但它的基本数据就这么大9000 GB。 @佛州华人论坛:佛州枫下论坛 The Rolia Forum of Florida

工作学习 / 学科技术讨论 / 一个 9000GB 的SQLServer OLTP数据库，有什么好的备份方法。俺采用一周一次FULL备份，备份文件被平均分割成30个230GB compressed 文件，分布在不同drive上。每天做differetial BACKUP和日志 backup。有无更好的办法？数据库每天archive，但它的基本数据就这么大9000 GB。 -houseless(糊涂); 2009-11-22 (#5698028@0)

What is your objective? What is your backup strategy? -hkchan(0); 2009-11-22 (#5698168@0)
how can you recover it? what's your rto? copy another set of disks. and then run backup from there. -saper((㊣金旺旺㊣)); 2009-12-5 (#5728653@0)

Did you just backup your database once a week? -hkchan(0); 2009-11-22 (#5698185@0)

full backup once a week, incremental backup everyday. typical strategy. -coolmao(酷猫*Arrakis); 2009-11-22 (#5698192@0)

My bad! I missed the differential backup part. -hkchan(0); 2009-11-22 (#5698195@0)

If money is not an concern, then try split-mirror backup. Backup your full database to another storage set. -hkchan(0); 2009-11-22 (#5698211@0)
Depends on your RPO and RTO, as hkchan said, if you don't care the cost, disk array based backup is the best plus the traditional backup software TSM/Netbackup etc. -ddlldd2000(hunting); 2009-11-22 (#5698796@0)
谢谢，俺已经split backup to30 files，道是在考虑增加一个SAN CONTROLLER专门给备份。MY objective就是想把备份时间加快到4--5小时。俺准备split backup from 30 files to 50 files试试，再者看看有无好的第三方软件。 -houseless(糊涂); 2009-11-22 (#5698928@0)

But with split mirror, it only takes a few minutes to offline your backup disks array. Again, if you have a lot of money, you can have a few backup disks arrays and take whatever time you need to backup your data to tape.This was how we backup our financial systems but I can't recall its size. -hkchan(0); 2009-11-22 {74} (#5698985@0)

牛大的数据库，这样备份迟早会累死的，不能archive出一部分吗？ -sunday8(每个年头都有新的梦景); 2009-11-24 (#5701643@0)
Get a 3rd party backup tool900+GB is small in my production, we have 7 dbs which are 1.5TB in average. We use RedGate sqlbackup tool to do the backup (weekly full backup + daily diff backup + 20 min t-log backup)

For full backup, it usually takes 4 to 5 hrs.

Hope this helps ! -yh_abc(yh_abc); 2009-11-30 {256} (#5716443@0)

1. logshipping 在standby server 端备份。 2. partition table在file group的不同文件上，只备份当前update的partition. 3. sql 2008 compression 备份速度快因为write block减少。 4. split mirror硬件实现。5. 第三方工具备份效率高。 -walkthrough(); 2009-12-1 (#5719660@0)

谢谢，1.公司不打算买机器for logshipping 2. 全数据库就三个巨大partition tables, 在三个filegroup里面,可以考虑filegroup在分多一些 4.俺需要问storage team. 5正考虑lite-speed，3。使用了compression 不行。 -houseless(川岛房子); 2009-12-10 (#5737842@0)

说个高端平台的技术：快速拷贝。IBM DS8000磁盘系统里面有个技术叫FlashCopy, 即在瞬间完成所有磁盘的拷贝，你的9TB也许不到10分钟即可拷贝完毕。IBM的DB2联机支持这种快速拷贝模式，当然将数据库停掉10分钟很适合做Full Backup.

当然这种高端磁盘还包含很多其他的技术，例如硬件级别的远程实时备份等。唯一的问题就是采购成本比较高。 -ready4u(就等你了); 2009-12-5 {178} (#5728544@0)

flash copy can be completed in seconds. but it has performance impact on the production DB. -saper((㊣金旺旺㊣)); 2009-12-5 (#5728665@0)

ANY backup has performance impact to PRD DB, disk array based PIT (Point In Time) copy has the smallest impact. -ddlldd2000(hunting); 2009-12-6 (#5730436@0)
一般FlashCopy之后，硬件会在后台拷贝所有数据。但它有个选项，即只拷贝被修改的原始数据。如果数据库不是修改频繁，性能影响应该不大。 -ready4u(就等你了); 2009-12-7 (#5730912@0)

you don't have to stop database to do the full backup with Flashcopy. -ddlldd2000(hunting); 2009-12-6 (#5730428@0)
谢了，可以考虑，每天停SQLServer十分钟，用此技术备份全部的数据文件。 -houseless(川岛房子); 2009-12-10 (#5737840@0)
ds8000 还算高端的? 快照技术备份时会很影响生产卷,使用EMC BCV之类的才行,不过楼主单位估计不肯出钱买高端的设备。 -vmale2(游玩人生); 2009-12-11 (#5741591@0)

@Florida