Well, once that's done as far as I understand it there is no way back, agreement or not, the clock ticks and after 2 years are up the separation is a fact. If people are hoping for a collapse of the Brexit after that point they are delusional, the only way this would reverse after that is by a new application, and I really don't see that ending up with the UK being in a better position after such a process than they have today. All kinds of exceptions were made to keep the UK in the EU, those are extremely unlikely to be granted a second time, especially not if the UK falls apart and Scotland ends up remaining.
The British prime minister is now making a strong play to pull Scotland out of the EU against their wishes, we'll likely see a race between the 'brexit' negotiators on the UK side and the Scottish 'remain' camp who will try to stay in without first exiting.
This is understandable from all points of view, the British government would rather like to negotiate from a slightly stronger position and the for the Scots because they were told they had to renegotiate their access to the single market if they seceded from the UK. Now that the British wish to exit that point has become moot which will most likely swing the referendum towards the Scots exiting the union. They probably feel quite cheated in Scotland.