Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Log Shipping scans files but doesn't restore Expand / Collapse
Author
Message
Posted Monday, January 13, 2014 1:00 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 7:41 AM
Points: 41, Visits: 377
I'm trying to setup the restore phase of log shipping, and it doesn't seem to want to restore. Running SqlLogShip.exe in the command line with -VerboseLevel 4 to return as much information as possible, but still not getting enough info.

My database was restored from a full backup WITH NORECOVERY and is in a restoring state. I have a folder with 100 transaction log backups, some of which are prior to the full backup, but most of which come after.

SqlLogShip.exe correctly determines the files that should be skipped and identifies which file is first to be restored, but then quits before performing any restores. It says the restore was successful, but 0 files were restored. Here's a few lines of the output:

Searching through log backup files for first log backup to restore. Secondary DB: 'DBName'
Skipped log backup file. Secondary DB: 'DBName', File: '<file path>'
...
Skipped log backup file. Secondary DB: 'DBName', File: '<file path>'
Found first log backup file to restore. Secondary DB: 'DBName', File: '<file path>'
The restore operation was successful. Secondary Database: 'DBName', Number of log backup files restored: 0


I don't see how this can be a permissions issue because I'm executing SqlLogShip.exe as myself (sysadmin, windows admin). SQL Server service account has correct permissions as well, and through profiler I can see calls to [master].[sys].[sp_can_tlog_be_applied] run against files in this folder. SQL Server can see and read the files in question, yet doesn't restore them. The SQL Server error log returns nothing of interest either.

Any ideas of what else I can try would be much appreciated!



_______________________________________________________________________________________________
Bob Pusateri
Microsoft Certified Master: SQL Server 2008

Blog: The Outer Join
Twitter: @SQLBob
Post #1530427
Posted Monday, January 13, 2014 1:13 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 3:43 PM
Points: 50, Visits: 168
Has your Log Shipping every worked for this database? It is possible that is is out of sync. You may nee to reload a new backup and try again.


Post #1530433
Posted Monday, January 13, 2014 1:18 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 7:41 AM
Points: 41, Visits: 377
Good question. This is a new setup so no it has never worked before (because it has never been attempted). However I can successfully restore the log backups manually, so I know they are compatible and there's no reason from a backup/restore perspective that they shouldn't work.

I should also point out that the age of the files is within the retention period setting, so that shouldn't prevent a restore from occurring either.


_______________________________________________________________________________________________
Bob Pusateri
Microsoft Certified Master: SQL Server 2008

Blog: The Outer Join
Twitter: @SQLBob
Post #1530434
Posted Monday, January 13, 2014 1:20 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 3:43 PM
Points: 50, Visits: 168
Let me know if it still does not work. I have successfully setup hundreds of Log Shipping databases, with SQL Server 2008 R2 and SQL Server 2012.


Post #1530435
Posted Monday, January 13, 2014 1:24 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 7:41 AM
Points: 41, Visits: 377
I'd say this behavior is pretty consistent - I have re-restored the full backup 4-5 times and have the same behavior each time.

_______________________________________________________________________________________________
Bob Pusateri
Microsoft Certified Master: SQL Server 2008

Blog: The Outer Join
Twitter: @SQLBob
Post #1530437
Posted Monday, January 13, 2014 2:04 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 3:43 PM
Points: 50, Visits: 168
I sent you a pm with my phone number. Feel free to call me to discuss your issue.


Post #1530454
Posted Tuesday, January 14, 2014 8:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:32 AM
Points: 5,956, Visits: 12,838
SQL_Bob (1/13/2014)
Good question. This is a new setup so no it has never worked before (because it has never been attempted). However I can successfully restore the log backups manually, so I know they are compatible and there's no reason from a backup/restore perspective that they shouldn't work.

I should also point out that the age of the files is within the retention period setting, so that shouldn't prevent a restore from occurring either.

Have you run the agent restore job and checked the history on this , it will usually give a clear indication as to the exact problem.


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1530729
Posted Tuesday, January 14, 2014 9:01 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 3:43 PM
Points: 50, Visits: 168
I would copy the code from the restore job, and run it in a command window. That way, you will see what the error is.


Post #1530734
Posted Tuesday, January 14, 2014 9:07 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:56 PM
Points: 32,771, Visits: 14,935
Well, if Master Bob is stumped, how can we help??

What about using something like the Process Monitor from Sysinternals to see if anything else launches after the tool reads the files? Or a detailed trace to see if SQL attempts something?

http://technet.microsoft.com/en-us/sysinternals/bb795533.aspx







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1530738
Posted Tuesday, January 14, 2014 9:09 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:56 PM
Points: 32,771, Visits: 14,935
Or use the file watcher to see if any process touches a log file.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1530742
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse