1

I've read dozens of articles about why this is a bad idea. The arguments against using a detached database as a backup are legion. However, I still think that in my case, it makes good sense. Realizing that those are often the words of admins who know too little, I'd like to put my strategy to the good folks here to see if someone can tell me why what I'm doing would be more properly done with the internal backup mechanism.

Let me address some of the common issues.

  1. My database files are almost full, so the fact that backups only copy utilized pages isn't relevant to me;
  2. I'm not using any filestream storage;
  3. my application can handle small bursts of downtime when I detach and make a copy;
  4. The various file permission nightmares that accompany detaching databases have already been solved.

The total size of the DB is about 1TB. My main reason for detaching and attaching instead of using backups is performance. In my testing, it is significantly faster to detach the database, make a copy of the underlying files, and attach the original files again than it is to perform a backup. During recovery, it is also significantly faster to attach files (even if I have to copy them to the proper location first) than it is to restore them.

I can get around the backup performance problem by using something other than a full backup, but that doesn't help when it comes to restoration. In the event of a disaster, I need to be back up and running quickly. My application can handle small amounts of downtime periodically, but any large stretch of downtime is disastrous. Restoring 1TB of database takes longer than the business wishes to tolerate for the application.

The last item I've often read is that detaching a database comes with some risk. Just like we ought to perform test restores of backups, I immediately attach any copied MDF/LDF/NDF files to a disaster recovery SQL Server to make sure the copy is good. I suppose my exposure here is that I could detach the database and break something such that the original DB files can no longer be re-attached. Honestly, I've never seen this, so if this is really a possibility, I feel that it's quite remote. I'm doing this nightly, so I'd lose a day's worth of reporting data in this (unlikely?) scenario.

Am I missing anything else?

Dave Robinson
  • 113
  • 1
  • 7
  • 2
    The business isn't ok with the database taking awhile to restore, in the event of a disaster, but they *are* ok with downtime every day(?) so that a copy of the database can be made? That seems like a very odd mentality to me, – Thom A Sep 04 '18 at 15:21
  • 2
    And is business ok with losing a whole day of data if the database goes down? You would lose everything from the last time you detached your database. You mention this in your question but don't really address it. As you have stated, using detached databases is **not** a good backup strategy. What you haven't posted is your arguments about why in your case it is ok when it is not ok for nearly everybody else. – Sean Lange Sep 04 '18 at 15:25
  • 2
    Use differential backups? You take a FULL backup weekly and a differential backup nightly. Then restore from your differential. – mvisser Sep 04 '18 at 15:28
  • @Larnu the application uses the data for reporting purposes. The application remains up even when reporting data is inaccessible. The application can also store unsaved reporting data for a limited time until the database is available again. So yes, limited downtime is acceptable because it means reporting data won't be lost, but it will in inaccessible for brief periods. Long stretches of downtime are bad because customers want their reports and can wait an hour, but not 8 hours. – Dave Robinson Sep 04 '18 at 17:07
  • @SeanLange I can only address the issues as I've understood them. If I were doing full arguments in both directions I would have no need to post here :) I'm looking for input, so just arguing that it's "not good" isn't helping me. Your point about losing the day of data is a good one that I didn't address. This is data is used for historical reporting. In a disaster, losing a day of reporting data is seen as better than not providing our customers with access to any reports for entire duration of a restoration, at least for our business. Other DBs we have aren't that way, but this one is. – Dave Robinson Sep 04 '18 at 17:13
  • @mvisser I might be missing something in what you're saying, but in a disaster, I'd need to restore the entire DB and then the differential backup as well. It's the performance of that restore that I'm trying to eliminate. Perhaps I've misunderstood what you're suggesting though. – Dave Robinson Sep 04 '18 at 17:15
  • 1
    So, you *are* saying that losing a day's worth of data is acceptable? – Thom A Sep 04 '18 at 17:52
  • @Larnu: given the choice between losing a day of data and having a multi-hour restore process where *all* data is inaccessible, the business has chosen that uptime is more important than any single day of reporting data. I don't want to litigate the business decisions here. I suspect that sitting in our shoes might make for more clarity. Right now though, I'm wondering about pitfalls of using detaching as a backup strategy. The data loss window is an important point. Are there others? – Dave Robinson Sep 04 '18 at 18:03
  • 1
    I didn't bother providing you with details about why it is not a good idea because you said you have researched it. "I've read dozens of articles about why this is a bad idea. The arguments against using a detached database as a backup are legion.". It almost seems that you want somebody to say that despite dozens of articles declaring this as a bad approach that it really is ok. We can't make that call for you. You and the business have to decide what works for you. It sounds like you found the path you are going to take and want to find a way to justify it. – Sean Lange Sep 04 '18 at 18:36
  • 1
    I think @SeanLange hits the nail on the head here. The comments/answers you're going to get here are going to align with what you've already read; that detaching and copying a database is not a good backup strategy. The reason for that is for exactly those reasons that you've read online already. People's minds aren't going to suddenly change because you think it's better for your environment. Maybe it aligns with the ideas of your business, but (from experience) those people aren't IT people, so don't understand what they're saying and will moan when the problems you said will happen do. – Thom A Sep 04 '18 at 19:07
  • @Larnu, thanks for the replies. I wouldn't exactly say that I've already decided and am attempting to justify. I've come up with an answer, but I'm happy to alter it if the reasons make sense. I've done my reading, which I think is wise. I understand my business requirements. I'm looking for any reasons out there that I might have missed. If I haven't missed anything, then great. The drawbacks to backups are performance and the data loss window + what I've already stated are the benefits. Put your last comment in an answer and I'll mark it correct. – Dave Robinson Sep 04 '18 at 20:18
  • Have you considered using replication? – Branko Dimitrijevic Sep 27 '18 at 10:33

1 Answers1

3

With this approach you are choosing to prioritise reduced recovery time over recovery point (data lost when you restore). That's a reasonable trade off that everyone has to make to some degree.

Your database will be offline every time you detach and re-attach to do a backup whereas the BACKUP command requires no downtime at all. It seems unusual to be more concerned about downtime during occasional restores than every day during backups but I guess that depends on the actual timing of the backups and the hypothetical timing of the restores.

You haven't mentioned transaction log backups. For most people log backups give the optimal recovery time and recovery point. Have you considered relatively infrequent log backups as an alternative strategy?

If recovery time is such a priority then you'll presumably need to have warm standby hardware that you can restore to. If you do have a standby server then you could use standard backup and restore to minimise downtime much more than you can by using the detach method: just restore your database to the standby server every day. You could even log-ship your transaction log backups.

As with any backup and restore strategy you should test it out. Do a trial run and see just how much losing a day's work actually costs. Maybe it's easy to underestimate that cost.

When you detach and re-attach make sure you include the log file(s). Keep an offline copy in addition to the copy that you attach. If an attach happens to fail (say because one of the files has moved) then in some cases the files may get "touched" so that they cannot easily be attached again. My advice would be never to try attaching from your only copy of the database files.

You will still need to use BACKUP to backup the Master database (and model/msdb if required).

nvogel
  • 24,981
  • 1
  • 44
  • 82