SQL Server Performance Forum – Threads Archive

What should be the best Backup Strategy

Hi All If possible can someone help me in making the backup plan and strategy.
I have three databases with the following sizes. 1. 55GB
2. 7 Gb
3. 100 MB. The 55GB database is growing almost 300 MB on daily basis. Currently we are making the daily backup in midnight on one of the HDD.The recovery model is simple and no backup of transaction logs.
Now as our system is very critical and we dont want to loose any last moment data so we are planning to make the backup of log transactions also and shift our change our recover model to full instead of simple. Now few things to ask: 1. I want to know that making the backup of Log transactions is better or differential backup is better? 2. Can anyone suggest which media ( HDD, MOD etc…) should be adopted for the backup purposes. I see lot of sites, some are suggesting the HDD and some are suggesting the MOD. Since our data is very critical so I cannot afford the malfunction of backup media in case of any failure. The money is not an issue for any solution. 3. This would also be very appreciable if anyone can suggest the Vendor for the Backup solution. 4. Is there any performance degradation if we make the backup of transaction logs of every hour? Regards Mansoor

This is a loaded question Mansoor. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] <blockquote id="quote">quote:<hr height="1" noshade id="quote"> 1. I want to know that making the backup of Log transactions is better or differential backup is better? <hr height="1" noshade id="quote"></blockquote id="quote"> Depends on your needs. Most people don’t really use differential backups. If you need a really quick recovery time though, you might consider taking one every couple hours during the day so you don’t have to recover ALL the transaction log backups. In addition, if you’re pressed for space, sometimes you can save space by doing a differential nightly and a full weekly. It all depends on your needs. <blockquote id="quote">quote:<hr height="1" noshade id="quote"> 2. Can anyone suggest which media ( HDD, MOD etc…) should be adopted for the backup purposes. I see lot of sites, some are suggesting the HDD and some are suggesting the MOD. Since our data is very critical so I cannot afford the malfunction of backup media in case of any failure. The money is not an issue for any solution. <hr height="1" noshade id="quote"></blockquote id="quote"> "The money is not an issue?????" WOW!!!! I would go with a SAN tape solution that makes backup to tape and ships offsite. Use a fiber-to-SCSI bridge so the tape is integrated into the SAN fabric. If you need a hotsite, and money REALLY isn’t an issue, then do hardware data replication over a WAN. <blockquote id="quote">quote:<hr height="1" noshade id="quote"> 3. This would also be very appreciable if anyone can suggest the Vendor for the Backup solution. <hr height="1" noshade id="quote"></blockquote id="quote"> This again depends on your needs. IBM and EMC both have some good solutions. I would stick with Veritas for the software if you use tape. <blockquote id="quote">quote:<hr height="1" noshade id="quote"> 4. Is there any performance degradation if we make the backup of transaction logs of every hour? <hr height="1" noshade id="quote"></blockquote id="quote"> There is ALWAYS performance degredation and overhead with a backup and/or DR solution. Anyone who tells you otherwise is a liar. MeanOldDBA [email protected] When life gives you a lemon, fire the DBA.

ON the backup strategy part of it I would go with Log shipping utility, which is hassle free as long as the log restores are continued without any issues. In order to confirm the status of the backup ensure to restore on the secondary location and use the application to test the database. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>