September 24, 2008 at 11:56 pm
Hi all,
I set the full recovery model in database and i configured full backup every day at 10:00 PM, now db size 185 GB so whenever working of backup sehuedule it is taking backup time hardly 6 Hrs, daily DB Growth is 1 GB.
Please tell me anybody, how to reduce the total backup time? if i configure other recovery model
Thanks
September 25, 2008 at 12:07 am
The recovery model doesn't affect the full backuptime, it only eliminates the need for logbackup in case you have a SIMPLE recovery model.
If you're running out of backup time, you have some options:
- change your backup strategy, i.e. use a full backup on Sunday and a incremental backup on working days. But be aware this will increase your restore time. Also, make sure you've tested this option
- Buy other backupsoftware. Most backupprograms have compression included. I switched to SQLSafe which reduces my 160GB backuptime from 100 minutes to 14 minutes and decreased the backupsize by 80%
- Switch to another backup method, i.e. dump the database to a disk and let your backupprogram backup this file to tape (I assume)
- Create a standby/mirror/replication database and do a backup on that database instead of your live database.
Wilfred
The best things in life are the simple things
September 25, 2008 at 12:42 am
thanks for reply.. i am new SQL DBA
If i take full backup on sunday, so recovery model should be Full. ok
But if i take Incremental backup (simple), so recovery model should be simple and this model should eliminate the logs, ok fine,
But Monday to saturday it taking incremental backup it means simple recovery model, so sunday how to switchover the full backup model.
As per your reply, i have going to be configure the two different shuedule jobs 1 is incremental backup (simple model), and 2 full backup model on sunday, i have doubt on sunday how to switchover the full backup model? is it sql itself change atuomatically or manually change full model on sunday.
September 25, 2008 at 1:07 am
I think you may be confusing the FULL recovery model with FULL database backups - and switching recovery models back and forth between full and simpl,e is probably not the way to go.
It sounds like you should be in FULL recovery mode, so that you can take log backups and if required recover to any point in time, in which case something like a full backup once a week, differential backups daily and log backups every few hours is probabaly a good starting point.
It is very difficult to be definite about this without knowing a lot more about how much data gets changed regularly, and when you say the databse is 150 Gb is that the data file size or data and log files?
Mike John
September 25, 2008 at 2:46 am
sorry for the dealy...
Now i got clear idea.. so i will be make configure the daily differential backup and one full backup on every sunday and everyday at 1 hrs Tlog backup, ok fine
But i have doubt for example tuesday server has been crashed , so what shuould be followed for recovery database. as per my steps as follows
1. restore on sunday full backup from backup file..
2. restore on monday diifferential backup from backup file
3. restore the Tlog backup on last day full backup after every 1 hour tlog backup till cash time.
pls tell me ,the above steps are correct or worng
thanks
September 25, 2008 at 2:57 am
Yes, that should work. Make sure you add the NORECOVERY parameter to eacht restore statement, until you're at the latest logfilebackup. Restore that backup with the RECOVERY parameter.
I stronly advise you to test this recovery scenario before you implement this in a production environment
There are a lot of articles in the internet about this.
Wilfred
The best things in life are the simple things
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply