Unanswered: Howto work and manage backup log files???
I have run into some problems with logfiles and backup logfiles (MS SQL server). I have read much about them but uptil now I dont seem to grasp how it works. Specially the part of working with backup log files.
Can someone please help me with this?
What I want todo is to create backup log files, so I can restore the database to any point in time before a failure occured. But using this method create's a problem with the backup log files. They get very big.
So how can I use backup log files without getting to large backup log files?
How can I shrink on a regular basis the backup log files and still be able to restore the database?
FIrst of all confirm the following :
How big is the Transaction log file?
What is the schedule of backup interval of Tlog?
Any network issues between live and backup servers?
What kind of transactions performed on the database?
Its not worth shrinking the Tlog on a schedule basis when frequent updates occuring to the database.
A basic backup schedule consists of a scheduled database backup with intermittent log backups.
For example, you might run a database backup once a week, and then run log backups every evening. On a more critical database you might run a backup every evening and a log dump every hour.
The log dumps will only contain the transactions since the last log dump or backup, so they should not grow too big.
You should also establish a backup cycle for retaining your database and log backups. For instance, if you backup your database every evening with hourly log dumps, you might just want to retain your log dumps for 2 days, allowing you to do point-in-time recover for corruption issues less than 2 days old. The nightly backups you might decide to keep for a full week, just in case you want to recover data. You might also decide to keep weekly backups (Sunday evening, for instance) for a full month, and retain the first backup of the month for a full year. For good measure, you could burn an annual backup of your database to CD for indefinite retention.
The point is, backup strategies depend upon the requirements of your application and your environment. Think about some scenarious that could occur, including "worst case" disasters, and try to come up with a backup strategy that will help you recover from them quickly.
One last imperative: TEST YOUR BACKUP STRATEGY REGULARLY. You need to actually go through the steps required to recover from the disaster scenarios you planned for, and document the steps. This way, you know they will work and you know you can do them quickly, efficiently, and accurately should an emergency occur.
If it's not practically useful, then it's practically useless.