Hi. I’m learning about Restic, and I have an issue with how Restic handles Microsoft SQL Server databases.
SQL Server has a .MDF and a .LDF file for each database. From what I’ve read, while the SQL Server service is running, it writes to these files throughout its operation. Apparently it performs this write during “checkpoint” operations automatically. But it doesn’t seem to update the “Date Modified” of the files until the SQL Server service is stopped.
I have Restic set up to back up my files once a day. But when I check my snapshots, I see .MDF and .LDF files that have the same “Date Modified” as the current .MDF and .LDF files on the hard drive. When I restore those files and check their data, the data is old. It seems that Restic is not backing up these files because they don’t appear to have been modified (judging by the date on the file).
To work around the problem, I am restarting the SQL Server service right before I back up. This updates the “Date Modified.” When I check the data in the snapshot, the information is up to date.
So is restarting SQL Server a recommended method for ensuring Restic backs up the newest data? Thank you.
restic tries to speed up the backup by only reading/comparing the metadata when a parent backup is found. The speed up can be prevented by using backup --force which forces restic to re-read all files as if they were new.
@764287, thank you. I found this regarding --force: “force re-reading the target files/directories (overrides the “parent” flag).”
But does that force all of my data to be uploaded, regardless of changes? Or does it just mean files are somehow checked more thoroughly and then backed up if a difference is found?
It means that the files are more thoroughly checked. When using --force, restic does not only compare metadata (like mtime and permissions) of a file, it compares the checksums of the chunks aswell. restic does not save/upload any duplicate data.
If you want to create a backup while the database is still running then I’d strongly suggest to use the windows VSS to create a shadow copy and then create a backup of that. That ensures that restic can backup a consistent snapshot of the database.
I suspect that the timestamps of a file is mostly only updated when closing it. And the SQL Server probably keeps it open all the time.
Actually, when using --force, restic does not use a parent snapshot at all and hence is not checking any metadata. It assembles trees and blobs for every directory and file, hashes them, and then goes “oh, this is already in the index so I’ll skip it” or “this isn’t in the index so I’ll add it.”
The options --force and --parent are related:
-
--force: Do not use a parent snapshot; therefore do not perform metadata “quick-succeed” checks. -
--parent ID: Use the snapshot with the given ID as the parent snapshot. - Neither argument: look for the most recent snapshot with the same host name and path set and use it as the parent. If no such snapshot exists, do not use a parent snapshot.
A parent snapshot is just an optimization that allows restic to skip reading and hashing files whose metadata hasn’t changed.
No, but it does mean that the contents of every file on disk will be read again, even if they haven’t changed. Deduplication still happens but after chunking and hashing the contents of the file.
Thank you, @764287 and @cdhowie!
MichaelEischer and rawtaz (who I’m apparently not allowed to at-mention because I’m new), that’s not an option with my host, but I think they’re already using shadow copy. I’m not sure. But --force works well enough for now. Thanks anyway.
If you’re not sure whether they execute restic in a shadow environment, it would be better to back up a SQL dump of the database as there’s no way that would be corrupt (server bugs and hardware issues notwithstanding). It’s not as fast to restore but I’ll take known-good-and-slow over maybe-good-and-fast.
@cdhowie, here is the PowerShell script they use. Do you agree they are shadow copying the files?
$resticExe = "C:\Windows\System32\restic.exe"
$rootVolume = "C:\"
##WINDOWS VSS
$ShadowPath = $rootVolume + 'shadowcopy\'
# Create a volume shadow copy and make it accessible
$s1 = (Get-WmiObject -List Win32_ShadowCopy).Create($rootVolume, "ClientAccessible")
$s2 = Get-WmiObject Win32_ShadowCopy | Where-Object { $_.ID -eq $s1.ShadowID }
$device = $s2.DeviceObject + "\"
# Create a symbolic link to the shadow copy
cmd /c mklink /d $ShadowPath "$device"
##WINDOWS VSS
#Get Foldernames into Variable for ForEach Loop
$DFSFolders = get-childitem -path $ShadowPath | where-object {$_.Psiscontainer -eq "True"} |select-object name
##Source Env Data
. "C:\Windows\System32\restic_repo.ps1"
#Run Backups
foreach ($DFSfolder in $DFSfolders)
{
cmd /c $resticExe backup --limit-download 31250 --limit-upload 31250 --tag HW_Backup ($ShadowPath + $DFSfolder.name)
}
# Delete the shadow copy and remove the symbolic link
$s2.Delete()
cmd /c rmdir $ShadowPath
echo "Done Backing Up"
cmd /c $resticExe forget --prune --group-by paths --tag HW_Backup --keep-daily 90If you want to backup with restic on Windows, and you want to use VSS, why not just use https://github.com/kmwoley/restic-windows-backup and be done with it?
Because my host set up this backup configuration–not me. I need to work within their confines. The code above has actually been partially copied from the project in your link.
Yes, that looks right. I didn’t realize that you were able to see the contents of the script they run.