DIY Database Clones

Question

DIY Database Clones

Tonie Huizer

SSC Veteran

Points: 243
More actions
April 29, 2021 at 12:00 am

#3874481

Comments posted to this topic are about the item DIY Database Clones

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

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

sqlstad SSCrazy Points: 2484 More actions · Answer 1

There is also a PowerShell module called dbaclone that is also able to do this. This PowerShell module works similarly to how SQLClone works without the fancy GUI etc.

GitHub: https://github.com/sqlcollaborative/dbaclone

PowerShell Gallery: https://www.powershellgallery.com/packages/dbaclone

Documentation: https://github.com/sqlcollaborative/dbaclone/wiki

Tonie Huizer SSC Veteran Points: 243 More actions · Answer 2

Wow Sander that's pretty nice, I wasn't aware of all the effort you put in that library, it looks really complete.

I will definitely try it out.

What do you use as a VSS Requestor?

sqlstad SSCrazy Points: 2484 More actions · Answer 3

The module uses diskpart to create the VHDs.

This was the easiest way to accomplish this and was already available within Windows by default.

When you create an image it will create the VHD file, backup the database and restore it into the VHD

If you have any questions feel free to contact me.

Tonie Huizer SSC Veteran Points: 243 More actions · Answer 4

Diskpart for the VHD handling works fine indeed, you wouldn't need the Hyper-V modules then.

But what I'm referring to is does your module make use of the Volume Shadow Service?

So, does dbaclone trigger the VSS when copying the MDF's or do you make a database backup (with snapshot) yourself when creating an image?

sqlstad SSCrazy Points: 2484 More actions · Answer 5

It does the backup and restore itself, it doesn't use VSS.

You can choose to use an existing backup or to create one on the fly

mv_gibson SSC Rookie Points: 43 More actions · Answer 6

Excellent write up. Thanks for putting this together. I do have a question around the SQL VSS Writer....in my experience its use, without pushing the copy only flag, will interrupt your backup plan on your production databases. We have used a number of tools that we had to figure out how to customize in order to avoid such problems. Is there a way to set that flag, or does the tool do that automatically? Thanks so much, Mike

Tonie Huizer SSC Veteran Points: 243 More actions · Answer 7

Thanks Mike!

To my knowledge it's true what you say. Backups without "copy only" flag can mess up your backup chain:

“Copy Only backup is an independent backup and it does not break the chain of database backup LSN (Log Sequence Number). In another word, it does not disturb the original backup workflow, but just create a copy of the original database independently.”

More can be read in a post of Pinal Dave on SQL Authority or for example the doc pages of VEAAM.

So, back to VSS, according to the documentation the Copy-only must be called up on explicitly:

"The requestor can set the backup type as a copy-only backup by setting the VSS_BACKUP_TYPE option as VSS_BT_COPY with the call"

In my demonstration I didn't called up on it explicitly, the only thing "supported" by Hobocopy is:

Hobocopy /full, which triggers a VSS_BT_FULL in stead of VSS_BT_COPY but that looks about the same.
Although, I'm not sure what it does with your backup plan.
Or make use of the .NET library of AlphaVSS SetBackupState , with a .NET app or PowerShell, in which you can specify the VssBackupType as Copy