up vote 2 down vote favorite
1
share [g+] share [fb]

Is backing up a SQL Server via doing a complete backup of the VM image rather than backing up individual databases a bad practise?

I can imagine that backing up the entire image looses the granularity of having several tlogs of each db for each day. Is this generally a bad thing?

Thaks

link|improve this question
30% accept rate
+1 from developer. Keep going! – WebMAOhist Aug 13 '10 at 16:47
feedback

2 Answers

up vote 1 down vote

If you do not back up the db at the db level, you lose the ability to do not only complete DB restores but also:

  • File restores (without restoring the whole db)
  • Page restores
  • Piecemeal restores

In addition you can have a very reasonable backup of SQL server and not take up too much space. With restore points from 15 minutes to 2 hours ago. Taking a snapshot of the image every 15 minutes seems like a ludicrous proposition.

However, IF your database is not very transactional (legacy, read-only), I suppose backing it up by taking a vm snapshot could in theory be okay.

link|improve this answer
1  
Good point about the legacy databases thing - We have a client with Pervasive SQL which is quite old-school in its manner of operation. They've always backed it up by simply copying the flat files. Now we're moving them to a more modern version, we're also implementing the pervasive backup agent which puts the database into a consistent state, then unlocks it when you're finished. At least this way, there's no 'maybe' about the consistency of the data. – Chris Thorpe Aug 13 '10 at 2:16
feedback
up vote 1 down vote

If you do a backup of the VM, then the database won't be in a consistent state when you back it up as the database won't flush its writes to disk because it doesn't know that there is a backup operation in progress.

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

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