Neal Waterstreet

Migrating from CrashPlan for Home

by Leave a Comment

I’ve been using CrashPlan for Home for a few years now to back up my NAS and a few of my machines. They’ve been great and I’ve never had any issues once I got everything configured. Working in IT and hearing stories about friends losing data led me to follow the 3-2-1 backup rule. Accidents can happen in many different ways and are usually expensive.

About a year ago I received an email from CrashPlan announcing that they were moving away from the personal backup business and focusing on small business. The Home plan was a pretty good deal, and I wouldn’t be able to match the features that their service provided for the price.

A few weeks ago, after some focused procrastination and another email warning about the deadline, I decided to take the easy route and switch to their Small Business account. That option is a little more expensive, but it was a seamless move. It didn’t require any changes at all. I read some reviews of the other comparable services, and it just seemed like a good idea to stay with CrashPlan.

WITH MARK is your friend

by Leave a Comment

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]

sshot-1

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]
sshot-2

 

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.

sshot-3

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]

sshot-4

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]

sshot-5

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]

sshot-6

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]

sshot-7

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]

sshot-4

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]

sshot-8
[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]

sshot-7