×

Loading...
Ad by
  • 最优利率和cashback可以申请特批，好信用好收入offer更好。请点链接扫码加微信咨询，Scotiabank -- Nick Zhang 6478812600。
Ad by
  • 最优利率和cashback可以申请特批，好信用好收入offer更好。请点链接扫码加微信咨询，Scotiabank -- Nick Zhang 6478812600。

@Florida

Wrong reply

yh_abc(yh_abc)
Sorry, LZ, I mis-read your msg.

For 9000 GB (i.e. 9 TB) db, I would use a different approach, such as whether you can partition some big tables, and for those tables with history data, the history data should be put into separate files, and consider them as static (if possible) so you do not need to backup them.

Also I believe 3rd party tool (like RedGate sqlbackup) will help too. In my case, if my DB goes bigger, sqlbackup can support backup in multi-thread, each thread is responsible for one piece of backup file, and of course, you will end up with multi-backup files, but it can reduce the overall backup time significantly (like 50%).
(#5716462@0)
Last Updated: 2009-11-30
This post has been archived. It cannot be replied.
Report

Replies, comments and Discussions:

  • 工作学习 / 学科技术讨论 / 一个 9000GB 的SQLServer OLTP数据库，有什么好的备份方法。俺采用一周一次FULL备份，备份文件被平均分割成30个230GB compressed 文件，分布在不同drive上。每天做differetial BACKUP和日志 backup。有无更好的办法？数据库每天archive，但它的基本数据就这么大9000 GB。 -houseless(糊涂); 2009-11-22 (#5698028@0)
    • 俺的问题是，那个FULL备份要TAKE 7 小时，这还不算拷贝到磁带的时间。 -houseless(糊涂); 2009-11-22 (#5698048@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. -hkchan(0); 2009-11-22 {74} (#5698985@0)
        This was how we backup our financial systems but I can't recall its size.
    • 牛大的数据库，这样备份迟早会累死的，不能archive出一部分吗？ -sunday8(每个年头都有新的梦景); 2009-11-24 (#5701643@0)
    • Get a 3rd party backup tool -yh_abc(yh_abc); 2009-11-30 {256} (#5716443@0)
      900+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 !
      • Wrong reply -yh_abc(yh_abc); 2009-11-30 {651} (#5716462@0)
        Sorry, LZ, I mis-read your msg.

        For 9000 GB (i.e. 9 TB) db, I would use a different approach, such as whether you can partition some big tables, and for those tables with history data, the history data should be put into separate files, and consider them as static (if possible) so you do not need to backup them.

        Also I believe 3rd party tool (like RedGate sqlbackup) will help too. In my case, if my DB goes bigger, sqlbackup can support backup in multi-thread, each thread is responsible for one piece of backup file, and of course, you will end up with multi-backup files, but it can reduce the overall backup time significantly (like 50%).
    • 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分钟即可拷贝完毕。 -ready4u(就等你了); 2009-12-5 {178} (#5728544@0)
      IBM的DB2联机支持这种快速拷贝模式，当然将数据库停掉10分钟很适合做Full Backup.

      当然这种高端磁盘还包含很多其他的技术，例如硬件级别的远程实时备份等。唯一的问题就是采购成本比较高。
      • 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)
    • How long to restore? -sm08(南泥湾); 2009-12-24 (#5762942@0)

More Topics

  • 看大家讨论日语，俺也正在认真学习。其实语言问题应该是很快也很容易就解决的，可能比自动驾驶来的还快。TREK里面，全宇宙的人都在大脑里安装了UNIVERSAL TRANSLATOR，外星人之间相互交流无障碍。废话少说，看视频。。。加上个MIC和耳机，不同国家的人就可以无障碍交流。
  • 肉脸真有搞 IT 的？Linux 把俄国中国伊朗踢出去了好像肉脸 IT 没啥感觉似的
  • 请问web developer和software developer技术上有什么区别？
  • Elon Musk 带着川普总统奔向火星了。
  • 扔硬币的实验研究
    • 枫下论坛主坛 /  工作学习 /  学科技术讨论