restore log to marked transaction

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

restore log to marked transaction

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-01-29 : 10:22:00

Simon writes "Hi

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

Simon

/*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'
GO"

nr
SQLTeam MVY

12543 Posts

Posted - 2007-01-29 : 11:32:26

Think you need STOPBEFOREMARK

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources