| Author |
Topic |
|
gregoryagu
Yak Posting Veteran
80 Posts |
 Posted - 2011-03-18 : 14:09:00
|
| OK, so I have a Windows 2008 R2 server running Sql Server 2008 R2. The Sql server is going to power a website that will have a lot of data traffic.What is the current recommended backup strategy?Initially, I thought DPM 2010 would be the way to go. However, have had a lot of problems installing it as it requires a domain. So I installed the domain, but then you have to do a lot of manual stuff to make it work as it's on a domain controller and I still haven't gotten it to work.But it made me think, what is the standard backup strategy for SQL Server being used on a web server? It's going to hold a lot of data, and while it's not banking, I don't want to be losing customer data.DPM supposedly is good enough to not lose any transactions at all.Windows Server Backup I have worked with, and it works very well for backing up the OS, but I don't know about using it to back up SQL Server. I suppose I could set it to back up every 15 minutes, but I don't know if that is best practice or not. Again, I don't know if 15 minutes of lost data is acceptable.I do already have a RAID 60 in place on the hardware side which is as good as it gets, but I am told that RAID is not a replacement for backups.Greg |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-03-18 : 14:12:49
|
| Use native SQL Server backups.You probably want nightly full backups and transaction log backups every 10 or 15 minutes. |
 |
|
|
gregoryagu
Yak Posting Veteran
80 Posts |
Posted - 2011-03-18 : 14:25:51
|
| Nothing. Am new to doing SQL Server backups and thought that MS was recommending DPM as the best way to do SQL Server backups, but I wasn't sure if the community was actually using this or not.Will check out the native backups further.Greg |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
gregoryagu
Yak Posting Veteran
80 Posts |
Posted - 2011-03-18 : 14:37:53
|
| Thanks Tara. Greg |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ptodd
Starting Member
1 Post |
Posted - 2011-04-16 : 18:41:10
|
Tara,I'm not sure what you mean by "DPM is rarely used for SQL backups"?We use DPM 2010 for about 35 SQL servers and it works great. It took a while for everyone to get confortable not performing manual or scripted SQL backups.It saves so much time/performance as compared to traditional backups. Why do you think Microsoft developed it to protect their products? I really like the fact that DPM allows us to "protect" the DB and roll Tlogs into that backup every 15 minutes. That way, if we have a failure, we only have to play back up to 15 minutes worth of logs.I'm definately not a SQL guy, but I'm really trying hard to make sure we're protecting our data in the most efficient manner.PTODD |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-04-16 : 20:23:18
|
quote:
That way, if we have a failure, we only have to play back up to 15 minutes worth of logs.
Unfortunately 15 minutes is too long a gap for a lot of industries to endure. Also, to my knowledge DPM cannot restore to a point in time between those intervals. So if you had a specific problem (dropping a table) that occurred just after transactions you wish to preserve, DPM could not restore to the point just before it. This is easily overcome using standard transaction log backups.DPM is really a file backup utility, and would be fine to back up the backup files...just not the database itself. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-04-16 : 20:27:29
|
quote:
Originally posted by ptodd
Tara,I'm not sure what you mean by "DPM is rarely used for SQL backups"?
I say that because it's true. It is rarely used for SQL Server backups. Just because you guys are using it, doesn't mean it's commonly used. We recently evaluated the product, and it is just not ready to be used in an Enterprise environment. One problem with it is that you can't change the names when you restore to another server. That's a problem if the database already exists there. Another problem is you can't get below 15 minutes of backups as Rob pointed out.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
|
DPM 2010 vs Windows Server Backup