Restoring last SQL transaction log

Question

Restoring last SQL transaction log

Urbel 665

Hi expert,

I've ended my large DB from Log shipping process and it has many transaction log files created,

and now how to make my DB on secondary instance taking online?

what is the steps should I do?

great thanks & appreciation for all reply

warmest regards,

UrBel

0 comments

2 answers

Your answer

Answer 1

Step 1 - Remove log shipping configuration

On the primary, run:

EXEC sp_delete_log_shipping_primary_secondary
    @primary_database = N'YourDB',
    @secondary_server = N'SecondaryServerName',
    @secondary_database = N'YourDB';

On the secondary:

EXEC sp_delete_log_shipping_secondary_database
    @secondary_database = N'YourDB';

Or just use the GUI: right-click the primary DB then Properties then Transaction Log Shipping then uncheck, which cleans up jobs on both sides.

Step 2 - Check the current state

SELECT name, state_desc, recovery_model_desc
FROM sys.databases
WHERE name = 'YourDB';

Step 3 - Bring it online with recovery

If you're done applying logs:

RESTORE DATABASE [YourDB] WITH RECOVERY;

If you still have log backups to apply first, restore each WITH NORECOVERY, then run WITH RECOVERY on the last one:

RESTORE LOG [YourDB] FROM DISK = N'path\to\final.trn' WITH RECOVERY;

This is a one-way step, once recovered, you cannot apply more log backups without restoring from scratch.

Step 4 - Clean up after it's online

Take a fresh full backup to start a new backup chain:

BACKUP DATABASE [YourDB] TO DISK = N'path\to\YourDB_new_full.bak' WITH INIT;

Then handle the leftover files:

The many .trn backup files from log shipping can be archived or deleted now. They're no longer needed once the DB is online and you have a fresh full backup.

Answer 2

To bring the secondary database online after log shipping, apply all remaining transaction log backups in sequence, then recover the secondary database.

Steps:

Copy any uncopied log backup files from the backup share to the secondary server’s copy destination folder.
Restore any unapplied transaction log backups to the secondary database in log sequence order.
- In SSMS: right-click the database on the secondary instance, select Tasks > Restore > Transaction Log.
- In the restore dialog, select the log backups in order. The backups must be restored in LSN order.
For the final restore, choose RESTORE WITH RECOVERY.
- This rolls back uncommitted transactions, puts the database into a consistent state, and brings it online.
- Choose this only when there are no more log backups to restore.
If the primary database is still accessible and the goal is a clean final cutover, first back up the active transaction log on the primary and apply that last log backup to the secondary before recovery.
- If the original primary is not damaged, a tail-log backup using WITH NORECOVERY leaves the primary in the restoring state and unavailable to users.
After the secondary is recovered, redirect clients to that server instance.

Important:

Recovering the secondary database ends the restore sequence. Additional transaction logs cannot be restored after WITH RECOVERY.
For a secondary database that is still participating in log shipping, restores normally stay in NORECOVERY. Only use RECOVERY when ready to make that secondary the active database.
Ensure all intended log backups have been applied before the final recovery step.
When making the database available on another server instance, ensure metadata is consistent with the original primary database.