Backing Up all databases on a SQL Server to Appended Backup.

[MWHeier]

Looking to backup all databases on server to a single appended backup file but running into an error. When I do individual files for each database I do not see this issue.

Here is my command. I am using the default @init value now with the same issue.

exec DBA.[dbo].[DatabaseBackup] @databases = 'USER_DATABASES,-ReportServer,-ReportServerTempDB,-SSISDB', @Directory = '\mypath$\Tempdump', @compress = 'Y', @BackupType = 'FULL',@DirectoryStructure = Null, @filename = '{ServerName}{InstanceName}{Year}-{Month}-{Day}.{FileExtension}', @MaxTransferSize = 4194304, @BufferCount = 32, @LogToTable = 'N', @execute = 'Y', @copyonly = 'Y'

Here is the error.

Date and time: 2025-01-08 12:31:53
Database context: [master]
Command: BACKUP DATABASE [ASM_SD] TO DISK = N'\mypath$\Tempdump\ESPR1SQLX_I1_2025-01-08.bak' WITH NO_CHECKSUM, COMPRESSION, COPY_ONLY, BUFFERCOUNT = 32, MAXTRANSFERSIZE = 4194304
Msg 3266, Level 16, State 1, Line 1
The backup data at the end of "\mypath$\Tempdump\ESPR1SQLX_I1_2025-01-08.bak" is incorrectly formatted. Backup sets on the media might be damaged and unusable. To determine the backup sets on the media, use RESTORE HEADERONLY. To determine the usability of the backup sets, run RESTORE VERIFYONLY. If all of the backup sets are incomplete, reformat the media using BACKUP WITH FORMAT, which destroys all the backup sets.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Outcome: Failed
Duration: 00:00:00
Date and time: 2025-01-08 12:31:53

After the first error also received the error below:

Date and time: 2025-01-08 12:32:00
Database context: [master]
Command: BACKUP DATABASE [AT37] TO DISK = N'\mypath$\Tempdump\ESPR1SQLX_I1_2025-01-08.bak' WITH NO_CHECKSUM, COMPRESSION, COPY_ONLY, BUFFERCOUNT = 32, MAXTRANSFERSIZE = 4194304
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device '\mypath$\Tempdump\ESPR1SQLX_I1_2025-01-08.bak'. Operating system error 32(The process cannot access the file because it is being used by another process.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Outcome: Failed
Duration: 00:00:00
Date and time: 2025-01-08 12:32:00

Thanks in Advance for any resolution.

Mark

[olahallengren]

I have just tested this with the same parameters with no issues. I was testing on SQL Server 2022 CU16.

Could you check your version using SELECT @@VERSION?

[MWHeier]

Microsoft SQL Server 2016 (SP3-CU1-GDR) (KB5046856) - 13.0.7050.2 (X64)
Oct 14 2024 22:01:52
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)

[Stiffboard]

Feels like Cannot open backup device cause the backup to not entirely complete and making the file corrupt. Any antivirus or similiar that interferes with the backup. Realtime scanning at destination.

[olahallengren]

I have tested it on SQL Server 2016 SP3 with no issues.

@MWHeier, could you try running it to a local directory on the server?

[MWHeier]

I switched to just performing individual backup files for each database and that is working for me. I do believe we have a File Services issue on are backup storage.