SSMS Change Scripts & Transaction

Question

Hi all!

I'm not an expert on SQL Server/T-SQL/SMSS so please bear with me :)

I use the 'Generate change script' feature of SMSS to track database schema changes. This generates files like the following:

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.NAW ADD
 GeslachtContactPersoon nvarchar(2) NULL
GO
ALTER TABLE dbo.NAW SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

Now, lately I executed such a script on an existing database and it failed, which resulted in a table being dropped. I was quite surprised since I thought all statements being contained in an BEGIN TRANSACTION COMMIT block would ensure the original database would be restored whenever failure occurred. However, it seems you need to manually check every statement for errors and then execute ROLLBACK yourself (hey, I said I was new to SQL Server :)), which seems to be quite a tedious job. Now I have 2 questions:

• What's the use of the BEGIN TRANSACTION COMMIT without a ROLLBACK somewhere?
• Is there anyway to let SMSS generate the the error handling and corresponding rollback action?

Thanks!

-Freek