Partager via

Using SQL 2005 Snapshots As A Rollback Procedure

  • Article

One of the great new features of SQL 2005 is database snapshots. With one simple transact SQL statement, you can provide your users with a read-only, static copy of their data. For example, you can take a snapshot on the last day of the month and users can do month-end reporting without worrying about changes to the underlying data. That’s pretty powerful. But did you know that snapshots can also be used to recover to a point in time? That is the subject of this article – how to use database snapshots to significantly reduce your recovery time in certain emergency situations.

As DBA’s, we spend a lot of time worrying about how to protect our data from disaster. There are really 2 goals here:

1. Amount of acceptable data loss.

2. Amount of acceptable downtime.

On the higher end of acceptable data loss we have full and differential backups. On the low end we have log backups, log shipping and finally mirroring where the potential loss is mere seconds. Pretty cut and dried.

But what about the down TIME involved? With mirroring you can fail over to a hot standby rapidly and automatically, thus achieving the smallest possible recovery time. This protects your data against physical disaster but does nothing to protect you from data corruption, because the mirror simply replicates the data “as is”.

To roll back bad data, you’re back to good old full, differential and log backups. For small databases this can be pretty simple. But it is often meticulous work, and if you restore one too many logs, you’ll have to start all over. For large databases, the recovery time can be very long. If you’re in this business long enough you’ll experience losing the online backups and have to restore from tape. I’ve seen situations where this took 1-2 weeks!

This is where snapshots can really help. Since the database can be recovered DIRECTLY from a snapshot, without going through the full, differential and log backups, this gives us a way to be very granular with point-in-time recoveries. And the recovery is almost instantaneous. So changes can be rolled back with almost no downtime.

Here is how we use it in MSCOM. Let’s say I’m doing an application upgrade. Before starting, I create a snapshot. Now something goes wrong. The deployment doesn't pass "smoke" testing, for what ever reason you can imagine -- and we decide to roll back and start again. I just recover from the snapshot, and in seconds all the changes are gone. It’s like having an undo file, or running the whole deployment in a transaction. And it works for both DDL and DML changes.

I think you can see how powerful this can be. We recommend making this a regular part of your internal change controls. Having said that, there are a few points to consider:

  • Snapshots do not back up your full text indexes, so don’t stop doing database backups.
  • Recovering from a snapshot breaks the log “chain”, so follow that up with a new full backup.
  • Recovering from a snapshot (like any other restore) breaks mirroring and transactional replication. So plan on breaking the mirror beforehand and taking new replication snapshots afterwards.
  • Recovering from a snapshot will undo everything, so take steps to stop taking changes from your users or point them to a redundant server.

Now for some sample code to get you started:

  1. Run this in the database you wish to snapshot. If you uncomment the “exec” statement it will print a script and then create the snapshot in one step:

set nocount on

declare @sql_string varchar (4000)

set @sql_string = 'Create Database ' + db_name() + '_Recovery_SS ON '

select @sql_string = @sql_string +

'(Name = ''' + name + '''' + ', FILENAME = ''' +

substring(filename, 1, len(filename)-3) + 'ss''),'

from sys.sysfiles

where groupid <> 0

select @sql_string = substring(@sql_string, 1, len(@sql_string)-1) + ' AS SNAPSHOT OF ' + db_name()

print @sql_string

-- exec (@sql_string)

  1. Drop the snapshot once you're sure you don’t need to roll back:

drop database <db-name_Recovery_SS>

  1. Recover changes, Undo:

RESTORE DATABASE <db-name> from

DATABASE_SNAPSHOT = <db-name_Recovery_SS>'

Here’s a test I did. Try it for yourself. First create a snapshot. Then in your original database, create a table, add 1 million rows, run sp_spaceused to prove they exist. Then restore from the snapshot and run sp_spaceused again to prove both schema changes and data are rolled back. On my system, the restore took 5 seconds:

create table Lots_Inserts (

Recno int identity,

Time     datetime,

Data int

)

go

set nocount on

go

declare @counter int

set @counter = 0

while @counter < 1000000

begin

            insert Lots_Inserts (Time, Data)

            values ( getdate(), DATEPART(ms, GETDATE()) )

            select @counter = @counter +1

end

go

-- Took 3 minutes, 30 seconds

sp_spaceused lots_inserts

Comments

  • Anonymous
    May 20, 2008
    Can you please tell me how to backup a database when snapshots are enabled? We always have 2 days worth of snapshots on our production machine apart from daily full backup's. What we observed is we are not able to restore any of the full backup's and all restores terminate with an error stating that they cannot restore the database as it has snapshots. Please mail me at shivakon@microsoft.com -Shiva

  • Anonymous
    May 21, 2008
    Hi Shiva: Your backup strategy need not change.  And the snapshot does not invalidate them.  But before doing a restore you have to first drop the snapshots.

  • Anonymous
    August 14, 2008
    If a database has a snapshot.  Can you back up the database, delete snapshot, and when you restore the database will it restore the snapshot?  If not can you backup a snapshot?