Restore Database for Log Shipping

[Former Member]

Hi Experts,

There is a plan to configure Log Shipping for Live system running on MS SQL Server 2005.

I have a backup taken 3 days back from Live(primary) system, planned to restore it in Secondary system in standby mode.

These 3 days lot of transactions happened, can i continue with Log shipping configuration or have to restore Transaction logs also to make primary and secondary database sync and then proceed with Log shipping configuration.

If i restore database in standby mode, then it is not possible to restore transaction logs. So I have restore it in NORECOVERY mode, apply Tlogs then convert it to standby database. Is this process is correct ?

Pls give some inputs

Regards

KT

[Former Member · Accepted solution]

There is no need to make another backup as long as all logs are available.

You can restore the secondary database either WITH NORECOVERY or WITH STANDBY.

The only difference is that WITH STANDBY makes the database open for read-only.

After the restore, you can setup the primary and secondary databases for log shipping (done from the primary database).

Once this setup is done you are able to switch the roles of the databases without any backup's at all.

You only have to "break" the log shipping and create a "tail log" from the primary database, thus putting that database in NORECOVERY.

On the secondary database, apply the last logs.

Disable the copy job on the primary database and the copy + restore job on the secondary database.

Manually copy and apply the "tail log" on the secondary database.

Leave the NORECOVERY mode with command: restore database xxx with recovery.

Setup primary and secondary log shipping databases and the scenario has be turned around without any new backups.

Read more about it at [Log Shipping|http://msdn.microsoft.com/en-us/library/ms190016(SQL.90).aspx]

[Former Member · Accepted solution]

Hi all,

Thanks for all the comments.

Issue had been resolved.

Just restored the backup and applied the 3 days .trn log files to the Secondary server in NORECOVERY mode.

After that configured log shipping and i checked from yesterday log started shipping and getting restored in Secondary Server

Regards

KT

[Former Member]

KT,

I'd suggest to create a new Full backup and restore it to the secondary server (if that's not a big deal). You can have your database on the secondary server either in stand by or in recovering state.

I don't know your requirements, but you might want to check out database mirroring in SQL 2005.

Regards.