February 9, 2009 at 12:57 pm
I'm trying to do a setup in our dev environment of log shipping and am stuck.
Here's what I have done so far:
On the primary server:
-Setup a shared folder where it writes to and set permissions for read/write.
-Set SSMS to backup to \\testServer1\LogShipping which is C:\LogShipping
-Set the secondary server to copy files to C:\LSCopy
-Set to backup a full copy
-Set to restore to NoRecovery
On the secondary server:
-Setup a shared folder where the copy is sent to and set permissions for read/write. (C:\LSCopy)
I went to the SQLAgent service on both machies and gave it a "log on as" and gave it a network credential that could access either folder. I restarted each service after that.
When I click OK to save the log shipping info, it backs up the DB ok, but the next step it fails the "Restoring Backup to Secondary Database". Says that it cannot access \\testServer1\LogShipping\ET.bak (Access is denied)
I have looked around but cannot find a definate answer or something close to try to investigate on my own.
Any ideas out there?
February 9, 2009 at 4:13 pm
see http://msdn.microsoft.com/en-us/library/ms188698.aspx
for the initial restore with norecovery of the full backup, do that step manually, results are not good if you leave that to the logshipping GUI. then take it from there via the jobs set up by logshipping.
I always run the SQL service under the same account as the agent, you should have no permissions problems if you do this.
---------------------------------------------------------------------
February 9, 2009 at 9:06 pm
Does it sound like I'm understanding the procedure correctly otherwise?
Do both folders on each machine need to be shared? For example, does the primary not only back up the db, but also copy it over to the other servers folder? From there, does that secondary server SQL Agent read that folder to do the restores?
Does the SQL Browser service need to be running on either/both machines for this to work?
The GUI makes it seem simple to setup, but I didn't imagine I would have this much difficulty.
Since I was trying so many different things I'm sure that I turned stuff on or opened up things that probably weren't necessary to do so in the first place.
February 10, 2009 at 3:43 am
jbloes (2/9/2009)
Does it sound like I'm understanding the procedure correctly otherwise?
yes basically
Do both folders on each machine need to be shared?
don't have to be shares but works best if they are. As long as the service account SQL runs under has enough access to the directories.
For example, does the primary not only back up the db, but also copy it over to the other servers folder? From there, does that secondary server SQL Agent read that folder to do the restores?
yes
Does the SQL Browser service need to be running on either/both machines for this to work?
no
The GUI makes it seem simple to setup, but I didn't imagine I would have this much difficulty.
Since I was trying so many different things I'm sure that I turned stuff on or opened up things that probably weren't necessary to do so in the first place.
have all 4 services running under same domain user account with correct permissions on both boxes should all fall into place.
---------------------------------------------------------------------
February 10, 2009 at 8:07 am
Thanks for the info, I changed those services like you said and also did this:
For the secondary db, I did a restore on that one manually and got that up. Once that was done I configured the log shipping and marked the button saying that the secondary db was already initialized.
That failed only because the @@servername didn't match the instance name
SELECT @@SERVERNAME As [@@SERVERNAME],
CAST(SERVERPROPERTY('MACHINENAME') AS VARCHAR(128)) + COALESCE('' +
CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128)), '') As RealInstanceName
So I did the sp to drop and re-add the correct name and then that ran just fine!
Now we will see how it all goes.
Thanks!
February 10, 2009 at 12:59 pm
I did a test where I brought online the secondary server and had my app connect to it, so that was successful (good news there).
I set everything back to normal but now the secondary won't do the restores saying it's out of sync.
I thought I could right click the db and go to restore > transaction logs, but it's grayed out.
What's the best way to get it back in sync?
February 10, 2009 at 2:07 pm
once you bring the secondary on-line you can apply no further tran logs to it, the db needs to be in recovery or standby mode.
Only one way -backup primary again, copy over and do full restore in norecovery mode, then restart log shipping jobs again.
log shipping will attempt to copy and restore next chronological log backup after last one it successfully restored, so I suggest you clear out all log backups previous the the latest full backup from your shares to ensure correct log is picked up.
---------------------------------------------------------------------
February 10, 2009 at 2:26 pm
I get this message when trying to do a full restore on my secondary
Restore failed for Server 'TestServer2'. (Microsoft.SqlServer.Smo)
------------------------------
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: The tail of the log for the database "ET" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log. (Microsoft.SqlServer.Smo)
Now, if I do that on my primary, won't that take it offline.
I right clicked the db on my primary, selected full backup and that's it. Am I missing something on the options page?
Should the restore be set to overwrite the existing db?
Thanks for your help and patience.
February 10, 2009 at 3:43 pm
you have taken a new full backup of the primary, yes?
copy that to the secondary,
the database now already exists on the secondary so when you restore over it you have to use the 'with replace' clause as well as norecovery. If you are using SSMS this equates to choosing the overwrite option.
check out restore database in BOL and also backup and recovery.
---------------------------------------------------------------------
February 10, 2009 at 4:35 pm
Not too long before your post, I did try it with the overwrite option and that did the trick.
I cleared out all the logs and everything picked right back up.
I just logged in and verified that it is doing what it is supposed to.
I'm very pleased with the results.
Thanks George for helping me out!
February 10, 2009 at 4:53 pm
no probs.
---------------------------------------------------------------------
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply