How to make SQL server 2005 logshipping secondary database to re-sync with primary database again

Jephe Wu - http://linuxtechres.blogspot.com

Objective: to make secondary db server to sync with primary again, logshipping is not working due to some required log files were deleted by some job.
Environment: Windows 2003 32bit, MS SQL server 2005 (management studio)

Problem: logshipping is not working according to Windows event log and sql job history, due to some required files were deleted, so the restoring is not working.



Steps:
1.  disable restoration job in job agent from SQL management studio
SQL Server Agent -> Jobs - LSRestore_DBxxxx\DBRSQLP4_xxxxx

 also, disable any other sql server agent job which might affect restoration process.
2.   restore the full database backup from primary

a. right clicking database DR_DB1 on secondary server.
b. choose 'task', 'restore database', General, source for restore, from device, add a file which is copied from production daily full backup, tick it to choose it.
c. go to options, choose 'overwrite the existing database', do not care about 'Restore the database file as' part even the 'Restore As' name is not same as what the DR server is having now for that database.
Because we have already choosen 'Overwrite the existing database', so don't have to care, it will overwrite the existing database name in DR server.

For 'Recovery state', choose the second options which is 'Leave the database non-operational, and do not roll back uncommitted transactions, Additional transaction logs can be restored (RESTORE WITH NORECOVERY), then click 'OK'.

After finishing it, the restore job will start it automatically and database will automatically try to find the transaction log file to recovery . 

5. right click on the restoratin job to check history, It will check all transaction log files in the specified directory, skip those which already contained in the restored database.  click refresh and get more information.
6. check reports for transaction log.

References:
1. Index rebuild generate huge log file makes logshipping and mirroring out of sync - http://connect.microsoft.com/SQLServer/feedback/details/352338/index-rebuild-generate-huge-log-file-makes-logshipping-and-mirroring-out-of-sync

2. How to: Remove Log Shipping (SQL Server Management Studio)
This topic explains how to remove log shipping using Microsoft SQL Server Management Studio.
To remove log shipping

    Right-click the database you want to use as your primary database in the log shipping configuration, and then click Properties.

    Under Select a page, click Transaction Log Shipping.

    Clear the Enable this as a primary database in a log shipping configuration check box.

    Click OK to remove log shipping from this primary database.

=============
Part II - after DR test, reconfiguring logshipping 

You don't have to delete database on secondary, just re-configure everything from primary; if you don't have enough space, you can delete it.

Steps:

1. right click database on primary, transaction log shipping , click 'enable this as a primary atabase in a log shipping configuration
2. click backup settings.type in network path of the folder on primary where we will generate transaction log for sharing with secondary for read
\\pridb\logshipping
D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\backup\logshipping

note:
a. grant read/write permission to the sql server agent service account of primary server instance so that the backup job on primary can delete old transaction log files successfully.
b. read only permission to sql server agent service account for the secondary server instance in the domain
c. grant sysadmin privilege to primary db sql server agent service account on secondary db, so that the logshipping status 'last backup created' can be updated on primary db , also on secondary db server log shipping report page, Backup - Time Since Last will also be updated. Otherwise, it will only show the initial primary db backup date during logshipping configuration wizard.

in Schedule, to change default interval from 15 minutes to whatever you need. (e.g. 2 mins)

3. click 'add' to add secondary server instance, click connect to choose secondary server name and windows authentication.
click to choose a existing secondary database or enter a name to create new database. e.g. 'DR_PRODUCTION'

in 'Initialize Secondary Database' tab, choose one of three options, if you have recent full backup, choose the second option, then type in a

network path to the backup file that is accessible by secondary instance, e.g. \\primdb\backup\PRODUCTION_backup_201205150110.bak
or
after copying file to secondary, use \\secdb\d$\PRODUCTION_backup_201205150110.bak
Note: you cannot directly use d:\PRODUCTION_backup_201205150110.bak since it's asking network path, otherwise, you are not able to add it once clicking 'add'.

click 'restore options', type in data and log files path on secondary server: e.g.
D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\PRODUCTION
D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\PRODUCTION

in 'copy files' tab, type in  destination folder on secondary where transaction log should be copied to
e.g. D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\logshipping

click on 'Schedule', change default 15 minutes interval to whatever you need (e.g. 2mins)

click on 'Restore Transaction Log' tab, choose 'no recovery mode' or 'standby mode' (readonly while applying transaction log).

choose 45 minutes for alert if no restore occurs within.

Note: you can also restore the full backup from secondary database first by right clicking 'database', choose 'restore', in 'options', choose the second option to leave database in non-operational state to be able to apply for transaction log and do not undo the uncommited transaction.

4. in Monitor server instance, choose secondary server.

5. user zabbix/dbforbix to monitor logshipping progress
refer to http://sqlmonitormetrics.red-gate.com/time-since-last-restore/

Measure the time since the last restore, in minutes.
Enter the T-SQL query that will collect data:

SELECT DATEDIFF(MINUTE, restore_date, GETDATE())
  FROM (SELECT TOP 1 restore_date
          FROM msdb.dbo.restorehistory
          WHERE destination_database_name = DB_NAME()
          ORDER BY restore_date DESC

       ) rd;