What to use for "rolling back" test databases?

Question

What to use for "rolling back" test databases?

chrisph

Hall of Fame

Points: 3701
More actions
June 1, 2012 at 9:25 am

#258957

I'm looking for an easy way to rollback databases for our development environment. Important to note that the databases are located on a SAN. Could set up a maintenance plan with backup jobs but was wondering if there is an easier way. I tried a disk 'snapshot/rollback' software but it appears not to be compaitable with the SAN.
Thoughts?
Thanks!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

DBA_Dom SSCrazy Points: 2902 More actions · Answer 1

Are you trying to rollback data or code in the database?

There are several ways other than a backup however a backup is typically what I take before I push new code to test/production for the developers in case a rollback is needed and then it is cleaned off with normal maintainece plan. But scripting is also another way. For code I suggest TFS. Data would be a little different and would depend on the size of the database.

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 2

The easiest way to "rollback" IMHO, it to do log-file backups just like you would in production and then restore to a point-in-time. Either that or take a full backup before any major changes so you can easily do a restore.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150468 More actions · Answer 3

chrisph (6/1/2012)
I'm looking for an easy way to rollback databases for our development environment.

Another option (Enterprise/Developer Edition) is to create a snapshot database and restore to that. As always, the best option for you depends on various factors. Maintaining the snapshot database (copy-before-write) does have some overhead, and the snapshot sparse file may grow large if you typically make many changes before rolling back, but it is another option to explore.

CREATE DATABASE database_snapshot_name

ON

(

NAME = logical_file_name,

FILENAME = 'os_file_name'

) [ ,...n ]

AS SNAPSHOT OF source_database_name

[;]

RESTORE DATABASE { database_name | @database_name_var }

FROM DATABASE_SNAPSHOT = database_snapshot_name

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi