| Author |
Topic |
|
dewacorp.alliances
452 Posts |
 Posted - 2006-10-18 : 18:58:37
|
| Hi thereNeed to better understanding regarding the backup and restore.Schenario 1:1) SQL native Transaction Log: 8AM to 11PM2) SQL native Full Backup: 2AMIf you do a full backup ... all the previous transaction will be obsolute ... wouldn't? Schenario 2:1) SQL native Transaction Log: 18/10/2006 - 8AM to 11PM - successfull2) SQL native Full Backup: 19/10/2006 - 2AM - successfull3) Have another SQL native full backup (for unknown reason) at 19/10/2006 - 7AM successfull 4) SQL native Transaction Log: 19/10/2006 - 8AM to 11PM - successfull5) Server crash at 19/10/2006 10AMTo restore, I need to get a full backup of point no 3 and restore the transaction log at poin no 4. Which that we normally do. But let say if it's using point no 2 first and then point no 4 .. will it work? What is a potential issue here if implement this.Schenario 3:1) Veritas NetBackup Transaction Log: 18/10/2006 - 8AM to 11PM - successfull2) Veritas NetBackup Full Backup: 19/10/2006 - 2AM - successfull3) SQL native full backup at 19/10/2006 - 7AM successfull (for secondary backup)4) Veritas NetBackup Transaction Log: 19/10/2006 - 8AM to 11PM - successfull5) Server crash at 19/10/2006 10AMDo i need to get backup file from point 3 and then point 4? The thing is in the Veritas NetBackup .. their GUI is only showing point no 2 only as full and point 4 as transaction logs. That what is bothering me.I am appreciated your feedback .. btw the Veritas NetBackup is SQL client type of backup (hot backup) NOT cold backup.Thanks |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-10-18 : 19:15:32
|
| Scenario1: No. You can still use them for a point in time restore.Scenario2: You need some full backup and the entire transaction log chain up to the crash. So yes you can use 3 and 4, but there are other options too if 3 or 4 is corrupt.Scenario3: I'm not sure how the Veritas stuff works. I can't imagine why people use it for their backups.Tara Kizer |
 |
|
|
dewacorp.alliances
452 Posts |
Posted - 2006-10-18 : 23:46:35
|
| Hi TaraSchenario 2:If the number 3 is corrupt, and you want to use 2 and then 4 ... would it work? Would the transaction gets up to date?Schenario 3:I do understand the that not all DBAs fan of this NetBackup stuff ... me too. But in our case we have implemented this already as primary backup. It works the same as a native SQL backup but (it passes the SQL command to SQL itself and run a backup to SAN disc and eventually goes to tape).So back to my question for schenario 3, would it doable similar to schenario 2. Thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-10-19 : 00:05:26
|
| As long as there aren't any tlog backups in between 2 and 3, then yes you can do 2 then 4. I can't answer your scenario3 question.Tara Kizer |
|
|
|
dewacorp.alliances
452 Posts |
Posted - 2006-10-19 : 01:16:58
|
quote:
As long as there aren't any tlog backups in between 2 and 3, then yes you can do 2 then 4.
There is NO transaction backups in between 2 and 3. So it's doable to use 2 and 4 and that what I need to know. I assume that will be the same concept for schenario 3. Any one can confirm this? Thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-10-19 : 11:15:52
|
| How about testing scenario3? It's fairly easy to do.Tara Kizer |
|
|
|
dewacorp.alliances
452 Posts |
Posted - 2006-10-19 : 11:33:50
|
| I think that is what I am going to do which testing to find out the answer. |
|
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2006-10-19 : 12:55:18
|
| think of it like this,BU = Full back upT = transaction logBU1,T1,T2,T3,BU2,T4,T5,BUVeritas, T6, T7to get to the end of T7 you can go.BU1,T1,T2,T3,T4,T5,T6,T7ORBU2,T4,T5,T6,T7ORBUVeritas,T6,T7 (it works but it's kind of salad dressing)This is a scenerio (maybe common) where the DBA and the IT guys have different priorties, Or the IT guys buy a wizard based solution and they don't really understand what they are doing.A DBA wants full atomic restore points, but the IT department wants full point and click system restore functoionality for their server.When MSSQL is installed on that Server the Backup package can only provide full system restore if you also utilize that backup software's SQL backup client.It becomes a matter of trust or choice, a certain level of complexity in third party bloatware when simple maintainable routines of data protection are available in the native RDBMS. Keep an eye on how the Veritas SQL client gets configured, Once they start wanting to include Tlogs in that configuration, yikes!!. (Fight for differentials used by the IT guys if need be.)You can explain some scenerios where the Veritas solution is flawed.Building burns down, How and you are going to restore a full systemfresh out of the box server, when the old one was 6 years old, good luck finding one with the exact specs. That kind of hardware/driverlegacy is gonna hurt one day. The IT guys often understand the hardware 'purity'issue clearly.You operating outside the Veritas world,could have the databases running anywherein about an hour after a server rename/replace.There is a counter arguement, Why dosen't the DBA learn VERITAS?disaster test VERITAS with the IT department. I think the main reason for me is it'snot something I come in contact with, they move licences around and have sporadicupgrades and all kind of junk. It's a huge web of backup strategy that is just not worth it. (<- as in getting involved with, or conterproductive or resource scarce or unskilled, "backups are always worth it")"it's definitely useless and maybe harmful". |
|
|
|
dewacorp.alliances
452 Posts |
Posted - 2006-10-19 : 19:50:07
|
| You are right. Personally i rather use native SQL backup and restore and pointing the backup to backup server and do the cold backup to tape using veritas. But in our system , this Veritas NetBackup (SQL client type of thing) taken over the SQL Native Backup (replacing FULL and Transaction) but I did manage to pursuade to put another native SQL backup to set as a secondary backup just in case the veritas one is failed.But now has something really bothering me which I explained in Schnario 3. |
|
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2006-10-19 : 21:17:19
|
I reread your Scenerio 3 and I think it's basically the inverse of what I exampled. And yeah you should be bothered.  I think the catch here is going to be specifically getting the tlogs performed by the Veritas layer scipted or retrived from tape as I believe these packages sometimes use the logical backup device rather than backup to file. Anyways that's how arcserve does it, expect non descript naming, like GUID's which infact may be buried in the native database format of veritas. Hopefully they configured Veritas to store the backup reference to use SQL which if you think about the absurdity of the situation is kind of funny.Technically I don't think there is any limitation to your Scenerio 3, even though Veritas dosen't show the native SQL backup, because the SQL BACKUP is not part of the virtual device Veritas knows. The systems tables will though, and the LSN number columns in the system tables may help put this in context. Tara is dead on with try it though, it's worth the time and effort and addresses your topic.Then go to BOL and read the logic device topics, they are easier to put in context while doing, then the future. http://www.microsoft.com/downloads/details.aspx?familyid=416f8a51-65a3-4e8e-a4c8-adfe15e850fc&displaylang=enJust thought of something funny, undock the hardrives out of the server and hide them, then see if the veritas package does indeed work!!!"it's definitely useless and maybe harmful". |
|
|
|
dewacorp.alliances
452 Posts |
Posted - 2006-10-22 : 20:58:17
|
quote:
Originally posted by tkizer
As long as there aren't any tlog backups in between 2 and 3, then yes you can do 2 then 4. Tara Kizer
quote:
From NetBackup Engineer: For the native full SQL backup (point 3) is this truncating the transaction logs created since the last full backup at via Netbackup (Point 2)"?
So looking at these 2 comments, just want to confirm regarding this, if there is no tlogs backups in between 2 and 3 OR "no truncating the transaction logs", I should be able to do 2 then 4 ... correct?Thanks |
|
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2006-10-23 : 12:55:57
|
| It's a pretty specific thing you are asking and I'd have to recommend, don't try and short cut this to a known "confirmed" scenerio. Just try the various scenerios on a tiny demo (local or dev server) database.Use EM and build a maintenance plan and pretend that is the VERITAS client (the bad guy). Then run your own set of backup and restores from QA (the good guy). You can then see the interaction and the degree of parallelism that exists between Tlog backups and Database backups. Get that pattern fully understood, then consider the Veritas layer and interpret how it does things. And finally create a demo(admin) database in the production environment that you can test on successive days or weeks within the "live" routines and confirm recovery.One trick or hint is to know that EM is often out of sync with the true state of the database so you may have to close it and open it frequently or it is very easy to get fooled."it's definitely useless and maybe harmful". |
|
|
|
|
Better Understanding with Backup and Restore