Creating a Windows SQL Server database fails when the destination file is in a subdirectory. Works OK when you save the file to the root folder on the S3 Bucket.
What is your rclone version (output from rclone version)
rclone v1.55.1
Which OS you are using and how many bits (eg Windows 7, 64 bit)
Windows Server 2016
Which cloud storage system are you using? (eg Google Drive)
AWS S3 Bucket
The command you were trying to run (eg rclone copy /tmp remote:tmp)
mount s3-awsbkpcog01:s3-awsbkpcog01/ S: --vfs-cache-mode full --config "C:\Program Files\rclone\.config\rclone\rclone.conf"
The rclone config contents with secrets removed.
[s3-awsbkpcog01]
type = s3
provider = AWS
env_auth = false
access_key_id =
secret_access_key =
region = eu-central-1
location_constraint = EU
storage_class = STANDARD
Additional Info
The latest version of Microsoft SQL, which is MSSQL 2019 CU19 (15.0.4102.2) is able to create a sql file or a database backup inside a directory.
MSSQL 2019 RTM (15.0.2000.5) or versions prior to to this, i.e. MSSQL 2017, 2016 etc. are unable to create a sql file inside a folder on winfsp. With every version of MSSQL a backup or database file can be created on the root of the filesystem.
2021-03-30 10:29:57.66 Backup BackupIoRequest::ReportIoError: write failure on backup device 'S:\test\test.bak'. Operating system error 87(The parameter is incorrect.).
2021-03-30 10:29:57.66 Backup Error: 3041, Severity: 16, State: 1.
2021-03-30 10:29:57.66 Backup BACKUP failed to complete the command BACKUP DATABASE
I opened a support case at Microsoft already, but Microsoft will not investigate the case, if additional software like rclone is used. I also created a Bug report in Github, because I thougt this would be a problem with winfsp, but they think it's more a Rclone issue.
in the meantime,
perhaps there is something wrong the sql backup setup?
have you tested the backup pointing to the root and that works.
then you change the dest to subfolder and it fails?
need to post a rclone debug log, showing the point in time of the error.
also, i would test with s3-awsbkpcog01:s3-awsbkpcog01 instead of s3-awsbkpcog01:s3-awsbkpcog01/
That bug report seems to indicate that it is to do with the Mount Manager and maybe rclone is discarding the special path \\.\S - you don't seem to be using that path in the example above though?
Something you could try is mounting it on a directory instead of a drive letter, so instead of S: choose an existing but empty directory say C:\gdrive and use that instead of S:
If that doesn't work we'll have to call in the experts - @albertony is good at Windows file systems.
i would try to mount to a folder, not a drive letter mkdir c:\mount\ mount s3-awsbkpcog01:s3-awsbkpcog01 c:\mount\sqlbackup --vfs-cache-mode full --config "C:\Program Files\rclone\.config\rclone\rclone.conf"
perhaps there is something wrong the sql backup setup?
I'm able to backup my database in any folder on my local System. Only a folder on S3 mounted with Rclone will give me this error message. So I can confirm that this is not an MSSQL issue.
have you tested the backup pointing to the root and that works. then you change the dest to subfolder and it fails?
Correct. I have tested the backup to the root of the mounted S3 Bucket and that works. But if I try to set the Backup within the subfolder then I recive the error mentioned in my first post. I have also tested this with different MSSQL Instances. With all of them I get the same error, except with MSSQL 2019 CU19.
I attached the logfile to this post. I first created a DB backup with the name database.bak on the Root of the S3 Bucket, which worked. After that I created a Backup in the folder "test" which resulted in an error.
No, I tried to use it with the special path, but then im not even able to access the S3 mount. So no, I'm not using that example.
That's a good Idea, which I will probably try. Unfortunattely this wouldn't be a solution for me. Just to give a little context. With MSSQL you are able to run multiple Instances on one Server. So for each instance I would like to have its on subfolder by instance name. If I mount the subfolder with rclone directly I will face the same problem again. Also an Issue is, that if all full- and logbackups - which are made every 1h and kept for 30days - are on the root of the mounted volume, then the time to list all files takes a while, because there are often more than 3000+ files on the root drive.
i workaround that by using a timstamp and then i can run as many instances at the same time as i need.
works well
the source folder is created on the fly using a timestamp. C:\data\rclone\scripts\rclone.exe sync "b:\mount\rcloner\data_wasabi_sync+check_20210427.144448\data" "wasabicrypten07data:en07data/data/rclone/backup" --stats=0 --fast-list --backup-dir=wasabicrypten07data:en07data/data/rclone/archive/20210427.144448 --log-level=DEBUG --log-file=C:\data\rclone\logs\data_wasabi_sync+check\20210427.144448\rclone.log
As far as I understand, with sync I would have to backup the databases on the local system first, and then sync the source to the s3 bucket?
If possible I would like to avoid creating backups on the local system and sync it to the cloud. Only real solution would be a direct backup to AWS. DBs often take a lot of space on the local System and also if there are too many steps from taking a backup to creating a backup on the S3 Bucket, there is a greater chance that something goes wrong and I might not even notice.
Sorry I don't wan't to sound picky but I would need to monitor those backups closely.
Please inform me if I'm wrong and I wouldnt need to take the backup on the local system first.
i understand that you need to use mount,
i was sharing a real world command i use everyday with rclone, but with sync
rclone mount s3-awsbkpcog01:s3-awsbkpcog01 c:\mount\sqlbackup\20210427.144448 --vfs-cache-mode full --cache-dir=c:\path\to\cache\sqlbackup\20210427.144448 --log-level=DEBUG --log-file=C:\path\to\log\sqlbackup\20210427.144448 --config "C:\Program Files\rclone\.config\rclone\rclone.conf"
then point the sql server backup script to that folder.
as far as i know; one way or another, sql server going to have to backup to local system. rclone mount is using --vfs-cache-mode so in effect, sql server is backing to to the local rclone cache and then rclone will move that to the cloud.
so i would choose to backup to local storage and then rclone move.
i do know that sql server can backup to azure, perhaps in the way you can get around the local system issue.