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

Exit code 300 puzzle

KHartmanKHartman Posts: 5
edited November 10, 2006 11:49AM in SQL Backup Previous Versions
I am having a problem with my full backup routine which errors as follows..

SQL Backup job failed with exitcode: 300 SQL error code: 0 [SQLSTATE 42000] (Error 50000). The step failed.

From other postings I understand the contiguous memory issue this refers to and my backups seem to complete on the 1st or 2nd retry. However, I am unable to restore anything from these backups.. Step 2 of the restore process seems to read the file information properly, LSN, Backup date, size, name, etc... however, when I procede to step 3, I get a pop up that says "Failed to read file list from backup file.", and I am unable to procede from that point.. This happens whether I am restoring from a backup from the native db backup list on step 1, or if I am using another database file to restore to a different database name.

I would attempt to defrag the backup drive, however, these backups all store to a shared drive structure on a different host, and the other servers using the same share drive are NOT failing, and restore just fine.

Here is a snap of the memory profile from the error log...

11/2/2006 8:00:02 PM: Memory profile
11/2/2006 8:00:02 PM: Type Maximum Minimum Average Blk count Total
11/2/2006 8:00:02 PM:





11/2/2006 8:00:02 PM: Commit 879951872 4096 961643 1948 1873281024
11/2/2006 8:00:02 PM: Reserve 67096576 4096 538089 414 222769152
11/2/2006 8:00:02 PM: Free 2621440 4096 153796 334 51367936
11/2/2006 8:00:02 PM: Private 879951872 4096 1248266 1576 1967267840
11/2/2006 8:00:02 PM: Mapped 4128768 4096 531309 98 52068352
11/2/2006 8:00:02 PM: Image 9850880 4096 111502 688 76713984

Any ideas?

Comments

  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hello Kevin,

    This has happened before in the past when trying to restore a SQL 2005 database onto a SQL 2000 Server. This operation won't be allowed by SQL Server. Of course the error message isn't terribly intuitive.
  • Options
    Thanks Brian...

    Well, I guess that makes sense, however, these are all SQL2000 databases... Over the weekend, I did a defrag of the share drive, and moved the "free" segment from 2.3M to 3.7M, but since the first threshold is 6MB according to what I have read, I have the same results on a test backup. I still can't figure out why the other servers backing up on the same share to a different folder seem to be working fine...

    Kevin
  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hello,

    Regarding the restore operation and the 'failed to read file list' error: Does the SQL Backup Agent service logon have rights to the share? If you are restoring from a different server other than the one that you backed up from, then keep in mind that the permissions need to be aligned for the SQL Backup Agent that is running on that particular server.
  • Options
    Yes, the agent has full rights to the share. The SQLagent, BACKUPagent, and SQLSERVERagent all use the same domain ID. Essentially the same config used on the other servers that backup to the same share drive.

    I have done some more experimentation as well in the mean time... I can successfully reroute a backup from one of the other servers to the shared folder in question, and restore that to the original database. However, I CAN NOT restore that same backup to a database on the problem server. I also tried to restore a backup from the problem server to a test database on another server, and was able to do that successfully.

    Everything seems to point to a problem somewhere on the database server in question. The share seems fine, the agents are synched and have permissions, backups to and from other servers are fine, backups from other servers to the share folder of the server in question seem fine, all SQLServer versions are the same, and while the backup job "fails" the backups seem to be writing and completing with this "300" warning in the log.

    The only thing I can't do is restore any database on the problem server, which pretty much makes the whole backup routine useless for that environment. I even blew away all the backup jobs and rebuilt them, only to achieve the same results. I am thinking now of re-installing the RedGate on that server, but that seems rather drastic for something that was working fine and now isn't.

    Any other ideas?
  • Options
    peteypetey Posts: 2,358 New member
    What is the result when you run a RESTORE HEADERONLY command from Query Analyzer/SSMS against the file e.g.

    sqlbackup '-sql "RESTORE HEADERONLY FROM DISK = [...] " '
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    When I run this against my db from QA, this is my result... not finding the stored procedure....

    Statement
    sqlbackup '-sql "RESTORE HEADERONLY FROM DISK = [E:\FULL_(local)_XXXXXKLH_20061110_075654.sqb] " '

    Result
    Server: Msg 2812, Level 16, State 62, Line 1
    Could not find stored procedure 'sqlbackup'.

    Unless I am running this wrong..
  • Options
    The sqlbackup stored procedure is in the master database. So master..sqlbackup should be used. If you still get this error once you have run it using master..sqlbackup then please let us know.

    Thanks,

    - James
    James Moore
    Head of DBA Tools
    Red Gate Software Ltd
Sign In or Register to comment.