Restore failed with "T-command" error

1

I’m trying out the sqlbackupandftp, but 1 out of two database failed with T-command error.
there is no error when sqlbackupandftp perform the backup.
when restoring it trigger this error.

i try on other sqlbackup program, no issue for both backup & restore…

can anyone suggect to me , either this is the database self-error, or sqlbackupandftp error?
Thank you.

[quote begin]
29/08/2022 17:23:20 [Error] Failed to restore “YLH-SERVICE-KIA” database. T-SQL command error: INSERT [dbo].[JobSht] ([GUID], [JSID], [JOBSHEETNO], [COMPANYID], [EXTNO], [TYPE], [DATE], [TIME], [BILLTYPE], [BILLNO], [BILLDATE], [DONO], [DODATE], [PONO], [SERVICECOUPONNO], [VOUCHERNO], [ACCOUNTSID], [ACCOUNTSNAME], [VEHICLEID], [REGISTRATIONNO], [CHASSISNO], [GOVERNMENTVEHICLE], [CUSTOMERID], [CUSTOMERNAME], [MECHANICID], [FOREMANID], [SERVICEADVISORID], [MEMBERSHIPNO], [PRODUCTIONNO], [MILEAGE], [CUSTOMERTYPE], [DATEOUT], [TIMEOUT], [ESTIMATEDDAYTOCOMPLETE], [ESTIMATEDTIMETOCOM… ‘, 40, 760079269, N’ ', 0, 0, 80821, 3930917, N’KelvinL ', 80821, 4041224, N’KelvinL ', N’1 ', N’1 ‘, N’ ‘, N’0 ‘, N’ ‘, N’ ‘, N’ ‘, N’ ‘, NULL, N’ ‘, N’$$990KrvMsFMli9T’, NULL, NULL, N’0 ‘, N’ ‘, N’ ', NULL, NULL, N’0 ‘, N’ ‘, CAST(0.00 AS Decimal(7, 2)), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N’ ', NULL, NULL, 0, 0, 0)

String or binary data would be truncated.
The statement has been terminated.

[quote end]

2

Hello,

To resolve the issue, please try to set local SQL Server connection and check if it works for you?

If you have any other questions, please let us know.

Thank you and sorry for the inconvenience.

3

Thank You Alexander for the feedback.

I had did a try to restoring to local server (Server-B it self) but end up with another error of SQLBackupandFTP can’t find/locate the backup file (source)…
It seem like SqlBackupAndFtp from “different” installation can’t see the backup create by another SqlBackupAndFtp installation? (just my guessing)

below is my setup :
Server-A :
MsSql Database1, Database2
OS : Win 11 Pro

Server-B :
MsSql Database1, Database2
OS : Win 11 Pro

Workstation-C :
LAN Share : MsSql Backup Location
OS : Win 10 Pro

1.) Backup Stage :
lunch backup job on Workstation-C, Create backup from remote Server-A, save to local.

2.) Restore Stage :
lunch restore job on Workstation-C, Read backup from local, restore to remore Server-B.

This is where the T-Command error occur only to one out of the two databases.

Trying restore to local instead of remote
3.) on Server-B, lunch restore job, read backup from Workstation-C, restore to local server.

At here the sqlbackupandftp report as below

[Quote Begin]
03/09/2022 11:30:14 [Error] Failed to restore “YLH-SERVICE-KIA” database. Downloaded files does not contains valid database backup file.

[Quote End]

Will do few more testing…

p/s: how do I attach log file here?

4

update:

the installation (sqlbackupandftp) on workstation-C can see the backup file (*.sql)
this still trigger T-command error only one specific database.

but the installation (sqlbackupandftp) on server-B can’t (seem like) see the backup file (*.sql)
hence it reported “no content”

update : 2022Sep06
not sure what happend,
1.) Re-install the program on Server-B,
2.) run & create backup without compression (save as *.sql) from Server-A database, store at Workstation-C
3.) run restore , read from Workstation-C, but program “can’t” detect the .sql backup just create. it only detect compressed (.zip) file.

5

Dear Alexander,

I done few tests & summarise as below :

Backup create from local database will had *.bak extension
Backup create from remote database will had *.sql extension
Restore to local database will only accept *.bak extension backup
Restore to remote database will only accept *.sql extention backup

this explain why when restore from remote backup to local database trigger error of “does not contain a valid database backup file”.

since restore from *.bak backup file doesn’t trigger T-Command error,
hence can conclude that the database have no error.
& the error should be come from SqlBackupAndFTP program at certain state (only that specific database we had here) when it involved *.sql extension backup.

as above, would you able to suggest solution/fix for me to resolve the T-Command error?

TQ
Billy

6

Hi Billy,

SQLBackupAndFTP does not generate a *.sql file on its own, but uses standard libraries from SQL Server developers. For a restore process, SQLBackupAndFTP also uses standard mechanisms for working with SQL Server.

If your backup file has been created via the remote connection, and it cannot be restored, then the issue is related to SQL Server, and we can do nothing here.

In this case, you can try to fix your *.sql file by yourself. Or simply use the local connection.

If you have any other questions, please let us know.

Thank you and sorry for the inconvenience.

7

Hi Alexander,

Noted and thanks for your reply.

because the servers will be swapped from time to time, hence we have to do the backup & restore from remote station.
local connection will not going to auto detect the master/salve swap, will end up backup from slave into the master…

I’m studying on writing ps script see if can solve the error or not.
Also looking to migrate the SQL Server to latest version, it is currently on 2014 version.

Thanks
Billy

8

Hi Billy,

Thank you for your reply and for the details.

Please feel free to contact us on any issues or concerns.