Neal Waterstreet

  • Home
  • About
  • Book Reviews
    • 2018 Reading List
    • 2017 Reading List
    • 2016 Reading List
    • 2015 Reading List
    • 2014 Reading List
    • 2013 Reading List
  • Projects
    • OBi200
    • DS716+ memory upgrade
    • Cord cutting
  • Presentations
  • Contact

Migrating from CrashPlan for Home

June 4, 2018 by neal 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.

Filed Under: Home Network Tagged With: Backups, CrashPlan

WITH MARK is your friend

November 22, 2012 by neal 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

Filed Under: Scripts Tagged With: Backups

February 2023
M T W T F S S
 12345
6789101112
13141516171819
20212223242526
2728  
« Mar    

Blogroll

  • Atlanta MDF
  • Atlanta Microsoft Business Intelligence Group
  • Atlanta Modern Excel User Group
  • Chris Webb's BI Blog
  • codegumbo
  • Lance England
  • PowerPivotPro
  • Prologika
  • SQLBI

Tags

#SQLSatATLBI Aereo at Atlanta Microsoft BI Azure Azure DataFest Backups Batch Certification changeset Chicago Conference CrashPlan Data Modeling Data Quality DAX Deployment DVR Entity-based staging Error Messages Excel Filter Goals Headphones Healthcare Leaf Tables MDM MDS MDS Add-in for Excel Oops! Something went wrong PASS Healthcare VC Power BI Power Pivot review Roku SQLSaturday SQL Saturday 800 SQLSaturday Atlanta BI SSIS Streaming Subscription View Time Training Validation Version Flag

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

Copyright © 2023 · Minimum Pro Theme on Genesis Framework · WordPress · Log in