January 13, 2014 at 1:00 pm
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[/url]
Twitter: @SQLBob
January 13, 2014 at 1:13 pm
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.
January 13, 2014 at 1:18 pm
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[/url]
Twitter: @SQLBob
January 13, 2014 at 1:20 pm
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.
January 13, 2014 at 1:24 pm
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[/url]
Twitter: @SQLBob
January 13, 2014 at 2:04 pm
I sent you a pm with my phone number. Feel free to call me to discuss your issue.
January 14, 2014 at 8:45 am
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" 😉
January 14, 2014 at 9:01 am
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.
January 14, 2014 at 9:07 am
Well, if Master Bob is stumped, how can we help?? :-P:w00t:
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
January 14, 2014 at 9:09 am
Or use the file watcher to see if any process touches a log file.
January 14, 2014 at 9:22 am
Good point, Steve.
Actually I busted out Process Monitor yesterday to see how it was scanning the log files in the directory and found out that file names do matter - it was expecting the file name to start with the name of the source database. I renamed the files accordingly and then it was able to find the files.
That brought me to the issue in this thread, where it clearly sees the files and knows what to start restoring, yet doesn't actually carry out the restore.
_______________________________________________________________________________________________
Bob Pusateri
Microsoft Certified Master: SQL Server 2008
Blog: The Outer Join[/url]
Twitter: @SQLBob
January 14, 2014 at 9:56 am
SQL_Bob (1/14/2014)
Good point, Steve.Actually I busted out Process Monitor yesterday to see how it was scanning the log files in the directory and found out that file names do matter - it was expecting the file name to start with the name of the source database. I renamed the files accordingly and then it was able to find the files.
That brought me to the issue in this thread, where it clearly sees the files and knows what to start restoring, yet doesn't actually carry out the restore.
How come the files were renamed??
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 14, 2014 at 10:06 am
Because this is not true log shipping. I have no control over the primary server - it belongs to the vendor and I can't touch it. Instead, they are sending me their transaction log backups via FTP.
So this is log shipping in the sense that it's a transaction log restore, but it is not something I can easily set up with the wizard. I anticipated having to write my own application to go through the logs, determine the restore order and carry out the restores, but if I can leverage sqllogship.exe to do this for me instead I will gladly do so.
It seems that I'm so close to having it work. Hope I can figure out the remaining bits.
_______________________________________________________________________________________________
Bob Pusateri
Microsoft Certified Master: SQL Server 2008
Blog: The Outer Join[/url]
Twitter: @SQLBob
January 14, 2014 at 10:57 am
SQLLogship.exe relies upon the metadata for the primary and secondary IDs for the log shipping partners. If this is not present then it has nothing to work with.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 14, 2014 at 12:50 pm
You are correct, but the metadata is all there. I populated it with the log shipping stored procedures as detailed in BOL:
http://technet.microsoft.com/en-us/library/ms188708(v=sql.105).aspx
If the metadata wasn't there, nothing would work at all and it wouldn't even know where to start looking for the log files.
_______________________________________________________________________________________________
Bob Pusateri
Microsoft Certified Master: SQL Server 2008
Blog: The Outer Join[/url]
Twitter: @SQLBob
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply