Duplicati: Backup Microsoft SQL Server

tldr; In some instances you may not be backing up the most recent SQL data if you have just the data folder selected or have the data folder selected along with the “Microsoft SQL Databases” section! The following is worth the read…

This is a quick how-to on backing up and restoring MSSQL (Microsoft SQL Server) databases and database files with Duplicati 2.

The documentation and forums are not clear on what you need to select to successfully backup an SQL server.

The interface offers a top “Microsoft SQL Databases” section but additionally still allows you to select the mdf and ldf files from within the data directory too. Which section should you use? Does ticking both have a bad side effect? (answer: Yes bad things happen!)

In the older versions if you have both selected you get no errors but likely have stale data being backed up and in version 2.0.4.5 and above you get errors like:

[Warning-Duplicati.Library.Main.Operation.Backup.FilePreFilterProcess.FileEntry-FailedToAddFile]: Failed while attempting to add unmodified file to database: C:\Microsoft SQL Data for LoBApp\MSSQL11.LoB2012\MSSQL\DATA\LoBAppDatabase.ldf
System.Data.SQLite.SQLiteException (0x80004005): constraint failed
UNIQUE constraint failed: FilesetEntry.FilesetID, FilesetEntry.FileID
at System.Data.SQLite.SQLite3.Reset(SQLiteStatement stmt)
at System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt)
at System.Data.SQLite.SQLiteDataReader.NextResult()
at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery(CommandBehavior behavior)
at Duplicati.Library.Main.Database.ExtensionMethods.ExecuteNonQuery(IDbCommand self, Boolean writeLog, String cmd, Object[] values)
at Duplicati.Library.Main.Database.LocalBackupDatabase.AddUnmodifiedFile(Int64 fileid, DateTime lastmodified, IDbTransaction transaction)
at Duplicati.Library.Main.Operation.Common.SingleRunner.c__DisplayClass3_0.b__0()
at Duplicati.Library.Main.Operation.Common.SingleRunner.d__2`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Duplicati.Library.Main.Operation.Backup.FilePreFilterProcess.c__DisplayClass1_0.<b>d.MoveNext()
[Warning-Duplicati.Library.Main.Operation.Backup.FilePreFilterProcess.FileEntry-FailedToAddFile]: Failed while attempting to add unmodified file to database: C:\Microsoft SQL Data for LoBApp\MSSQL11.LoB2012\MSSQL\DATA\LoBAppDatabase_Primary.mdf
System.Data.SQLite.SQLiteException (0x80004005): constraint failed
UNIQUE constraint failed: FilesetEntry.FilesetID, FilesetEntry.FileID
at System.Data.SQLite.SQLite3.Reset(SQLiteStatement stmt)
at System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt)
at System.Data.SQLite.SQLiteDataReader.NextResult()
at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery(CommandBehavior behavior)
at Duplicati.Library.Main.Database.ExtensionMethods.ExecuteNonQuery(IDbCommand self, Boolean writeLog, String cmd, Object[] values)
at Duplicati.Library.Main.Database.LocalBackupDatabase.AddUnmodifiedFile(Int64 fileid, DateTime lastmodified, IDbTransaction transaction)
at Duplicati.Library.Main.Operation.Common.SingleRunner.c__DisplayClass3_0.b__0()
at Duplicati.Library.Main.Operation.Common.SingleRunner.d__2`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Duplicati.Library.Main.Operation.Backup.FilePreFilterProcess.c__DisplayClass1_0.<b>d.MoveNext()

The above was from a setup where the SQL database was ticked in the “Microsoft SQL Databases” section but also the drive containing the SQL Data file was also included in the backup.

My test setup works flawlessly and backs up and restores the correct versions of the database. This instance has the “Microsoft SQL Databases” options ticked but not the actual folder in “Computer” containing the SQL database files.

2019-03-31 22_27_00-Domain Controller Lab - VMware Remote Console.png

You also do need the Advanced Options “snapshot-policy” set to Required otherwise the backup fails with:
[Warning-Duplicati.Library.Modules.Builtin.MSSQLOptions-MustSetSnapshotPolicy]: Snapshot policy have to be set to “required” when backuping Microsoft SQL Server databases. Changing to “required” to continue

2019-03-31 21_57_15-Domain Controller Lab - VMware Remote Console

In this instance when you go to restore a file you actually still see the expected drive and folder structure:

2019-03-31 21_58_38-Domain Controller Lab - VMware Remote Console.png

To restore make sure you tick the ldf and mdf file for the database you wish to restore!

Note that if you try to restore the database with SQL server started you will get errors:

Duplicati.Library.Main.Database.LocalRestoreDatabase-SearchingBackup]: Searching backup 1 (31/03/2019 20:32:52) ...,
2019-03-31 21:35:51 +01 - [Information-Duplicati.Library.Main.Operation.RestoreHandler-RemoteFileCount]: 1 remote files are required to restore,
...
]
Warnings: [
2019-03-31 21:35:51 +01 - [Warning-Duplicati.Library.Main.Operation.RestoreHandler-TargetFileReadError]: Failed to read target file: "C:\Microsoft SQL Data for LoBApp\MSSQL14.LOBAPP2017\MSSQL\DATA\LoBAppDatabase.mdf", message: The process cannot access the file 'C:\Microsoft SQL Data for LoBApp\MSSQL14.LOBAPP2017\MSSQL\DATA\LoBAppDatabase.mdf' because it is being used by another process.,
2019-03-31 21:35:51 +01 - [Warning-Duplicati.Library.Main.Operation.RestoreHandler-TargetFileReadError]: Failed to read target file: "C:\Microsoft SQL Data for LoBApp\MSSQL14.LOBAPP2017\MSSQL\DATA\LoBAppDatabase_log.ldf", message: The process cannot access the file 'C:\Microsoft SQL Data for LoBApp\MSSQL14.LOBAPP2017\MSSQL\DATA\LoBAppDatabase_log.ldf' because it is being used by another process.,
2019-03-31 21:35:51 +01 - [Warning-Duplicati.Library.Main.Operation.RestoreHandler-PatchFailed]: Failed to patch file: "C:\Microsoft SQL Data for LoBApp\MSSQL14.LOBAPP2017\MSSQL\DATA\LoBAppDatabase.mdf", message: The process cannot access the file 'C:\Microsoft SQL Data for LoBApp\MSSQL14.LOBAPP2017\MSSQL\DATA\LoBAppDatabase.mdf' because it is being used by another process., message: The process cannot access the file 'C:\Microsoft SQL Data for LoBApp\MSSQL14.LOBAPP2017\MSSQL\DATA\LoBAppDatabase.mdf' because it is being used by another process.,
2019-03-31 21:35:51 +01 - [Warning-Duplicati.Library.Main.Operation.RestoreHandler-PatchFailed]: Failed to patch file: "C:\Microsoft SQL Data for LoBApp\MSSQL14.LOBAPP2017\MSSQL\DATA\LoBAppDatabase_log.ldf", message: The process cannot access the file 'C:\Microsoft SQL Data for LoBApp\MSSQL14.LOBAPP2017\MSSQL\DATA\LoBAppDatabase_log.ldf' because it is being used by another process., message: The process cannot access the file 'C:\Microsoft SQL Data for LoBApp\MSSQL14.LOBAPP2017\MSSQL\DATA\LoBAppDatabase_log.ldf' because it is being used by another process.,
2019-03-31 21:35:51 +01 - [Warning-Duplicati.Library.Main.Operation.RestoreHandler-MetadataWriteFailed]: Failed to apply metadata to file: "C:\Microsoft SQL Data for LoBApp\MSSQL14.LOBAPP2017\MSSQL\DATA\LoBAppDatabase.mdf", message: The process cannot access the file 'C:\Microsoft SQL Data for LoBApp\MSSQL14.LOBAPP2017\MSSQL\DATA\LoBAppDatabase.mdf' because it is being used by another process.,
...
]
Errors: [
2019-03-31 21:35:51 +01 - [Error-Duplicati.Library.Main.Operation.RestoreHandler-RestoreFileFailed]: The process cannot access the file 'C:\Microsoft SQL Data for LoBApp\MSSQL14.LOBAPP2017\MSSQL\DATA\LoBAppDatabase.mdf' because it is being used by another process.,
2019-03-31 21:35:51 +01 - [Error-Duplicati.Library.Main.Operation.RestoreHandler-RestoreFileFailed]: The process cannot access the file 'C:\Microsoft SQL Data for LoBApp\MSSQL14.LOBAPP2017\MSSQL\DATA\LoBAppDatabase_log.ldf' because it is being used by another process.
]

I had to stop the Microsoft SQL Service. Restored the file on Duplicati.. started the SQL server and it had restored back to the date and time I expected!

STALE DATA !!! BAD THINGS!!!… 

So the next thing I want to document is what, if any, side effects or problems there are if you are backing up the entire drive (or folder) containing the SQL files _and_ the “Microsoft SQL Database” area on Duplicati.

2019-03-31 22_13_32-Domain Controller Lab - VMware Remote Console.png

In the screenshot above you can see I’ve ticked the “Microsoft SQL Database” section as well as including the Data folder for the SQL Database service.

This setup triggered the “Failed while attempting to add unmodified file to database” warning I’ve noted at the beginning of the article! What is more worrying is the data that was backed up was stale. Possibly only updated and available to Duplicati if you run a manual SQL backup or stop the SQL service!!!!!!

If you do want to backup the entire drive that contains your SQL server data or if you want to backup the SQL server folder it seems to work correctly without any stale data backup problems if you can’t.

Exclusions for the mdf and ldf files or exclusions for the data folder within a drive seem to affect the “Microsoft SQL Database” section too resulting in no file being backed up.

Workarounds

If you are not including your SQL data folder in your backup set (and are just ticking the database in the “Microsoft SQL Database” section) you are not affected. It might be a good idea to be sure that you can retrieve up to date information from your SQL backup in Duplicati.

If you do want to include your SQL folder in your Duplicati backup (for example you backup c:\ d:\ e:\ etc.. and can’t easily change your backup method to not encompass the SQL data folder one one of the drives) then..

The best workaround with no downtime is probably to scheduled task an SQL backup into another folder such as c:\SQLBak before you run your Duplicati backup. This gives the highest chance of having non-stale data. You’ve got your c:\SQLBak and whatever potentially stale data Duplicati retrieves from the SQL folder structure.

Another workaround would be to stop the SQL Server, run the backup, then start it again. This will ensure that SQL Server has flushed all the changes to the mdf and ldf files ready to be picked up by Duplicati.

This entry was posted in Uncategorized. Bookmark the permalink.

Comment on this topic

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s