Applying Transaction logs to an O/S backup

Question

Applying Transaction logs to an O/S backup

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 1

HowardW (5/21/2012)
Restores can't be as fast by definition, as you don't have a backwards path to a point in time restore (Oracle has Flashback, a kind of reverse transaction log), so I'm not aware of any vendors that offer more clever a solutions than just overwriting the current state with the previous backup.
Its slightly outdated that SAN/OS level backups are "hot" backups, some are, some aren't. I've used NetApp's stuff on a trial basis and it all works quite nicely, but we opted for native in the end as there was too much of a disconnect between the SAN team and the DBA team for everyone to be comfortable with it.

Thanks, and that's interesting. I have been worried about the Windows Backup stuff. I know it's supposed to work with a pause, but I have read of some problems early on (not sure if they are still there), so I've been worried. The VSS stuff was a little flaky at first.

The restore point makes sense, but does it drop your dbs into "recovering mode" or is it some integration that brings it back to the full (and/or log) time and then online?

I certainly would be nervous about this. Hard enough to get good coordination on a regular day, much less in a crisis.

HowardW One Orange Chip Points: 29109 More actions · Answer 2

Steve Jones - SSC Editor (5/21/2012)
The restore point makes sense, but does it drop your dbs into "recovering mode" or is it some integration that brings it back to the full (and/or log) time and then online?

It literally executes a RESTORE command in SQL with some command options to say it's a snapshot backup and exposes the snapshot via the VDI, so it'll go through the same Restoring/Recovering/Online states as a normal restore.

It doesn't just copy back Data/Log files, then attempt to ONLINE them, letting SQL Server perform a hot recovery.

I haven't done a huge amount of work with the Windows Backup utility (other than noting that it records an entry in the backup history), but I'm not aware of any major bugs.

Having said that, the Windows backup is a really basic backup with few backup and restore options, so I wouldn't suggest replacing native backups with it. Although I can imagine it being very useful in private clouds to just backup the machine in a job lot and not have to worry about backing up the SQL DB's seperately.

jkelly95z@yahoo.com SSChasing Mays Points: 620 More actions · Answer 3

Howard has a much better handle on the tech aspects!

But i would agree with you both that based on testing so far and the lost in translation fun between us and the SAN team, this proof of concept exercise may not go much further!

I love the idea of this, but there are a lot of moving parts and troubleshooting would be difficult when we have involve another team.

Just as an aside, but also within the SAN tech porn... we're testing thin provisioned DB snapshots to make multiple copies of our 2 TB production DB for dev teams/etc...

these ibm v7000's (svc) sure do have alot of gizmos and knobs to twist and turn!

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 4

A while back, I posted a breakdown of SAN snapshot options with SQL Server in response to a different question:

http://www.sqlservercentral.com/Forums/Topic795819-357-2.aspx#bm797256

-Eddie

Eddie Wuerch
MCM: SQL

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 5

Eddie Wuerch (5/21/2012)
A while back, I posted a breakdown of SAN snapshot options with SQL Server in response to a different question:
http://www.sqlservercentral.com/Forums/Topic795819-357-2.aspx#bm797256
-Eddie

Thanks, missed that.

dustin_prescott Grasshopper Points: 10 More actions · Answer 6

There were quite a few factors that scared us away from SAN snaps for our databases. The two main problems were extra space to store the snaps(change data) which is very hard to calculate and the software required a bit of scripting to actually maintain the snaps.

Might be worth doing a little math as well. If you pickup a good compression product you could get 5:1 or 80% compression so you would only be backing up 1TB or 1048576MB. If you have to backup over a 1Gbps network that would be ~120MBps and would complete in about 2.5hrs. With a good differential plan you may only have to do that once a week or less.

If you are considering SAN snaps I would suggest contacting your vendor to see if you can get some lab time with the backup and restore process. It may not be as glamorous as it sounds but it is worth checking into if you have a very strict RTO.