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 Tuesday, January 14, 2014 9:22 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 16, 2014 10:05 AM
Points: 41, Visits: 388
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
Twitter: @SQLBob
Post #1530747
Posted Tuesday, January 14, 2014 9:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 6,162, Visits: 13,305
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"
Post #1530765
Posted Tuesday, January 14, 2014 10:06 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 16, 2014 10:05 AM
Points: 41, Visits: 388
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
Twitter: @SQLBob
Post #1530768
Posted Tuesday, January 14, 2014 10:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 6,162, Visits: 13,305
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"
Post #1530790
Posted Tuesday, January 14, 2014 12:50 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 16, 2014 10:05 AM
Points: 41, Visits: 388
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
Twitter: @SQLBob
Post #1530845
Posted Tuesday, January 14, 2014 1:43 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 6,162, Visits: 13,305
I would look at getting the vendor to populate the LS tables on their system if possible. Have you asked them if they are willing to do this?

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

"Ya can't make an omelette without breaking just a few eggs"
Post #1530862
Posted Tuesday, January 14, 2014 2:40 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 16, 2014 10:05 AM
Points: 41, Visits: 388
Alright, after much more experimentation, it turns out that what I'm trying to do *can* work, provided that the formatting of the log backup file names exactly matches what log shipping is expecting.

I'll be working on a blog post detailing everything discussed here as well as how I solved each step. Will include a link when it's up. Thank you everyone for your help!


_______________________________________________________________________________________________
Bob Pusateri
Microsoft Certified Master: SQL Server 2008

Blog: The Outer Join
Twitter: @SQLBob
Post #1530877
Posted Wednesday, January 15, 2014 8:39 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Today @ 8:42 AM
Points: 33,049, Visits: 15,154
Thanks for the update.

Sounds like half-a** coding and an item for Connect.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1531153
Posted Wednesday, January 15, 2014 9:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 6,162, Visits: 13,305
Steve Jones - SSC Editor (1/15/2014)
and an item for Connect.

No, log shipping is doing exactly what it should be. If anything, you'll probably get an unsupported message for the scenario that's being implemented here


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

"Ya can't make an omelette without breaking just a few eggs"
Post #1531183
Posted Wednesday, January 15, 2014 9:29 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, July 14, 2014 7:18 AM
Points: 50, Visits: 174
I have created two sql scripts: First one will create Log Shipping for all databases that are not already setup for Log Shipping on the primary server. My second script will then run on the secondary server to create all the new databases and creates the sql jobs for copying the files and loading them. This made it so easy for me to setup log shipping for 300 databases on a a single server.


Post #1531186
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse