I think SQL Clone is one of the game changing products from Redgate. This product really fits into a DevOps mindset, allowing me to quickly and easily build (and rebuild) a dev database.

While the agent web pages make this easy, they’re slightly cumbersome and the PowerShell cmdlets fit better with a DevOps flow. In setting up a Query Store demo, I found myself changing some data and needing to reset my database rapidly, so I built a quick PoSh function to do this for me.

Here’s my function:

function Add-ADWClone {

param([Parameter(Mandatory=$true)][string] $CloneName)

$mycredential = Get-Credential

Connect-SqlClone -ServerUrl ‘http://socrates:14145’ -Credential $mycredential

# remove the image if it exists

$SqlServerInstance = Get-SqlCloneSqlServerInstance -MachineName Plato -InstanceName SQL2016

$Clones = Get-SqlClone -Location $SqlServerInstance

if ($Clones.Name -contains $CloneName) {

$CloneToDelete = Get-SqlClone -Location $SqlServerInstance –Name $CloneName

Remove-SqlClone -Clone $CloneToDelete | Wait-SqlCloneOperation

}

# Create the new image

$image = Get-SqlCloneImage -Name ‘Adw2014Base’

$sqlServerInstance = Get-SqlCloneSqlServerInstance -MachineName Plato -InstanceName SQL2016

$image | New-SqlClone -Name $CloneName -Location $sqlServerInstance | Wait-SqlCloneOperation

}

In this function, I’m taking the name of a cloned database. I’ll use that to check if the clone exists, and if so, remove it. I do this by using Get-SqlClone. Once that’s done, I get the image, which is static here (this is a function for a specific project) and then I’ll create the new clone.

This works great. If I run the command, I’ll get a clone being created. You can see my client in the back creating the clone database.

If I re-run the command, I’ll see the delete.

And then the clone create again.

Changing Development

When I first saw a prototype a few years ago, I could immediately think back to being a full time developer and the hassles of manipulating my development database, making data changes to test code, trying to reset them, writing scripts to undo changes and more. At some point I tried to perform backups and restores of a standard database, but I’d keep forgetting to update things.

SQL Clone makes this easier, and together with some way to push/pull code from a VCS to your database, it means that I can quickly reset a database back to a known state. One of the things that I’d like to easily do is whack my development database, recreate a new one, and then move on with writing code. If I make a mistake, I repeat the process.

Moving On

This is a basic Proof of Concept, something I just whacked together for a project, so I’ve coded in the image name, and I get the credentials from the user. I could clean this up, and have it as a simple up-arrow, enter from the  PoSh command line that I use as I need to reset my system. Or code in a saved, secure credential that lets me double click some batch file to run this for me.

Automation speeds up development by removing simple tasks from the developer. SQL Clone makes it easy to reset my database to a known state (the image) and create new databases as needed.

If you haven’t tried SQL Clone, give it a try today by downloading an evaluation.

Filed under: Blog Tagged: DevOps, powershell, SQL Clone, syndicated