Forum Discussion

puneetd's avatar
puneetd
Level 3
8 years ago

SQL Backup with different policy type

Currently, We are taking backup of SQL database in flat file (backup of .bkp file) using NetBackup 7.7.3. We are thinking about changing it from flat file backup to application backup using NetBackup SQL policy.

Which is better in both if we compare it on behalf of bandwidth, security, restoration, bandwidth utilization and other parameters? 

 In current sitution this backup of this policy takes approximately 16 hr to finish. Will it improve after changing the policy and minimize the backup time?

7.7
NetBackup 7.7.3
SQL
Windows Server 2008 R2
  • Michael_G_Ander's avatar
    Michael_G_Ander
    Level 6
    8 years ago

    Bandwidth depends on where the bottleneck is. Tuning for SQL agent backup is of course a little different than for file backup, but ultimately it is Netbackup that is doing the transport between client and media server.

    It is better restore wise in my opinion as you get a 1 step restore versus the 2 step one you probably have today.

    Security might be slightly better as you don't have flat files that can be copied.

     

  • watsons's avatar
    watsons
    Level 6
    8 years ago

    From my observation, the main reason people moving from the DB flat files backup to the SQL policy backup, is to improve their RTO & RPO.  That means you can cut down your resources and time for recovery/restore DB and it also allows restore up to a certain point. In short, more flexibility when it comes to restore.

    Talking about backup performance, you can take into consideration of:

    1) Does it take long for you to generate the DB flat files (.bkp files) ?  if so that might make more sense to use SQL policy backup because you don't need to do that anymore.

    2) 16 hours for flat files backup is quite long, I am not sure if using SQL policy would that save you time but there are settings for you tune in SQL policy (multiple stripes etc.)

    Again, it all comes down to how flexible you want your recovery to be. I still have a customer who doesn't mind using flat file backup as the SQL database is not as critical. 

  • manatee's avatar
    manatee
    Level 6
    8 years ago

    imho, it is better to use SIP from a backup admin point of view as you don't have to muck around with backing up and restoring that flat file. from my DBA's point of view, from what i learned it's cumbersome to restore a flat file.

    as for the performance, there is also the hardware part you have to investigate. is your backup using the same NIC or is there only a single NIC (network interface card) in your client? would be better to have a secondary NIC and do the backup traffic there. to give you an idea, my Oracle db which is around 2TB took more than 15 hours to backup. that was before i installed a secondary NIC and route all backup traffic there. now it only takes less than 3 hours.

    so poke around the hardware part too.