A virtual copy of a database is that possible?

Question

Post reply

A virtual copy of a database is that possible?

ben.brugman

SSChampion

Points: 13369
More actions
November 14, 2011 at 7:51 am

#251725

Situation:
One database,
20 developers.
For testing purposes a developer would like a virtual database of the existing database only for the duration of the testing. (A snapshot of the database). Alterations to made to the virtual database are not affecting the 'main' database.
The testings process does include software which uses transactions.
(So using the snapshot mechanism provided is not an option).
This must be a problem encountered many times. But is there a solution to this problem ?
Ben Brugman
(The size of the database is such that using a copy of the database wil take to much time for the developer. The amount of alterations to the database the developer makes is very limited).

Viewing 9 posts - 1 through 8 (of 8 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Any reason why they can't do a copy of the db overnight (backup / restore).

Then during the day do everything AS begin tran, rollback tran so that they keep it clean.

repeat nightly as needed.

Plan B => http://www.red-gate.com/products/dba/sql-virtual-restore/

ben.brugman SSChampion Points: 13369 More actions · Answer 2

Ninja's_RGR'us (11/14/2011)
Plan B => http://www.red-gate.com/products/dba/sql-virtual-restore/

Thanks for plan B.

Found a similar plan C:

Plan B => http://www.idera.com/Products/SQL-toolbox/SQL-virtual-database//

Ninja's_RGR'us (11/14/2011)
Any reason why they can't do a copy of the db overnight (backup / restore).
Then during the day do everything AS begin tran, rollback tran so that they keep it clean.
repeat nightly as needed.

Not actual enough and time consuming during the day.

Actual test will commit data.

Thanks again for plan B.

Ben Brugman

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

ben.brugman (11/14/2011)
Ninja's_RGR'us (11/14/2011)
Plan B => http://www.red-gate.com/products/dba/sql-virtual-restore/
Thanks for plan B.
Found a similar plan C:
Plan B => http://www.idera.com/Products/SQL-toolbox/SQL-virtual-database//
Ninja's_RGR'us (11/14/2011)
Any reason why they can't do a copy of the db overnight (backup / restore).
Then during the day do everything AS begin tran, rollback tran so that they keep it clean.
repeat nightly as needed.
Not actual enough and time consuming during the day.
Actual test will commit data.
Thanks again for plan B.
Ben Brugman

Plan D, list tables being updated.

BCP OUT, BCP IN.

Should run in seconds unless you have TBs of data in those tables.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

Restore the DB onto the Dev server, and then make a snapshot.

Then you can run normal testing with transactions, and restore the database from the shapshot when you are done. A restore from a snapshot should only take a few seconds it you are just doing small sets of transactions.

ben.brugman SSChampion Points: 13369 More actions · Answer 5

Michael Valentine Jones (11/14/2011)
Restore the DB onto the Dev server, and then make a snapshot.

To which snapshot technique are you refering?

We have a single development database but about 20 developers who all want to use the technique.

Thanks for your time and attention,

Ben Brugman

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 6

ben.brugman (11/15/2011)
Michael Valentine Jones (11/14/2011)
Restore the DB onto the Dev server, and then make a snapshot.
To which snapshot technique are you refering?
We have a single development database but about 20 developers who all want to use the technique.
Thanks for your time and attention,
Ben Brugman

There is only one database snapshot "technique" that I know of.

From SQL Server 2008 Books Online:

Database Snapshots

http://msdn.microsoft.com/en-us/library/ms175158(v=SQL.100).aspx

Requires Developer or Enterprise Edition

ben.brugman SSChampion Points: 13369 More actions · Answer 7

Michael Valentine Jones (11/15/2011)
There is only one database snapshot "technique" that I know of.

Thanks for pointing out this technique.

I knew of snapshot isolation but this is used differently, I guess.

Other snapshot techniques are used with VMware and San disks.

(The VM ware technique we looked into, but can not be used with our setting with multiple users. When you are a single user you can snapshot your 'system' including the database and return to the snapshot at a later time.)

Thanks again,

Ben Brugman

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 8

Get a copy of SQL Virtual Restore from Red Gate. Based on the incredible Hyperbac technology you can mount READ/WRITE compressed backup files as databases!! Just amazing what you can do for testing scenarios with that capability.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service