restoring to a marked transaction

Question

restoring to a marked transaction

Simon_L

SSCarpal Tunnel

Points: 4669
More actions
January 29, 2007 at 4:43 am

#172009

using the following syntax in SQL 2005 (win 2003) to restore a log to a marked transaction. Code runs ok but I'm never getting back
to the point in time where id 13 still exists , help !
kind regards
-Si
/*backup and overwrite */
BACKUP DATABASE AdventureWorks
TO DISK = N'C:\SQL Backups\adw.bak'
WITH FORMAT, INIT, NAME = N'AdventureWorks-Full Database Backup'
GO
/* verify data */
restore verifyonly
from disk = 'C:\SQL Backups\adw.bak'
with file = 1
go
-- with MARK to place a marker in the tranny log
BEGIN TRANSACTION CandidateDelete13a
WITH MARK N'Deleting a Job Candidate';
GO
USE AdventureWorks;
GO
DELETE FROM AdventureWorks.HumanResources.JobCandidate
WHERE JobCandidateID = 13;
GO
COMMIT TRANSACTION CandidateDelete13a;
GO
BACKUP LOG AdventureWorks
TO DISK = N'C:\SQL Backups\adw_log.bak'
with format, NAME = N'AdventureWorks-Trans Log Backup'
GO
/*
RESTORE DATABASE AdventureWorks FROM DISK = N'C:\SQL Backups\adw.bak' WITH FILE = 1, RECOVERY, NOUNLOAD, REPLACE,STATS = 10
GO
*/
use master
RESTORE DATABASE AdventureWorks FROM DISK = N'C:\SQL Backups\adw.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE,STATS = 10
GO
use master
RESTORE LOG AdventureWorks FROM DISK = N'C:\SQL Backups\adw_log.bak'
WITH recovery, STOPatMARK = 'CandidateDelete13a'

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

Couldn't really get from BOL if the tran marked was included or excluded in the restore, but...

There is also a STOPBEFOREMARK, that might suggest that STOPATMARK is inclusive, and before is exclusive the tran involved. Maybe try 'before' variant and see what happens?

/Kenneth

Simon_L SSCarpal Tunnel Points: 4669 More actions · Answer 2

Hi

thanks for the reply. I try stopbeforemark and it indeed returns the row. However, if I use this sequence :

/*backup and overwrite */

BACKUP DATABASE AdventureWorks

TO DISK = N'C:\SQL Backups\adw.bak'

WITH FORMAT, INIT, NAME = N'AdventureWorks-Full Database Backup'

GO

-- with MARK to place a marker in the tranny log

BEGIN TRANSACTION CandidateDelete13a

WITH MARK N'Deleting a Job Candidate';

GO

USE AdventureWorks;

GO

DELETE FROM AdventureWorks.HumanResources.JobCandidate

WHERE JobCandidateID = 13;

GO

COMMIT TRANSACTION CandidateDelete13a;

GO

--delete another

DELETE FROM AdventureWorks.HumanResources.JobCandidate

WHERE JobCandidateID = 12

--backup entire log

BACKUP LOG AdventureWorks

TO DISK = N'C:\SQL Backups\adw_log.bak'

with format, NAME = N'AdventureWorks-Trans Log Backup'

GO

/*

RESTORE DATABASE AdventureWorks FROM DISK = N'C:\SQL Backups\adw.bak' WITH FILE = 1, RECOVERY, NOUNLOAD, REPLACE,STATS = 10

GO

*/

use master

RESTORE DATABASE AdventureWorks FROM DISK = N'C:\SQL Backups\adw.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE,STATS = 10

GO

use master

RESTORE LOG AdventureWorks FROM DISK = N'C:\SQL Backups\adw_log.bak'

WITH recovery, STOPbeforeMARK = 'CandidateDelete13a'

GO

I would expect the restore to not recover the row for ID 12 yet it does , am I still missing something crucial here !?

kind regards

-si

MikeTomkies Hall of Fame Points: 3185 More actions · Answer 3

You marked your delete stament transaction and restored up to the point before the mark so ID 12 will be recovered i.e you restored up until the point before the delete took place.

Simon_L SSCarpal Tunnel Points: 4669 More actions · Answer 4

now I feel stupid 🙂

thank you very much for the advice/help

-si