September 29, 2003 at 11:01 pm
Hi All,
The question is about backup of large DB (<50GB).
What is the best way to create Disaster Recovery plan for big Db?
I understand that it is individual in each case, but it will be good to know the 'best practice' based on your experience. Any suggestion will be appreciated.
September 30, 2003 at 12:10 am
What is the recovery model of your database??.
madhusudannaidugundapaneni
Madhu
September 30, 2003 at 12:14 am
Recovery: "Truncate log on checkpoint"
September 30, 2003 at 7:44 am
That's not a large database. I have one that is over 200GB.
Here are some things to consider:
Your system just crashed. You tell your boss you can restore from backups but everything since midnight is unrecoverable. Is your boss going to be 'pissed'?
How much data can you afford to lose?
That needs to be answered before you decide how to do backups.
My database is mission critical 7/24. I do one full backup each day, one differential 12 hours after the full backup, and the transaction logs are backed up every four hours after the full back up is done.
I can restore my database with a maximum of 4 hours of data loss. And that's only if the crash happens just before the next transaction log backup.
-SQLBill
October 1, 2003 at 5:35 pm
Thank you very much.
My Db is operated 7/24 and will grow quick.
The best way to do the same way as you do.
Few questions how to do it:
1. What do you use for daily full backup - SQL backup on type, disc or third party backup Software (we have BackupExec)?
2. How do you compress the data?
3. Do you store differential and log backup on disk or type and what do you use as a backup software.
4. To restore DB what we need to do first: apply full backup>then last defferential>Log backup? If you do log backup every for hours, why do you do the differential backup?
As I understend the log backup keeps all changes after last full buckup. Do we really need to do Differential and log or one of them is enough?
Is it possible to restore from backup created by different software e.g. Full backup using Backupexec, Log using SQL Server?
October 2, 2003 at 8:06 am
1. What do you use for daily full backup - SQL backup on type, disc or third party backup Software (we have BackupExec)?
Ans-I do the 'native' SQL Server backup to disk, then I use Backup Exec to copy those files to tape. Each day the .bak files (on disk) are overwritten. That gives me the most recent backups on disk anything else is on tape.
2. How do you compress the data?
Ans-I don't compress the initial backups. I have Backup Exec set to use Hardware compression and if not available use software compression.
3. Do you store differential and log backup on disk or type and what do you use as a backup software.
Ans-I do full, differential, and transaction log backups to disk first; then copy them to tape (See #1).
4. To restore DB what we need to do first: apply full backup>then last defferential>Log backup? If you do log backup every for hours, why do you do the differential backup?
As I understend the log backup keeps all changes after last full buckup. Do we really need to do Differential and log or one of them is enough?
Ans- I do a differential backup so I don't have to restore several transaction logs. At the end of the day, I only need to restore the full backup, the differential and two transaction logs. If I didn't do the diff backup, I would have to restore the full backup and four transaction logs. The differential backup is only important if you are doing lots of transaction log backups. It will cut down on the amount of logs you will have to restore.
Is it possible to restore from backup created by different software e.g. Full backup using Backupexec, Log using SQL Server?
Ans-never tried it. It can probably be done. You would just have to make sure the last file restored from Backup Exec is done WITH NORECOVERY. That way other backups can be restored.
Hopefully that answered your questions.
-SQLBill
October 2, 2003 at 4:13 pm
Thank you very much SQLBill!
What sort of documentation would you suggest to get for the Db and DRP?
October 3, 2003 at 7:38 am
What's DRP and what kind of documentation are you looking for?
The Books OnLine (BOL) is my main source of information for SQL Server.
Do you mean documentation from management on how backups are to be done?
For that I sent a formal memorandum to my management providing my suggested methods of doing backups. I gave them a couple of different ways and provided pros and cons. Then I took their responses, came up with the final solution and sent a memo back. The memo stated that per my memo of 'date' and responses, this is the method that I'm going to use (of course the wording was more formal). I also have email 'chains' between me and management on how to do backups and restores. All of that includes the maximum time they would like to have the server down before it's recovered. (I also included that unforeseen circumstances may cause a recovery to exceed the requested length of time).
-SQLBill
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply