Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-02-07 : 09:27:00
|
| David writes "I have a 300GB datawarehouse database on SQL server 2000. I'm planning to implement a filegroup backup/restore job so the recovery time can be reduced. I'm running into some puzzles that I hope you guys can share some of your experience with me. Problem 1: It doesn't seem SQL server letting you creating a new database and restoring it from a filegroup ( full ) backup without having a full backup copy. Is there a way to do this ? In case of disaster recovery, I find the filegroup backup/restore plan very weak. Problem 2: SQL server doesn't allow multiple filegroup backups to run concurrently. To do a full filegroup backup takes as long as a full backup. So you really gain nothing in backup time. Between full/differential/ filegroup, I like the idea of the filegroup backup strategy. However, it has so many limitations and doesn't really work the way you think it would. Please correct me if the problems above are not true. I would like to know if I'm missing anything. If you have a good filegroup backup strategy, also please share with me. Thanks in advance. David." |
|
|
JamesH
Posting Yak Master
149 Posts |
Posted - 2003-02-07 : 12:29:14
|
| I'm not sure what else you can do other than throw hardware at this issue. There are several options that you can take, none of which are cheap. These are 2 of the things that we have done on databases of 150GB+ and other critical systems.1) spend the money on a warm stand-by plan. Yes, there is log shipping out there but we have opted to do our own thing which has worked very well for us. It requires an additional server and I would strongly suggest installing dual NICS on their own VLANS so the backups/restores, etc can run on their own. (Also for DR)Max downtime: around 10 minutes or so, which is usually the Development team redirecting .ini files. The stand-by server is available after the backup transaction with no_truncate is restored, or if that fails we lose 5 minutes worth of data by executing a RESTORE DATABASE <NAME> with RECOVERY.2) Buy a NAS or SAN Device, there are many out there that perform at different levels and I would definately have them ship you a box to do a proof of concept on the box BEFORE paying a dime. We tested several and ended up getting the NetApp F880 with 3 shelves (3TB expandable to 9TB). What you get with this is the ability to do snapshots (Even schedule them through SQL Agent) that at the Tree level (for Multiple databases) that can be restored in less than 5 minutes regardless of size. NetApp's newest stuff will be using the VDI.api provided by Microsoft which you can find on their site and even download free. It also does a backup (Snapshot) in literally seconds. (Using Split Mirroring). We've been very happy with it and have started planning for more in the future for DW and even more OLTP systems. BTW, performance has also been great.Max downtime: Less than 5 minutes.Enough of my rambling. Other than these options you are really dependant on the SQLEngine, and your hardware. Which has always been the case. Hope this helps.JamesH. |
 |
|
|
|
|
|
|
|
Filegroup Backup/ Restore.