Script Transfer Failed 1 of 2 backups SAME CONNECTION

Application issues
1

i tried taking the offending database off line, then back online to clear all connections. No help there.

The connection is to an AWS Lightsail VM running SQL Server, connection on port 1444 (not standard 1433).

Very odd that it can connect and grab 1 database, but the other fails on the same connection.

Below is the abbreviated info, here is the advanced log file URL.
Sql Backup And FTP

Thank you!

9/2/2021 1:30 PM Backing up “Tats” with “Full” backup type.
9/2/2021 1:30 PM Failed to backup “Tats” database with “Full” backup type: One or more errors occurred. > Script transfer failed.
9/2/2021 1:30 PM Backing up “IncomeExpense” with “Full” backup type.
9/2/2021 1:31 PM Database “IncomeExpense” successfully backed up to “C:\Windows\TEMP\SYSTEM\Pranas.NET\SBF\b69c62ad-3fec-4da0-8ff1-a0fc1cc2e87e\backup\IncomeExpense202109020630.sql : 12.182MB”.

2

i’ve resolved this problem. The detailed log showed:
Microsoft.SqlServer.Management.Smo.FailedOperationException: Script transfer failed. —> Microsoft.SqlServer.Management.Smo.FailedOperationException: Discover dependencies failed.

While dependencies that referenced missing DBs seemed to be no problem, a single dependency referenced a database that was offline (SSMS shows “dbName (Offline)”).

This resolution worked for me…

1. locate the ID of the database that failed.

SELECT database_id, name FROM sys.databases
WHERE database_id > 4

2. Find the dependencies (important - FROM dbName below!):

DECLARE @DBid int = 5 – the id from above

Select
FromDB = DB_NAME(@DBid),
FromSchema = OBJECT_SCHEMA_NAME(referencing_id,@DBid),
FromObject = OBJECT_NAME(referencing_id,@DBid),
referenced_server_name,
ToDB = ISNULL(referenced_database_name, db_name(@DBid)),
ToSchema = referenced_schema_name,
referenced_entity_name
FROM [Tats].sys.sql_expression_dependencies
WHERE ISNULL(referenced_database_name, db_name(@DBid)) <> DB_NAME(@DBid)
Order By ISNULL(referenced_database_name, db_name(@DBid))

3. Eliminate the dependency, or put the dependent DB back online…
In my case, DB’s that were missing were not causing the trouble. Only the “Offline” DB caused my issue.

3

Hi Barry_Tucker,

Good news! Thank you for your reply!