Hi,

I need to create a maintenance SP that will perform a backup of a database and then re-index all the tables held within it.

I would like this to be an atomic transaction so that the re-index doesn't take place unless the backup was successful.

I'm a little green with SQL transactions so would appreciate some guidance.

Here is what I currently have...

Code:
CODE
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: 1DMF
-- Create date: 26/09/2013
-- Description:	Scheduled task SP to backup & re-index MS SQL Express
-- =============================================
CREATE PROCEDURE MyDB_Maintenance
	
AS
BEGIN TRANSACTION -- start ATOMIC

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	-- Set isolation level serialised to ensure transaction runs alone
	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

	-- Start Backup
	BACKUP DATABASE [MyDB] 
	TO DISK = N'C:\SQL Backup\MyDB.bak'
	WITH NOFORMAT, INIT,  NAME = N'MyDB Backup', 
	SKIP, STATS = 10;

	check for success	
	?
	
	-- re-index database
	EXEC sp_MSforeachtable @command1="print '?'", @command2="ALTER INDEX ALL ON ? REBUILD WITH (ONLINE=OFF)";

COMMIT TRANSACTION
GO
You will see I have highlighted a place holder where I'm not sure what I need to do (if anything) to check for backup success and hope you can help.

Do I need to check that the backup was successful seeing as I have set the SP to begin as a 'transaction' (atomic) , this should mean if the backup fails, the transaction as a whole fails doesn't it and so the re-index won't run?

Perhaps I need to check SQLSTATE? Is SQLSTATE available for this purpose? Is it @@Error instead?

Your input is appreciated.

Regards,
1DMF