InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 01:23:29 GMT20http://www.sqlservercentral.com/Forums/Topic1413261-1549-1.aspxnot quite the problem we had here (we had log backups taken outside of the log shipping jobs) but thanks for the post.Tue, 12 Feb 2013 05:05:59 GMTgeorge sibbaldhttp://www.sqlservercentral.com/Forums/Topic1413261-1549-1.aspxi have same problem and its work after execute this query onsecondary serverfollow this link http://social.msdn.microsoft.com/Forums/en/sqldisasterrecovery/thread/44840af1-0a85-44a9-98a6-8fc2ca559f81Tue, 12 Feb 2013 04:56:30 GMThiren-411325http://www.sqlservercentral.com/Forums/Topic1413261-1549-1.aspx[quote][b]hiren-411325 (2/11/2013)[/b][hr]There is bug in SqlLogShip.exeuse msdbupdate dbo.log_shipping_secondary_databases set [restore_delay] = -2000000000[/quote]I've not come across this, in which version? Under what circumstances would you want to run this and what is its effect? Please explain further.Tue, 12 Feb 2013 02:57:04 GMTgeorge sibbaldhttp://www.sqlservercentral.com/Forums/Topic1413261-1549-1.aspxThere is bug in SqlLogShip.exeuse msdbupdate dbo.log_shipping_secondary_databases set [restore_delay] = -2000000000Mon, 11 Feb 2013 23:07:42 GMThiren-411325http://www.sqlservercentral.com/Forums/Topic1413261-1549-1.aspxHi again,Recently I found two of the databases (Primary and Secondary) on my log shipping were Out Of Sync. Below are the steps I performed to bring it back to Sync:1. Identify the SQL Agent Job on the Primary Database server which is taking Transactional Log Backups of your Database and Disable it. ( This is a very critical step to take in a 24X7 environment , So I would recommend this process to be performed on a non-busy period ; like midnight or so. So that you loose minimum data.) 2. Identify the SQL Agent Jobs on the Secondary Database server that are Copying and Restoring Log Backups. Disable them.3. After Disabling the Agent jobs, Take a Full Backup of the Primary database and Restore it on the Secondary Database, option WITH NO RECOVERY mode. You won't be able to do this in SSMS GUI since the Secondary database is on a STANDBY/READ-ONLY mode and the RESTORE DATABASE options are disabled on the tasks>Restore . USE the T-SQL code instead to perform this step . ( Even a recent Differential backups is enough to perform this Restore if a Full backup has not been taken after the LS OFF-Sync )4. After a fresh Restore has been performed on the Secondary Database, Execute sp_who2 and see if there are any users that are connected to the Secondary Database. Kill the SPIDs connecting to the Database if there are any. Don't have to do this if you have selected "Disconnect users in the database when restoring backups" while you setup the Log Shipping. 5. Re-Enable the SQL Agent Job on the Primary Server that was taking Transaction Log Backup. Right click and click "Start Job At Step". 6. Clear all transactional log files (trn) from the Secondary server's Restore Folder.7. Re-Enable SQL Agent Jobs on the Secondary Database server for Copying and Restoring Log Backups. Right click on the Copy job and click "Start job at Step". Wait for the .trn files to be copied to the Restore Folder. After that, Right click on the Restore Job and click "Start Job at Step".8. You may want to manually restore the last transactional backup file to the Secondary Database with the option (RESTORE WITH STANDBY) on SSMS.Log shipping should now be back IN-SYNC and ready to READ by users. Use Log File Viewer on the Secondary Server to confirm that Restores are Not Failing. Look on the "Message" on the Log file summary , It should say something like this; "The restore operation was successful. Secondary Database ....... Number of log backup files restored: ##some number " You may see some latency between the Live Database and the Secondary Database.May not apply to all environments. ==============================Some Info:Primary: SQL SERVER 2008 R2 Enterprise SP2Secondary: SQL SERVER 2008 R2 Enterprise SP2Secondary Database mode: STANDBY/READ-ONLYTransactional Log Backup: Every 30 mins interval . This is the only log backup taken for the whole database. Running two parallel log backups at same time , BAD IDEA !!Database size: 400GBTotal Time taken : 1 hour.Fri, 08 Feb 2013 15:02:41 GMTSqlSWAThttp://www.sqlservercentral.com/Forums/Topic1413261-1549-1.aspxthanksThu, 07 Feb 2013 13:37:49 GMTgeorge sibbaldhttp://www.sqlservercentral.com/Forums/Topic1413261-1549-1.aspxI have restored the last transactional Log backup with the option of STANDBY on SSMS. The database is now back to ReadOnly / STANDBY. Now I users will be able to read from this database and the Log shipping is IN-SYNC. thanks for all the help guys.Appreciate it. I will put detailed steps here shortly in case anyone needs it later.Thu, 07 Feb 2013 12:49:09 GMTSqlSWAThttp://www.sqlservercentral.com/Forums/Topic1413261-1549-1.aspxOk, Guys the The Restore was successful and the Log shipping is back on sync. I can confirm it by looking at the restore log on the Log File Viewer. It says that the Restore Operation was successful. I have a question though. The database is now on "Restoring Mode" since we used "NO RECOVERY" Any idea how to bring it back to Standby Mode since we want to use this for Read purposes ?thanks.Thu, 07 Feb 2013 12:06:11 GMTSqlSWAThttp://www.sqlservercentral.com/Forums/Topic1413261-1549-1.aspxwell thats out of left field.I don't do replication but I would have thought you would have to reinitialise it from snapshot.Thu, 07 Feb 2013 10:22:19 GMTgeorge sibbaldhttp://www.sqlservercentral.com/Forums/Topic1413261-1549-1.aspxGuys, Is the full restore possible on the Secondary Database while it is being Replicated ? My secondary database for this log shipping is replicated. So do i have to disable the replication before I restore the Full Backup on this database ?Thu, 07 Feb 2013 10:13:58 GMTSqlSWAThttp://www.sqlservercentral.com/Forums/Topic1413261-1549-1.aspxVery much appreciate George and Perry.Thu, 07 Feb 2013 09:56:59 GMTSqlSWAThttp://www.sqlservercentral.com/Forums/Topic1413261-1549-1.aspx[quote][b]george sibbald (2/7/2013)[/b][hr]BTW, you don't need to reconfigure logshipping you can use your existing jobs and set up, just reinitialise the backup.You would be best pausing log jobs, taking a full backup (in copy_only mode if you are worried about your diffs) and just restore that to the secondary in norecovery mode, then re-enable your log shipping jobs. Clear down the log directory on the secondary first if you want.[/quote]+1Thu, 07 Feb 2013 09:06:45 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1413261-1549-1.aspxBTW, you don't need to reconfigure logshipping you can use your existing jobs and set up, just reinitialise the backup.You would be best pausing log jobs, taking a full backup (in copy_only mode if you are worried about your diffs) and just restore that to the secondary in norecovery mode, then re-enable your log shipping jobs. Clear down the log directory on the secondary first if you want.Thu, 07 Feb 2013 08:59:37 GMTgeorge sibbaldhttp://www.sqlservercentral.com/Forums/Topic1413261-1549-1.aspxYes , that's what i thought . But I take Full backups every 3 days and diffs everyday. And the LS is out of sync for like a week now. It may be easier for me to reconfigure the whole log-shipping setup. Doesn't take much time though. Is there anyway i can restore the last full backup + last diff backup + all the trans log backups to the Secondary log shipped database and put it back to sync. Right now the database is in Standby/Read only mode. Appreciate the help...Thu, 07 Feb 2013 08:39:34 GMTSqlSWAThttp://www.sqlservercentral.com/Forums/Topic1413261-1549-1.aspxHave you taken any full backups since LS broke, if not you may restart LS with a differential backupWed, 06 Feb 2013 16:01:54 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1413261-1549-1.aspxHey, ya'll are right guys. Looks like the Restore jobs on the Secondary database is not restored successfully. ( Eventhough it shows that job run successfully ) The sql event log shows that the trn files are skipped while the trying to Restore. I am pretty positive that it has to do something with the transactional log sequence number. The day when I did this log shipping setup there was a log backup job ( our original log backup from the maintenance plans ) still running parallel to our new log shipping transactional log backup . So as soon as I realized that there were two transactional log backups running side by side and interfering with each other's lsn's I disable/deleted the original log backup. But I guess I was too late.Now, I am pretty positive that deleting all transactional log backups and setting up the log shipping from the beginning will solve this problem. I will keep it posted...thanks for all the help.Wed, 06 Feb 2013 09:15:53 GMTSqlSWAThttp://www.sqlservercentral.com/Forums/Topic1413261-1549-1.aspxalso the restore job will report success even if it did not actually restore any logs because it could not find any with correct LSNs.It sounds as if your logs are not restoring. Check your sql errorlog for evidence of logs restoring and run this to tell you last log copied and restoredselect * from msdb.dbo.log_shipping_monitor_secondaryThu, 31 Jan 2013 09:52:26 GMTgeorge sibbaldhttp://www.sqlservercentral.com/Forums/Topic1413261-1549-1.aspx[quote][b]sdpages (1/29/2013)[/b][hr]Hello all,I have a SQL log-shipping High availability setup from a Primary Server to a Secondary Server which ships transactional logs every 1 hour. Both the databases are running on SQL Server 2008 R2. According to my understanding every changes that are commited on the Primary Server should reflect on the Secondary Server. Today I ran Redgate SQL Compare to compare the two databases and discovered 38 objects which are inconsistent between the two servers. So I waited for 1 hour for the next restore to be completed on the secondary server. Even after all the log backups have been restored to the Secondary server, the two databases are not identical.I created a test table on the primary database to see whether it will be created by the restores on the Secondary server, but it is not created even after many restores..Hourly backup is taken without any errors, and None of the transactional logs on the Secondary server is failing.Is there any fault on my Log shipping setup ? Where to start troubleshooting ?What might be the case ? Please suggest..[/quote]Check the jobs carefully and inspect the output of each step, i'm betting that the restore job is showing errors restoring files. Post back what you find.Thu, 31 Jan 2013 09:11:20 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1413261-1549-1.aspxHello all,I have a SQL log-shipping High availability setup from a Primary Server to a Secondary Server which ships transactional logs every 1 hour. Both the databases are running on SQL Server 2008 R2. According to my understanding every changes that are commited on the Primary Server should reflect on the Secondary Server. Today I ran Redgate SQL Compare to compare the two databases and discovered 38 objects which are inconsistent between the two servers. So I waited for 1 hour for the next restore to be completed on the secondary server. Even after all the log backups have been restored to the Secondary server, the two databases are not identical.I created a test table on the primary database to see whether it will be created by the restores on the Secondary server, but it is not created even after many restores..Hourly backup is taken without any errors, and None of the transactional logs on the Secondary server is failing.Is there any fault on my Log shipping setup ? Where to start troubleshooting ?What might be the case ? Please suggest..Tue, 29 Jan 2013 12:42:15 GMTSqlSWAT