Creating an atomic transaction to backup & re-index DB

[1DMF]

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 total noob with SQL transactions so would appreciate some guidance.

Here is what I currently have...

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;

	[highlight #FCE94F]-- check for success	
	?[/highlight]
	
	-- 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) and hope you can help.

Do I even 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?

Or do I still need some form of expression to check SQLSTATE? Is SQLSTATE only available when working with cursors?

Your input is appreciated.

Regards,
1DMF



"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads