Restore

NewDBAGirl

I have log shipping set up on several databases that have been running for months w/o a problem. Today one of the restores are throwing errors. It just started a few hours ago. When I try to view the properties of the database through Enterprise Manager, it is saying Error 927 - Cannot be opened. It is in the middle of a restore.

Does this sound familiar to anyone? I'm wondering why this one restore would have broken suddenly. Could the last restore just be hanging there?

Thanks.

Brian Donahue

Hi,

I know that you cannot set up a log shipping restore script and specify a stopat time. To do this, you would have to bring up the database WITH RECOVERY. I think you can get a 927 if you specify a STOPAT parameter in the job script without restoring WITH RECOVERY, so you would want to remove any STOPAT command from the backup job script.

NewDBAGirl

I'm sorry if you missunderstood. I didn't do anything. I didn't set a STOPAT command. The restore just stopped functioning today. I didn't touch anything. It has been working fine for 2-3 months now.

Just wondering if you all had any ideas on why it would just stop functioning.

Brian Donahue

That was one possible reason, but I think to find the reason rather than guessing at it, checking the SQL Backup activity logs would probably point out the cause. By default, you find them on the SQL Server at %allusersprofile%\Application Data\red gate\sql backup\logs.

You probably get the message from Enterprise Manager because during log shipping, the database is not available because the restore is running WITH NORECOVERY, so the database is in a state where it needs to have more data restored before it's usable. For instance, suppose you have a full and a log backup, and you want to restore both of them. You restore the full WITH NORECOVERY so that you can apply the log backup as well. At this point, the database can't be used. But then you restore the log backup WITH RECOVERY and the database will be online.