June 1, 2009 at 5:47 am
Hello,
I wonder if anyone would be kind enough to offer some comments? I am an Oracle DBA by trade . . .
/* there will be a pause for the hissing to die down */
. . . and I have been trying to make sure our backup strategy is decent. (SQL Server has mainly been left as a Windows sysadmin issue.)
What we are doing is a full daily backup of all databases ("master", "msdb", "model", user dbs) to disk, which is copied to a NAS. The last 28 days are kept on the NAS.
In addition there is a separate weekly tape backup using Veritas NetBackup (with the SQL Server extension).
The transaction logs for "model" and user dbs are backed up every half an hour, and the last 48 hours are kept on the NAS. (The idea is to overlap the last two full backups, in case the most recent file was bad.) This is done mainly for disaster recovery. Neither of our two instances are very busy, and the log backup size is small. There is no disaster recovery target time.
I set "model" to Full recovery mode after reading an article on MSDN about it. (can't remember where, though). I think that "master" has to remain as Simple; I am not sure about "msdb", though - should (can?) it be set to Full as well? It seems pretty important.
I have done successful test restores from the NAS files though not PIT (I want to try that this week). AFAIK no-one has ever tried to restore from NetBackup . . . :hehe:
What do experienced SQL Server DBAs think of this setup? Are there any suggestions or improvements I might make? Thanks for any feedback at all.
June 1, 2009 at 6:11 am
Hi Marmot
This looks good for me, saying that it depends upon what the company really needs, Say i worked for a company where they dint have any backup at all ( you will rolling on ground by now), then i changed that, There was a few question that needs to be answered when considering back up and DR
(i) What kind of downtime can the company afford
(ii) How much data loss can the company afford
(iii) How much money the company is prepared to invest on DR
(iv) Is there any legal term the data has to be saved.
This are the basic question that needs to be answered before we say if the plan is good or not.
When you ask the above questions to your company, the answer to the question 1 will be 0 downtime, but when you come down to Question 3, most likely the answer for the first question will change 🙂 ( as per my experience ) 🙂
Hope this helps
🙂
June 1, 2009 at 6:22 am
Don't bother with tran logs of model. The database should never change (it's used as a model when creating new databases or recreating tempDB). Other than that, looks OK. Is half an hour's data loss acceptable in the case of a disaster?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 2, 2009 at 12:24 am
Hi ,
I configured our backup strategy as below. I have couple question and Please advice me if I need to change anything in our strategy. We have sql server 2005 Enterprise edition x64.
1. Full back of all databases including system databases daily once(at 1:00AM)
2.Differential backup of all databases excluding system databases at every 5 hrs(starting at 6:00 AM and 10:00 AM,3:00 PM, 8:00 PM end at 9:00 PM)
3.log backup of all databases except master every 1 hr starting from 2:15 AM and 3:15 AM, 4:15 AM, 5:15 AM, 6:15AM, 7:15 AM, 8:15 AM, 9:15 AM, 10:15 AM, 11:15 AM, 12:15 PM, 1:15 PM, 2:15 PM, 3:15 PM, 4:15 PM, 5:15 PM, 6:15 PM, 7:15 PM, 8:15PM, 9:15PM, 10:15PM, 11:15PM, and ends at 12:15 AM
Questions:
1.I used to run log backup every 1 hr starting from 1:00 AM before, but reading on this forum, to aviod differential backup and log backup at same time, I increased 15 mins more. Now is this makes sense?
2.I created 3 folders called Full, Differential and Log in Backup directory as below and respected backups goes into that folder.
K:\Backup\Full
K:\Backup\Differential
K:\Backup\Log
We have 20 databases and there will be Many log backup files K:\Backup\Log(which occurs every 1 hr)
is this the normally experienced DBA's follow??
3.Is every 1 hr log backup for system bases is necessary? or can exclude them from log backup??
which way experienced DBA's follow??
4.Is there any problem, if the log and differential backups occur at same time? is the same way you guys do to avoid occurrence of log and diff backups at same time as I did(by increasing 15 min or something like that)
5. I created a sql agent job to delete old backups files older than 3 days by creating 3 steps in one job using VB script.
step 1: deletes the .bak files in K:\Backup\Full
step 2 : deletes the .bak files in K:\Backup\differential
step 3:deletes the .trn files in K:\Backup\log
is the above method to delete old backup file is a good approach or not?
how you guys delete old backup files?
6.Taking Tape backups from above 3 folders at 3:00 AM daily.
is tape backups has to run daily or weekly???
thanks for your inputs
June 2, 2009 at 5:57 am
1. Full back of all databases including system databases daily once(at 1:00AM)
2.Differential backup of all databases excluding system databases at every 5 hrs(starting at 6:00 AM and 10:00 AM,3:00 PM, 8:00 PM end at 9:00 PM)
3.log backup of all databases except master every 1 hr starting from 2:15 AM and 3:15 AM, 4:15 AM, 5:15 AM, 6:15AM, 7:15 AM, 8:15 AM, 9:15 AM, 10:15 AM, 11:15 AM, 12:15 PM, 1:15 PM, 2:15 PM, 3:15 PM, 4:15 PM, 5:15 PM, 6:15 PM, 7:15 PM, 8:15PM, 9:15PM, 10:15PM, 11:15PM, and ends at 12:15 AM
Questions:
1.I used to run log backup every 1 hr starting from 1:00 AM before, but reading on this forum, to aviod differential backup and log backup at same time, I increased 15 mins more. Now is this makes sense?
Yes, its good.
2.I created 3 folders called Full, Differential and Log in Backup directory as below and respected backups goes into that folder.
K:\Backup\Full
K:\Backup\Differential
K:\Backup\Log
We have 20 databases and there will be Many log backup files K:\Backup\Log(which occurs every 1 hr)
is this the normally experienced DBA's follow??
Yes, with a sensible name appended at the end of the backup file
3.Is every 1 hr log backup for system bases is necessary? or can exclude them from log backup??
In my view you dont have to, but I would say it really depends on your SLA's. I take once a day backup of all system databases. and take a backup whenever the system information change
4.Is there any problem, if the log and differential backups occur at same time? is the same way you guys do to avoid occurrence of log and diff backups at same time as I did(by increasing 15 min or something like that)
Well not really a big problem. differential and log backups can till be taken at the same time but what it is if you want to restore to a point of time where differential and log backups coincide then you end up restoring either of them
5. I created a sql agent job to delete old backups files older than 3 days by creating 3 steps in one job using VB script.
step 1: deletes the .bak files in K:\Backup\Full
step 2 : deletes the .bak files in K:\Backup\differential
step 3:deletes the .trn files in K:\Backup\log
is the above method to delete old backup file is a good approach or not?
how you guys delete old backup files?
Well, again it depends on your SLA's what they describe your retention period.
June 2, 2009 at 9:52 am
Thanks to all who responded, or read the topic.
I was mainly curious to see whether anyone would burst out laughing at my backing up the transaction log every half an hour.
You are right that we don't have a true disaster recovery plan for SQL Server. I was trying to come up with something similar to archived redo logs in That Other Database . . . OTH, if our SAN does vaporise, losing a mean 15 minutes of HR data won't be our biggest concern. 😉
@rambilla4: Your strategy seems very comprehensive, but it might complicate the restore. (A case of, which file(s) do I use?) You may wish to write a restore guide, taking into account the time of day and the desired restore time. Not to mention the time of night when someone might have to do a restore . . .
Lastly, I'd just like to say hi to everyone here, and I look forward to learning more, and hopefully reaching a stage where I can contribute.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply