We have a customer who is backing a 61gb database from a primary server to a secondary server by running SQL backup and dumping the backup file to the secondary server and then restoring the database on the secondary server. the secondary server is used to run reports. The backup/restore was working fine but has stopped working. Looking at the job history in SQL Enterprise manager, they are receiving the following error when backing up on the primary machine -
SQL Backup job failed with exitcode: 5240 SQL error code: 0 [SQLSTATE 42000] (Error 50000). The step failed.
The problem seems to have started after they had an incomplete backup due to a power failure. The restore job on the secondary server never completed because of this and left the DB on the secondary server in the loading state. SQL server was restarted and the DB was restored from the last good backup. Since this time, the backup on the primary server keeps failing. They also do a regular SQL Server backup in the middle of the night. That backup is running with no problems.
Could you pls try restarting the SQL Backup Agent service on the primary server?
SQL Backup job failed with exitcode: 5240 SQL error code: 0 [SQLSTATE 42000] (Error 50000). The step failed.
The problem seems to have started after they had an incomplete backup due to a power failure. The restore job on the secondary server never completed because of this and left the DB on the secondary server in the loading state. SQL server was restarted and the DB was restored from the last good backup. Since this time, the backup on the primary server keeps failing. They also do a regular SQL Server backup in the middle of the night. That backup is running with no problems.