×

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

@Calgary

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

  • 现在社区游泳池招救生员巡逻，过一会还要不停的点人头，这工作是否很快就被AI代替了，装一个摄像头后面是AI处理系统，这样只招一个等着救人就行了，人头沉下水一定时间立马强光照射，还可以分析水下人的状况来判断是否溺水等
  • 新冠疫苗引发心肌炎? 最新研究这样看
  • 这道几何题有解吗?
  • SAM 2 Demo
  • 如何在 AWS EC2 实例上构建 WordPress 应用程序?
    • 枫下论坛主坛 /  工作学习 /  学科技术讨论