Setting up Log Shipping - stuck on "Restore database"
ehuna
Posts: 15
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.
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
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
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?
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.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8