Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have some ALTER TABLE statements that I am running. Not all of them work (they are the result of running SQL Data Compare) and I want to group them in some transactions and roll back the statements if something goes wrong.

Is this possible, or is it only data that can be rolled back?

share|improve this question
    
Are you talking about Redgate SQL Compare? One of the synchronization options there is to use transactions IIRC so you could look at the generated script there to see some boiler plate code for this. –  Martin Smith Sep 25 '12 at 16:08
    
Yes I am. I shall take a look at that. –  Piers Karsenbarg Sep 26 '12 at 8:46
1  
It is supported, and relatively safe if kept very simple. Mixing DML and DDL can make things very complicated internally, leading to 'interesting' behaviours e.g. connect.microsoft.com/SQLServer/feedback/details/685800/… and connect.microsoft.com/SQLServer/feedback/details/684732/… –  Paul White Sep 26 '12 at 12:57

3 Answers 3

up vote 1 down vote accepted
   BEGIN TRANSACTION
      BEGIN TRY
        ALTER TABL1...
        ALTER TABLE2
        COMMIT
      END TRY
      BEGIN CATCH
         ROLLBACK
      END CATCH
share|improve this answer

Yes, this is possible.

Most DDL statements can be rolled back in SQL Server (There are a few exceptions such as CREATE DATABASE)

share|improve this answer

You need to look into transactions and exception handling in T-SQL. Check out the last two examples on this page: http://msdn.microsoft.com/en-us/library/ms175976.aspx

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.