Using the WITH MARK clause in a transaction can save time and tears. Instead of restoring backups to a point in time, WITH MARK can be used to name transactions and can then be used to roll forward to the transaction itself.
Make sure the AdventureWorks database recovery model is set to FULL
[code language=”sql”]
USE master
GO
ALTER DATABASE AdventureWorks SET RECOVERY FULL WITH NO_WAIT
GO
[/code]
We need to take a FULL backup first, just in case it was not using the FULL recovery model.
[code language=”sql”]
BACKUP DATABASE AdventureWorks TO DISK = N’D:\Backups\AdventureWorks_now.bak’
GO
[/code]
We can run some update statements against the Person.Contact table. We can start off by changing the EmailPromotion to 0 for ContactID 2.
[code language=”sql”]
USE AdventureWorks
GO
BEGIN TRANSACTION EmailPromotion WITH MARK
UPDATE AdventureWorks.Person.Contact SET EmailPromotion = 0 WHERE ContactID = 2
COMMIT TRANSACTION
GO
SELECT TOP 5 FirstName, LastName, EmailAddress, EmailPromotion, Phone
FROM AdventureWorks.Person.Contact
GO
[/code]
Update all of the phone numbers to the same number by leaving out a WHERE clause. This is a favorite mistake of mine.
[code language=”sql”]
BEGIN TRANSACTION UpdatePhone WITH MARK
UPDATE AdventureWorks.Person.Contact SET Phone = ‘123-456-7890’
–WHERE ContactID = 4
COMMIT TRANSACTION
GO
SELECT TOP 5 FirstName, LastName, EmailAddress, EmailPromotion, Phone
FROM AdventureWorks.Person.Contact
GO
[/code]
OOPS.
Finally change the last name from ‘Ackerman’ to ‘Smith’.
[code language=”sql”]
BEGIN TRANSACTION LastName WITH MARK
UPDATE AdventureWorks.Person.Contact SET LastName = ‘Smith’ WHERE ContactID = 5
COMMIT TRANSACTION
GO
SELECT TOP 5 FirstName, LastName, EmailAddress, EmailPromotion, Phone
FROM AdventureWorks.Person.Contact
GO
[/code]
Go ahead and back up the transaction log.
[code language=”sql”]
USE master
GO
BACKUP LOG AdventureWorks TO DISK = N’D:\Backups\AdventureWorks_now.trn’
GO
[/code]
First, we’ll use STOPBEFOREMARK to restore to the point prior to the EmailPromotion transaction.
[code language=”sql”]
USE master
GO
RESTORE DATABASE AdventureWorks FROM DISK = N’D:\Backups\AdventureWorks_now.bak’
WITH NORECOVERY, REPLACE
GO
RESTORE LOG [AdventureWorks] FROM DISK = N’D:\Backups\AdventureWorks_now.trn’
WITH STOPBEFOREMARK= ‘EmailPromotion’
GO
SELECT TOP 5 FirstName, LastName, EmailAddress, EmailPromotion, Phone
FROM AdventureWorks.Person.Contact
GO
[/code]
Now we can use STOPBEFOREMARK to restore to the point prior to the UPDATEPHONE transaction.
[code language=”sql”]
USE master
GO
RESTORE DATABASE AdventureWorks FROM DISK = N’D:\Backups\AdventureWorks_now.bak’
WITH NORECOVERY, REPLACE
GO
RESTORE LOG AdventureWorks FROM DISK = N’D:\Backups\AdventureWorks_now.trn’
WITH STOPBEFOREMARK= ‘UpdatePhone’
GO
SELECT TOP 5 FirstName, LastName, EmailAddress, EmailPromotion, Phone
FROM AdventureWorks.Person.Contact
GO
[/code]
Now we can use STOPBEFOREMARK to restore to the point prior to the LASTNAME transaction.
[code language=”sql”]
USE master
GO
RESTORE DATABASE AdventureWorks FROM DISK = N’D:\Backups\AdventureWorks_now.bak’
WITH NORECOVERY, REPLACE
GO
RESTORE LOG AdventureWorks FROM DISK = N’D:\Backups\AdventureWorks_now.trn’
WITH STOPBEFOREMARK= ‘LastName’
GO
SELECT TOP 5 FirstName, LastName, EmailAddress, EmailPromotion, Phone
FROM AdventureWorks.Person.Contact
GO
[/code]
STOPATMARK is similar to STOPBEFOREMARK, but it includes the marked transaction instead to stopping prior to it. Here we will restore back to LastName with STOPATMARK.
[code language=”sql”]
USE master
GO
RESTORE DATABASE AdventureWorks FROM DISK = N’D:\Backups\AdventureWorks_now.bak’
WITH NORECOVERY, REPLACE
GO
RESTORE LOG AdventureWorks FROM DISK = N’D:\Backups\AdventureWorks_now.trn’
WITH STOPATMARK= ‘LastName’
GO
SELECT TOP 5 FirstName, LastName, EmailAddress, EmailPromotion, Phone
FROM AdventureWorks.Person.Contact
GO
[/code]
But wait, there is more! You can also use WITH MARK to restore to a LSN instead of a name.
[code language=”sql”]
SELECT TOP 3 database_name, mark_name, description, lsn, mark_time
FROM msdb.dbo.logmarkhistory
ORDER BY mark_time DESC
GO
[/code]
[code language=”sql”]
USE master
GO
RESTORE DATABASE AdventureWorks FROM DISK = N’D:\Backups\AdventureWorks_now.bak’
WITH NORECOVERY, REPLACE GO RESTORE LOG AdventureWorks
FROM DISK = N’D:\Backups\AdventureWorks_now.trn’ WITH STOPBEFOREMARK= ‘lsn:45000000323400003’
GO
SELECT TOP 5 FirstName, LastName, EmailAddress, EmailPromotion, Phone
FROM AdventureWorks.Person.Contact
GO
[/code]