SQL Server Performance Forum – Threads Archive

Restore terminated abnormal – partial db restore

Hi, 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. 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: Cannot associate files with different databases. [SQLSTATE 42000] (Error 5173) RESTORE LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013) 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) 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. 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… Then I run me restore Tlogs job again (but just the restore part) and get the following error: 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] (Message 4320) RESTORE LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed. Any advice…? Regards Hugh

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.

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′

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.

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.

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.

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

the site didn’t notify me of these posts, so i only found them 2 days ago by chance… twight, 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… you got it right on the spot ! what a strange error, and even stranger that no-one (but you) ever enountered it before ! 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=’

‘ /> 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 ! thanks for the advice guys ! regards hugh

I got the same error. But no solution so far.
Please reply to the post if anybdy has got any solution for this.
http://sql-server-performance.com/Community/forums/p/25505/141097.aspx#141097
Regards
Sachin

Be patient and posting same question referrence on different threads would create confusion, refer to http://sql-server-performance.com/Community/forums/p/25505/141097.aspx#141097 and followup.

]]>