asked
Transaction Log Backup
Hello, I am a Jr.SQL Server DBA and i have a task to setup a backup plan for a database. At present the database is backed up everyday i.e full backup everyday which is causing an issue due to the collision of other jobs as the job takes 3 hrs to complete. The database has more reads than writes and also need to have point in time recovery. The backup plan which i think can be implemented is: 1. Weekly once full backup. 2. Everyday differential backup. 3. Hourly Transaction log backup. Is the backup plan correct? On what basis i need to set the transaction logs backup schedule (minute,hourly)? Also can i ignore transaction logs and schedule only differential backups as the differential backups will also have transaction logs?(Pls correct me if am wrong)
It depends on your [RPO][1]! There is no basis. You should ask this question -- On what basis i need to set the transaction logs backup schedule (minute,hourly)? -- whoever makes decisions in your company. If this database server goes down now, how much data can we lose? Based on the answer, you'll create you RPO. If the data doesn't change that much and the size of the full backup is much larger than the differential backups then it makes sense to take weekly full backup and daily differential. Transaction logs give you much more flexibility during recovery! You should definitely take them but with which frequency depends on your RPO. Also make sure you run integrity checks on this database at least once a week before you take the full backup, make sure your database's page_verify_option is set to CHECKSUM, you take backup with CHECKSUM as well. It will put some load on your CPUs but it's worth it! Keep in mind that you don't have a good backup unless you test it! Restore the backups with CHECKSUM to a different server regularly and ideally run the integrity check after the restore. Also keep enough backups to go back in case of a corruption! Disks are cheap -- but losing data due corruption is priceless. Read Paul's blog, he has tons of very good info on this subject -- [Paul Blogs Backup/Restore][2] [1]:
http://whatis.techtarget.com/definition/recovery-point-objective-RPO [2]:
http://www.sqlskills.com/blogs/paul/category/backuprestore/
@SQLRookie The frequency of a TLOG BU should be decided by the business. If it is once an hour you have an RPO of an hour. So if you lose the DB 1 minute before the next TLOG BU you will lose 59 minutes of data (potentially).If this business is ok with this then hourly TLOG BU is fine. You need to push the business for that decision.
Denis, You explanation is good. What I meant about the question "On what basis i need to set T.Log bkp?" is that how to know how frequently the data changes? I had used a query which showed me reads and writes ,so if am not wrong less writes means less data change right?
15 People are following this question.
