October 1st, 2013, 04:34 AM
Creating an atomic transaction to backup & re-index DB
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...
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.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
-- Author: 1DMF
-- Create date: 26/09/2013
-- Description: Scheduled task SP to backup & re-index MS SQL Express
CREATE PROCEDURE MyDB_Maintenance
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)";
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.