Backing up two tables using SQL transactions
First of all, you don't check @@ERROR after deleting which is not a good idea.

Second, you don't need to name transactions, but you can if you want to. Although, I'd recommend to avoid the same naming as you use for tables. For example, Table1_Backup is preferable in this case.

Let's make it more readable and compact. We have two ways.

1. XACT_ABORT

If you SET XACT_ABORT ON, transaction will automatically roll back on every run-time error.

SET XACT_ABORT ON

BEGIN TRANSACTION
    INSERT INTO [Table1_BU] SELECT * FROM [Table1] ORDER BY ID
    DELETE FROM Table1
COMMIT TRANSACTION

BEGIN TRANSACTION
    INSERT INTO [Table2_BU] SELECT * FROM [Table2] ORDER BY ID
    DELETE FROM Table2
COMMIT TRANSACTION

SET XACT_ABORT OFF


2. TRY ... CATCH

Second approach is to get it under control.

BEGIN TRY
    BEGIN TRANSACTION
        INSERT INTO [Table1_BU] SELECT * FROM [Table1] ORDER BY ID
        DELETE FROM Table1
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION
END CATCH

-- and, of course, the very same for the second table