SQL Server Performance

Restore terminated abnormal - partial db restore

Discussion in 'SQL Server Log Shipping' started by HyIsDieBaas, Nov 10, 2004.

  1. HyIsDieBaas New Member

    Hi,<br /><br />I#%92ve got log shipping running here for a couple of months now. But every now and then the restoring of my log files fail. I run standard edition, so I rolled my own shipping using this site's tutorial.<br /><br />It#%92s the very first Tlogs restore that fails, right after a data restore. The data backup and restore went through fine, and the first Tlog backup thereafter also, but during the Tlog#%92s restore I get the following error:<br />Cannot associate files with different databases. [SQLSTATE 42000] (Error 5173) <br />RESTORE LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013) <br />File ‘MyDB_Data#%92 was only partially restored by a database or file restore. The entire file must be successfully restored before applying the log. [SQLSTATE 01000] (Error 4320) <br />Log file ‘D:MyDB_log.ldf#%92 does not match the primary file. It may be from a different database or the log may have been rebuilt previously. [SQLSTATE 01000] (Error 510<img src='/community/emoticons/emotion-11.gif' alt='8)' />. The step failed.<br /><br />That doesn#%92t make sense to me..., cause the data restore didn#%92t fail? Also, time did pass between the completion of the DB restore, and this first backup of the Tlogs afterwards...<br /><br />Then I run me restore Tlogs job again (but just the restore part) and get the following error:<br />File ‘MyDB_Data#%92 was only partially restored by a database or file restore. <br />The entire file must be successfully restored before applying the log. [SQLSTATE 01000] (Message 4320) <br />RESTORE LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.<br /><br />Any advice...?<br />Regards<br />Hugh<br />
  2. satya Moderator

    Try to restore full database backup from Primary server to Secondary server using WITH REPLACE option in RESTORE statement.



    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. HyIsDieBaas New Member

    thanks for the reply satya.

    this is my current data restore command.

    RESTORE DATABASE @dbname
    FROM DISK = @bakFile
    WITH
    REPLACE,
    STANDBY = @standbyFile,
    MOVE @logicalMDF TO @logicalMDF_path,
    MOVE @logicalLDF TO @logicalLDF_path

    WAITFOR DELAY '00:00:05'
  4. satya Moderator

    So after the database restore does the log shipping restore continues?

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. HyIsDieBaas New Member

    my backup restore job runs from 13h15 for about 1h30min. so it finishes around 14h45. my 1st log restore job is scheduled to begin at 15h00.

    the whole process runs smooth, and then, for no reason i can see, that first job starting at 15h00 fails for the reasons i provided above. it created a backups of the logs fine, and its the correct file copied to the standby server. i check the data restore job history, and it went through fine, no error.
  6. twight New Member

    Hey. I have an idea. I do the same home-brew log shipping you do on about 20 databases.

    Are you using linked servers and remote stored procedures to restore the database on the standby? If you are, you may be running into a problem I ran into. It took a (free because it's a bug) Microsoft support incident to resolve it.

    There is a setting on the Connections tab of the database Properties dialog called "Query Timeout (sec, 0 = unlimited)" that is set by default to 600 (10 minutes). This is the timeout period for remote connections. If your restore takes more than 10 minutes, it will time out, but YOU WILL RECEIVE NO ERROR!!

    Look at your job history for the DB backup. If the amount of time for the restore step is 10:01, then you hit this problem. Up the timeout, and you will be all set.

    Hope this is it.
  7. Lesleyg New Member

    Perhaps I've misunderstood your set up, but you shouldn't have to run a database restore nightly on a secondary server.

    Simply restoring every log backup is sufficient to keep the server up to date and in sync.

    hth,
    Lesley



    Lesley Gilmour
  8. HyIsDieBaas New Member

    the site didn't notify me of these posts, so i only found them 2 days ago by chance...<br /><br />twight,<br /><br />do you know how long i've searched the web and forums for this error. every single person comes back with "you need to add 'with recovery' to your restore"... i have to explain then, no Tlogs can be restored if i add that clause, so i am in a bit of a catch-22 situation...<br /><br />you got it right on the spot ! what a strange error, and even stranger that no-one (but you) ever enountered it before !<br /><br />i upped the time-out limit (on my local server. if i change it on the remote/standby server i get the same issue.. seems strange, should the standby's timeout be upped?) of my server and its working like a charm again ! thanks again and again for this help <img src='/community/emoticons/emotion-2.gif' alt=':D' /><br /><br />Lesley, i understand your point. i will test around with your option and see whether i can save myself a couple hours' DB backups+restores !<br /><br />thanks for the advice guys !<br /><br />regards<br />hugh<br />
  9. sachinsamuel New Member

  10. satya Moderator

Share This Page