Rollback strategies

Question

Rollback strategies

Loundy

SSCertifiable

Points: 6080
More actions
February 17, 2012 at 7:33 am

#250934

Hi All,
I'm trying to work out what's the best way to roll back specific objects. For my company we don't have any form of automation so we rely heavily on tsql scripts or scripting current objects in ssms to use as roll backs.
I have a strategy in place for all objects except SSIS packages.
My question is what kind of roll back strategies are available for rolling back an SSIS package. So far I can think of Exporting package or drop and recreate the package.
Thoughts?
Cheers,
[font="Times New Roman"]There's no kill switch on awesome![/font]

Viewing 7 posts - 1 through 6 (of 6 total)

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

I would use version control software like TFS giving each change of the package a version number allowing you to get a specific version out of source control

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

Loundy (2/17/2012)
Hi All,
I'm trying to work out what's the best way to roll back specific objects. For my company we don't have any form of automation so we rely heavily on tsql scripts or scripting current objects in ssms to use as roll backs...

1. BACKUP DATABASE

2. In case of roll back - RESTORE DATABASE

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Steve Jones - SSC Editor SSC Guru Points: 736188 More actions · Answer 3

I'd export the packages, keep them in VCS (free one like Subversion). Then deploy the new one. If it is not correct, or there is an issue, delete it, import the old one.

Loundy SSCertifiable Points: 6080 More actions · Answer 4

Thanks for the replies, I've been thinking along the lines of exporting. Maybe creating a backup folder in msdb to store the old packages then if we need to restore them move them back to the root of msdb...

[font="Times New Roman"]There's no kill switch on awesome![/font]

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 5

Loundy (2/17/2012)
Thanks for the replies, I've been thinking along the lines of exporting. Maybe creating a backup folder in msdb to store the old packages then if we need to restore them move them back to the root of msdb...

Kind of handmade version control? it may work, yes.

Other alternatives are using a real version control software or setting in stone a rule that forces DBA to extract and save the previous version of any piece of code that is targetted for changes.

Just as a note to show how old I am, I personally prefer to use the expression "roll back" for transactions while using the expression "back out changes" for code.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 6

Since multiple packages can use the same config file and since there could be external dependencies on batch files or xml files there is no once one solution on how to rollback a package. It depends on how the package is designed and what dependencies it has. Msdb is a good place to store and backup as long as all your packages are stand alone.

Jayanth Kurup[/url]