Images Seem To Be Being Reused When New Images Are Created

CraigEddy

Here's our situation:

We perform nightly backups, and copy the BAK file to a location.

The next morning, I use the following script to create a new Image from the latest BAK file:

# Script to create a new SQL Clone data image from a backup file
param (
  [string]$TemporaryServerMachine = 'STD2R01-BRKFIX', # Specifies which instance of SQL Server will be temporarily used to restore the backup while creating an image
  [string]$TemporaryServerInstance = 'FACTSInfo', # No additional disk space is required for this temporary restore
  [string]$SourceDatabase = 'FITDB'
)

$SQLCloneServer= 'http://ste_ngdb01:14145'
$BackupFolder = '\\ste_file03\Backups\SQLClone\BackupSources\DailyBackups'
$ImagesFolder = '\\ste_file03\Backups\SQLClone\Images' 


Connect-SqlClone -ServerUrl $SQLCloneServer
 
$TemporaryServer = Get-SqlCloneSqlServerInstance -MachineName $TemporaryServerMachine -InstanceName $TemporaryServerInstance # You can omit the instance parameter if there is only one instance
 
 
if (!(Test-Path ($BackupFolder)))
  {
    write-host 'Backup folder not found. Exiting.'
    break
  }
 
# Get the latest backup file for our database (striped backups would be more complex)
$BackupFiles = Get-ChildItem -Path $BackupFolder  |
    Where-Object -FilterScript { $_.Name.Substring(0,$SourceDatabase.Length) -eq $SourceDatabase}   # My backup files always start with the database name
 
# Now we have a filtered list, sort to get latest
$BackupFile = $BackupFiles | 
    Sort-Object -Property LastWriteTime  |    
    Select-Object -Last 1 # I only want the most recent file for this database to be used
 
$BackupFileName = $BackupFile.FullName
 
#Start a timer
$elapsed = [System.Diagnostics.Stopwatch]::StartNew()
 
"Started at {0}, creating data image for database ""{1}"" from backup file ""{2}""" -f $(get-date) , $SourceDatabase , $BackupFileName
 
$DataImageName = $SourceDatabase + "_" + (Get-Date -Format "yyyyMMdd") # Prepare a name for the data image, with a timestamp
$ImageDestination = Get-SqlCloneImageLocation -Path $ImagesFolder # Point to the file share we want to use to store the image
 
New-SqlCloneImage -Name $DataImageName -SqlServerInstance $TemporaryServer -BackupFileName $BackupFileName -Destination $ImageDestination | Wait-SqlCloneOperation # Create the data image and wait for completion

"Total Elapsed Time: {0}" -f $($elapsed.Elapsed.ToString())  

Then, we will randomly Clone this image to some instance or other. The next day, or a couple of days later, we'll create a new Image from a new nightly backup file and then make a Clone from that.

But....something very bad is occurring...when we look at the cloned database for the second clone, it's actually used the Image from the first clone. For example, if we look at one of our "audit" tables which gets several thousand rows per day in it, the most recent clone/image will only have data as recent as the first clone/image.

I've looked at the dbo.Images table and see data such as this:

Id	CreatedBy	DeletedOn	FriendlyName	ImageLocationId	ImageLocationResourceName	ImageType	OriginDatabaseName	OriginServerName	SizeInBytes	SourceLocationId	SourceLocationResourceNames	State	OriginDatabaseVersion
40	FIS\eddycr	NULL	FIARPE_20171214	2	fiarpe_20171214_00000040_ore	1	FIARPE	STP-DB01\FACTSINFO	5267218944	27	["\\\\ste_file03\\Backups\\SQLClone\\BackupSources\\DailyBackups\\FIARPE.bak"]	2	706
61	FIS\eddycr	NULL	FIARPE_20171220	2	fiarpe_20171220_00000061_11o	1	FIARPE	STP-DB01\FACTSINFO	5267218944	27	["\\\\ste_file03\\Backups\\SQLClone\\BackupSources\\DailyBackups\\FIARPE.bak"]	2	706
66	FIS\eddycr	NULL	FIARPE_20171221	2	fiarpe_20171221_00000066_u4q	1	FIARPE	STP-DB01\FACTSINFO	5267218944	27	["\\\\ste_file03\\Backups\\SQLClone\\BackupSources\\DailyBackups\\FIARPE_20171221.BAK"]	2	706

Notice that the SizeInBytes value is identical for each of the rows, a virtual impossibility if these Images were all sourced from different .BAK files.

Please help! This issue is making SQL Clone unusable for us...

We are using v2.4.1.8761

PlantBasedSQL

Thank you @CraigEddy - apologies I couldn't help out with this one, but I'm sure Support will be able to get you back on track and help out!

Happy Holidays!

PlantBasedSQL

Hi @CraigEddy thank you for your post and sorry to hear you're having issues with this - I just used your exact script to try and replicate the issue on my machine but unfortunately (or fortunately) it all seems to work fine.

The only key difference I noted, however, was the differences in the dbo.Images table where my backups in SourceLocationResourceNames came through as "AdventureWorks_Production_[X].BAK" where X was 20171220, 20171221 and 20171222 for the past 3 days - I notice yours are showing as FIARPE.bak (x2) and FIARPE_20171221.bak, are any of these expected backup files not being generated, are they not in the folder, or could anything be going wrong at the point Clone consumes them? (Which would be why you end up with the same size etc.)

If this isn't the case and something is indeed going wrong with SQL Clone then I would say the best course of action would be to send this all through to support@red-gate.com along with your serial key and the team will do everything they can to help out ASAP!

CraigEddy

The BAK files were indeed being generated daily, but always overwrite the contents of that folder and named, for example, "FIARPE.BAK" (we actually have three databases which we image/clone and all three exhibited the issue we're seeing). The images were created with the date suffixes in the image names.

The FIARPE_20171221.BAK instance was my hope that if we just renamed the source BAK file then we wouldn't see this problem.

We definitely confirmed that if we create an image from a particular BAK file, clone it to a server, and check its data that the data was not coming from the source BAK file. However, if we restored that same BAK file directly to a server, the data was indeed current.

Thanks, I'll send an email to support.