Transaction Recovery

create database test

CREATE TABLE [dbo].[t1](

      [id] [int] NOT NULL,

      [fname] [varchar](20) NULL,

PRIMARY KEY CLUSTERED

(

      [id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

--Take normal full backup

BACKUP DATABASE [test] TO  DISK = N'C:\backup\test.bak'

WITH NOFORMAT, NOINIT,  NAME = N'test-Full Database Backup',

SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

insert into t1 values ('1','mohamed'),('2','faizal'),('3','akba')

--Now we have three reocords inserted in to table t1

--create the store procedure

--Inside in the store procedure we added the input with mark'IC3046'  , SQL Server registers all the tranasaction information inside in the msdb database  so when we restore the log backup , it allow us to stop at before the transaction.

Create the store procedure  and Execute SP

USE test

Create procedure proc1 as

Begin

BEGIN TRY

    BEGIN TRAN t1 with  mark 'IC3046'     

    delete from t1 where id=3

    COMMIT TRAN t1

END TRY

BEGIN CATCH

     

      --Comment out or remove as required

      --TOBE USED when running as a stand alone script

      SELECT

        ERROR_NUMBER() AS ErrorNumber,

        ERROR_MESSAGE() AS ErrorMessage,

        ERROR_LINE() AS ErrorLine,

        ERROR_SEVERITY() AS ErrorSeverity,

        ERROR_STATE() AS ErrorState;

    --Comment out or remove as required

    --TOBE USED when running as a Stored Procedure etc

      DECLARE @ErrorMessage VARCHAR(4000);

      DECLARE @ErrorSeverity INT;

      DECLARE @ErrorState INT;

      SELECT

        @ErrorMessage = ERROR_MESSAGE(),

        @ErrorSeverity = ERROR_SEVERITY(),

        @ErrorState = ERROR_STATE();

            RAISERROR (@ErrorMessage, -- Message text.

               @ErrorSeverity, -- Severity.

               @ErrorState); -- State.

      --Comment out or remove as required

      --To be used if a rollback is needed on any error

    IF (XACT_STATE()) <> 0

    BEGIN

        PRINT 'There has been an Error, see error message. Rolling back transaction.'

        ROLLBACK TRAN t1

    END

      --Comment out or remove as required

      --To be used if wishing to commit on non critical errors

    IF (XACT_STATE()) = -1

    BEGIN

        PRINT 'The transaction is in an  non committable state. Rolling back transaction.'

        ROLLBACK TRAN t1

    END

    IF (XACT_STATE()) = 1

    BEGIN

        PRINT 'The transaction is committable. Committing transaction.'

        COMMIT TRAN   t1

    END 

END CATCH

End

--Execute the store procedure as follows and this store procedure will delete one record from table t1

exec proc1

Recovery validation process

select * from t1

--Now we have two outputs only

--Now we can see the execute delete operation registered in server itself by using the below command as follows

select * from msdb.dbo.logmarkhistory

--if the server has Logshippinng Mechanism,we can use the existing transaction log from the logshipping.

--Now we will restore the fullbackup database in to newly created database TestReplicate as follows

--In this example I used standby mode so it allow us to read the data but we can use norecovery method also

Restore the Full backup with standby/no recoevry

RESTORE DATABASE [TestReplicate] FROM

 DISK = N'C:\backup\test.bak' WITH  FILE = 1,

  MOVE N'test' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestReplicate.mdf',

   MOVE N'test_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestReplicate_1.LDF',

    STANDBY = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\ROLLBACK_UNDO_TestReplicate.BAK',  NOUNLOAD,  STATS = 10

--Now we will run restore the log backup before Delete operation by using mark_name from sdb.dbo.logmarkhistory

 in the stopbeforemark restore command

Restore the T-log before the transaction

use master

RESTORE LOG [TestReplicate]

FROM  DISK = N'C:\backup\test.trn' WITH STOPBEFOREMARK = 't1', STATS = 10

GO

-- if stopatmark is specified , we will have the data that includes transaction.

--if stopbefore is specified , we will have the data before the transaction.

----Now we can see the full records from the table and deleted recorded is the third one.

Recovery operational

use TestReplicate

select * from t1

--copy this data in to original database test

INSERT INTO test.dbo.t1 (id, fname)

SELECT id, fname

FROM testreplicate.dbo.t1

WHERE id = 3

--Query the original database

use test

select * from t1