One of our teams is developing a database that will be somewhat large (~500GB) and grow from there (I know 500 Gigs may seem small to many of you, but it will be one of the larger databases in our shop). One of the issues they are grappling with is backing up and restoring the database. Basically, the database will have several "data" tables and one table used for storing images / documents. We need to accomplish the following:

  • Be able to quickly backup and restore only the data tables (sans images) to our test server for debugging and testing purposes.
  • In the event of a catastrophic database failure, restore the data tables only to get most of the application up and running ASAP. Then, restore the images table when possible.
  • Backup the database within the allotted nightly time window (a few hours). My questions are:

Is it possible to accomplish the first two goals while still having the images stored in the same database? If so, would we use filegroups, filestream, or something else? How do other shops backup their databases in a reasonable time window while maintaining high availability? Do you replicate to a second server and backup from there?

  • 1
    possible duplicate of [SQL Server 2005 / 2008 - multiple files / filegroups - how many? Why? ](serverfault.com/questions/29097/…) – GregD Dec 21 '10 at 16:41
  • 1
    What does your management say? What is the dollar loss involved with a "catastrophic database failure"? If the dollar loss cannot be stated then I submit that some further deliberation is in order. A risk assessment exercise might help your management understand the situation more clearly and might be a justification for a workable budget. – jl. Dec 21 '10 at 17:03
  • Well, this project isn't my project. The team just came to me for advice, and according to them, having the database offline for even one day to do a full restore is unacceptable. I'm not inclined to pursue this further with management as it is not my project, but I would like to present them with as much information and as many facts as possible to allow them to make a decision. – DCNYAM Dec 21 '10 at 17:20

Pretty simple: DO NOT PLAN TO RESTORE.

In the event of a catastrophic database failure, restore the data tables only to get most of the application up and running ASAP.

Really? Your definition of catastrophy is not mine and the rest of the worlds.

In case of a datastrophy you want to get back up asap, but asap may require rebuilding the data center due to fire. THIS is a catastrophy.

For server failures etc. - do not plan to use backups. Use replication, log file shipping to keep a second server (on a separate SAN) hot and read to take over within a defined short tmieframe. I know companies shipping log files every 10 minutes.

Pretty much your only chance. Move catastrophy up to something that is a REAL desaster, not a raid / san failure. Something where your questio nis not "how fast can I restore" but "how fast do I get new hardware".

Restores for dev etc. are less time critical.

|improve this answer|||||
  • 1
    +1 on replication. Once you get past a certain point, recovering from an offline cold copy of the data just isn't time or resource feasible. – sysadmin1138 Dec 21 '10 at 16:50
  • Not planning for a restore really isn't an option. Replication would be nice, but we lack the hardware and the budget to purchase additional hardware. In addition, the backup solution implemented by our network / systems group is to backup the databases to .bak files, then backup those .bak files to tape. I have no control over this process. Therefore, we need to find a solution involving backups / restores. I'm sorry, but a catastrophic database failure is any failure of the database that is not recoverable. This may or may not be a catastrophe depending on the value of the data. – DCNYAM Dec 21 '10 at 16:55
  • "Catastrophic database failure" for some can mean that the coffee isn't made when they get into work. – jl. Dec 21 '10 at 16:57
  • 1
    Well, when your wishes hit reality, your wishes have to go - not reality. Restoring large backups is NOT FEASIBLE§ without significant outtime. Point. Everyone I know works with redundant hardware. Dont have that - not SQL Server's problem. Basically you plan to avoid a restore for anything but a REAL catastrophy, and in this case you normally have a lot of leaway. New data centers dont get build overnight. Keep a hot copy ready. Point. – TomTom Dec 21 '10 at 17:03
  • I tried to convince them that being offline for a day or so would be required, but was told that was unacceptable. If there is no alternative, then that is that and that is what I will present to the team. Unfortunately (or maybe fortunately), this is not my project. I'm just an adviser and as such have little authority in the ultimate decision. I just want to present options and facts. Thank you for your input. – DCNYAM Dec 21 '10 at 17:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.