What are the challenges you face when working across database platforms? Take the survey
Options

Error message or not

gsc_ghxgsc_ghx Posts: 73
edited March 26, 2008 8:56PM in SQL Backup Previous Versions
I have backups that report an error but when I look at the job log - it shows this:
SQL Backup log file
3/25/2008 5:00:00 AM: Backing up dba (full database) to:
3/25/2008 5:00:00 AM: \\SQL_Land\db-d-acc-01\dba_FULL_RG.bak

3/25/2008 5:00:00 AM: BACKUP DATABASE [dba] TO DISK = '\\SQL_Land\db-d-acc-01\dba_FULL_RG.bak' WITH NAME = '<AUTO>', DESCRIPTION = '<AUTO>', INIT, COMPRESSION = 1

3/25/2008 5:02:11 AM: Thread 0 error:
Error 622: Error flushing contents of backup file(s).
3/25/2008 5:02:11 AM: Warning 215: Thread 0 warning:
Warning 215: Error flushing buffer contents to file: \\SQL_Land\db-d-acc-01\dba_FULL_RG.bak
Warning: System error (The specified network name is no longer available)


Then it shows this section ---- Does this mean the backup WAS successful?

Processed 4248 pages for database 'dba', file 'dba_data' on file 1.
Processed 6 pages for database 'dba', file 'dba_log' on file 1.
BACKUP DATABASE successfully processed 4254 pages in 31.188 seconds (1.117 MB/sec).


If it was successful how do I capture the success and not the failure of this job? I get alerted that the job has failed but this leads me to believe that it was able to backup the db on a second try.

Thank you... :roll:

Comments

  • Options
    peteypetey Posts: 2,358 New member
    It is a failure. I would strongly suggest you perform a full backup of the dba database as soon as possible, if you have not already done so.

    This scenario occurred as SQL Server had already passed all the backup data to SQL Backup. To SQL Server, the backup has completed, and it is able to report the backup statistics.

    However, SQL Backup doesn't immediately write all backup data to disk immediately. It needs to compress and consolidate them in chunks. In this case, SQL Backup failed to write the final chunk out to disk, as the network connection is no longer available. The backup file is most unlikely usable.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    so because it has passed all the info to sql server before it really knows it can't access the backup location - sql server thinks it is successful. But my SQL server is alerting me to a failed backup and the SQL Backup log is showing the success.

    Does SQL Backup have a retry and it fails first then is successful but by this time it has informed SQL SErver that it has failed?
    Thanks
  • Options
    peteypetey Posts: 2,358 New member
    >> But my SQL server is alerting me to a failed backup ...
    You mentioned "I have backups that report an error ...". The error is reported by SQL Backup, not SQL Server.

    >> ... and the SQL Backup log is showing the success.
    The SQL Backup log is showing this:

    3/25/2008 5:02:11 AM: Thread 0 error:
    Error 622: Error flushing contents of backup file(s). ...

    which are errors.

    SQL Backup does not have a retry function in such cases.

    See here for an overview of the interaction between SQL Server and SQL Backup. Notice that in the last diagram, SQL Server passes data to SQL Backup for processing. When that has completed, SQL Server reports the backup statistics. However, writing to disk is performed by SQL Backup.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    Thank you again Petey. this is very helpful. I do appreciate it.
    :P
Sign In or Register to comment.