Log Shipping Problem

I have a warm standby 2003 Server and am trying to set up log shipping. I am
working with SQL Server 2005 and have created the log shipping jobs using the
wizard. Initially as suggested in the documentation, I backed up the primary
database using norecover options. I resored the database on the secondary to
overwrite the existing database with recovery and standby options which has
left the secondary database in Standby/Read-Only mode. All the the log
shipping job history on the primary and secondary servers show success (even
the restore on the secondary). When I insert records into tables on the
primary I do not see them appear in the secondary database after the restore
job executes. I don't have much experience with log shipping or partial
restores from log files for that matter, I have always done full restores
with no recovery. Why don't I see the new records in the secondary database
after a successful restore (according to job history). I can see the .trn
files in the shipped log file directory, how does this process work, does it
take a restore with norecovery before you can see them in the secondary
database? There is nothing in the documentation that indicates this, I'm
missing something here ... I guess it has something to do with partial
restores. What am I doing wrong.
Fran Morabito wrote:
> I have a warm standby 2003 Server and am trying to set up log shipping. I am
> working with SQL Server 2005 and have created the log shipping jobs using the
> wizard. Initially as suggested in the documentation, I backed up the primary
> database using norecover options. I resored the database on the secondary to
> overwrite the existing database with recovery and standby options which has
> left the secondary database in Standby/Read-Only mode. All the the log
> shipping job history on the primary and secondary servers show success (even
> the restore on the secondary). When I insert records into tables on the
> primary I do not see them appear in the secondary database after the restore
> job executes. I don't have much experience with log shipping or partial
> restores from log files for that matter, I have always done full restores
> with no recovery. Why don't I see the new records in the secondary database
> after a successful restore (according to job history). I can see the .trn
> files in the shipped log file directory, how does this process work, does it
> take a restore with norecovery before you can see them in the secondary
> database? There is nothing in the documentation that indicates this, I'm
> missing something here ... I guess it has something to do with partial
> restores. What am I doing wrong.
>
I don't know anything about the log shipping wizard, I've always rolled
my own log shipping routines. It's a pretty simple process:
1. Start with a full backup of the live database
2. Restore full backup to standby using WITH NORECOVERY option
3. Schedule frequent transaction log backups of the live database,
preferrably writing each backup to a seperate file
4. Monitor for new transaction log backups, when found, restore to
standby using WITH NORECOVERY option.
I would start by making sure your transaction log backups are occurring
on the live server, and that they are being restored on the standby server.
Tracy McKibben
MCDBA
http://www.realsqlguy.com