March 21, 2014 at 9:11 am
Len.Geoghegan (3/21/2014)
I'm a developer - not qualified as even Junior DBA, though I have to pretend once in a while. 🙂As a developer though, I used to make temporary backups of code files by copying them into folders named CYA. Until a coworker announced that we were running low on disk space so he'd deleted those "See-Ya" folders!
oh good... Now i know what CYA stands for...
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
March 21, 2014 at 10:49 am
I agree with TaylorMade about a backup and restore. Does anyone know the prevalence of using snapshots versus a more traditional environment?
March 21, 2014 at 11:18 am
Its probably low because its an enterprise edition feature. I use snapshots when I'm making a small change, have a rollback plan that doesn't require a restore (think putting back the previous version of a proc), and have log backups running - making them a safety net that is fast to use, but not the only safety net. I can quickly look at the snapshot or even compare to it to make sure I'm back where I want to be. In QA environments its often ok to just do the revert.
March 21, 2014 at 2:42 pm
Carefully designed question. Thanks.
"The revert operation failed" is the essential in the question.
I usually have an issue when other sessions are alive while trying to restore from a snapshot.
Regards,
Igor
Igor Micev,My blog: www.igormicev.com
March 21, 2014 at 6:24 pm
Thanks for the question Andy. Particularly like the setup.
I, personally, just dragged my lunch back into the office so Junior could stop freaking out and read me the blinking error coherently by standing to the side and letting me SEE it... 😀
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 22, 2014 at 4:43 am
arthurolcot (3/20/2014)
Great quality question.. Content (and humour!) aside, the question itself actually made you think and apply logic to come up with the most likely correct answer (which we all have to do when troubleshooting) and not just know hard facts about a particular feature. One of the best qotd for a long while... Thanks..
+ 1, I liked the funny scenario :-), I even answered it before having my lunch. 😉
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
March 22, 2014 at 5:05 am
Andy Warren (3/19/2014)
Comments posted to this topic are about the item <A HREF="/questions/snapshot/107576/">Alas Poor Snapshot, I Knew Him Well</A>
because you didn`t mention the exact error msg; this option: "There are other people connected to the database" could be true.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
March 22, 2014 at 5:10 am
17% answered it should be: "Revert Database". Wow, I never heard about this option before!
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
March 22, 2014 at 10:11 pm
I love Chipolte, too bad there isn't one out here in Afghanistan. I got to finish my lunch.
March 26, 2014 at 11:54 pm
nice question. thanks for sharing
March 30, 2014 at 5:02 pm
I agree with others, excellent question and funny to boot.
I did not get to eat lunch, but that's because it's 1 am here. But I did not have to leave home, I could leave Junior to complete his graveyard shift.
Regarding this comment:
TaylorMade (3/20/2014)
Nice question, and able to finish lunch,however . . .
I would have asked Junior why he didn't BACKUP the database for this purpose. BOL - "Reverting is not intended for media recovery. . A database snapshot is an incomplete copy of the database files, so if either the database or the database snapshot is corrupted, reverting from a snapshot is likely to be impossible. Furthermore, even when it is possible, reverting in the event of corruption is unlikely to correct the problem. Therefore, taking regular backups and testing your restore plan are essential to protect a database." Snapshots primary purpose are for reporting needs.
Unless I misunderstood the question, Junior is not trying to use the snapshot to restore from corruption (if he was, I would now be in my car heading to the data center instead of spending time on SQLServerCentral); he made the snapshot prior to a deployment that may need to be rolled back.
For that scenarion, database snapshots are (in my opinion) an excellent option.
March 31, 2014 at 7:43 am
The problem with restoring from a snapshot or transaction log backup is that you may end up "throwing out the baby with the bath water", meaning that legitimate changes made by users to the database in the window between create snapshot and restore will be lost. What we typically do for a routine database deployment is require the developer to supply a "rollback script" with the change order ticket along with instructions on exactly when and how to use it. So, a script that deploys modifications to a handful of stored procedures would also include a rollback script that creates the previous version.
Non-routine deployment, for example scripts that update or delete large volumes of data or upgrade 3rd party ISV databases, are more problematic and snapshot may be an appropriate solution for that scenario, but Junior would not be manning the server room by himself that day. Senior would be on site and supervising it step by step.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 31, 2014 at 8:35 am
Eric M Russell (3/31/2014)
The problem with restoring from a snapshot or transaction log backup is that you may end up "throwing out the baby with the bath water", meaning that legitimate changes made by users to the database in the window between create snapshot and restore will be lost. What we typically do for a routine database deployment is require the developer to supply a "rollback script" with the change order ticket along with instructions on exactly when and how to use it. So, a script that deploys modifications to a handful of stored procedures would also include a rollback script that creates the previous version.Non-routine deployment, for example scripts that update or delete large volumes of data or upgrade 3rd party ISV databases, are more problematic and snapshot may be an appropriate solution for that scenario, but Junior would not be manning the server room by himself that day. Senior would be on site and supervising it step by step.
Excellent addition. I wanted to include this side note myself, but I apparently forgot.
If your rollback scenario after a change is "restore from backup" (meaning you are willing to lose changes made between deployment and decision to roll back), then revert to snapshot is just as good, but quicker.
If you cannot afford to lose changes, then neither restore from backup nor revert to snapsho is an option; you'll have to have a (tested!) rollback script,
Viewing 13 posts - 31 through 42 (of 42 total)
You must be logged in to reply to this topic. Login to reply