Competition: What’s your favorite Redgate tool? Enter now.

Setting up Log Shipping - stuck on "Restore database"

ehunaehuna Posts: 15
edited December 5, 2006 9:17PM in SQL Backup Previous Versions
I am setting up Log Shipping using the SQL Backup UI (not the command line or extended stored procs directly).

When I get to the "Performing tasks" step, the first step works, but it now seems stuck on the second step. Here's what I see:

Backup database [DATABASENAME] on [SOURCECOMPUTERNAME] - Completed
Restore database [DATABASENAME] on [DESTINATIONCOMPUTERNAME] - Restored ... 535.188 MB
Create backup job on [SOURCECOMPUTERNAME] - Pending
Create restore job on [DESTINATIONCOMPUTERNAME] - Pending

I can see that on the source server the full backup was copied to the "Backup folder" but still remains in the network share folder. I can also see that the backup was logged properly in the SQL Backup log directory. This log file says that the file size is:

12/4/2006 12:03:51 PM: Backup data size : 538.813 MB
12/4/2006 12:03:51 PM: Compressed data size: 253.990 MB
12/4/2006 12:03:51 PM: Compression rate : 52.86%

I can also see on the destination server that the full backup was copied to the "Processed" folder. But I don't see that anything was logged in the SQL Backup log directory.

Why is SQL Backup stuck (now for over 2 hours) on this step? The progress was good, with a few MBs every few minutes but it seems stuck now. I don't see any errors in the Windows' "Application" event log.

What should I do to make this work? If I just kill SQL Backup, the jobs to backup and restore won't be created properly.

I know I can set it up manually using the command line of the extended stored procs. But that will be more complicated and a lot more work and I don't need the extra flexibility.

Comments

  • peteypetey Posts: 2,358 New member
    On the destination server, try to restore the database manually, using the same MOVETO option that you set up using the Log Shipping Wizard. Do you encounter any errors?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • There were no errors. So I tried going through the Log Shipping wizard again and this time was very patient. I let it run overnight and in the morning I had the four checkboxes and all four tasks were completed.

    I am setting up log shipping on a production environment where we have a standby system. This standby system is in a different city and we have a VPN between the two systems.

    Although we are assured 1 Mbps simetric between the two systems, sometimes it can be a lot slower.

    I think a possible feature request for future versions of SQL Backup would be more feedback on the restore status. The restore worked for an hour fine, where every few seconds I could see a few MBs had been transferred.

    But when it got to 99% or even 100% of all data being restored, it literally just sat there for hours. A link with more detailed steps or something that would give me the assurance that SQL Backup didn't just die or is not responding would be great.

    Otherwise, how would I know how long I should wait before panic kicks in and I have to kill SQL Backup?
  • peteypetey Posts: 2,358 New member
    Are the network share and 'MOVETO' folders located nearer the primary or standby server?

    It may explain why the restore was so slow. SQL Backup may be trying to move the file from the network share that was nearer the primary server, to the MOVETO folder that's nearer the standby server. The copy process may be taking a while.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.