November 20, 2017 at 10:50 am
Hello, I cannot seem to get log shipping to work on my server setup. I'm sure I am missing something simple as my config is pretty basic but I'm out of ideas.
Here's what I have:
2 SQL server 2012 installed on Server 2012 std
Neither install is on a domain or AD.
Both servers have same passwords for users on both systems.
I am trying to set up log shipping between them. In explorer I can go to each of the shared drives (one on both servers) without any issues. But when I try to set up log shipping, I get an error on the primary that it cannot access the backup location (the second server). I have tried adding the network drive on the second server in explorer but then I get the error it cannot find the location of the backup drive.
On the second server the 'copy' job created will work fine but as it has no file, doesn't do any good.
Could anyone point me in a direction to get this resolved? I've out of different ideas to try.
Thanks
November 20, 2017 at 12:10 pm
unematt - Monday, November 20, 2017 10:50 AMHello, I cannot seem to get log shipping to work on my server setup. I'm sure I am missing something simple as my config is pretty basic but I'm out of ideas.
Here's what I have:
2 SQL server 2012 installed on Server 2012 std
Neither install is on a domain or AD.
Both servers have same passwords for users on both systems.I am trying to set up log shipping between them. In explorer I can go to each of the shared drives (one on both servers) without any issues. But when I try to set up log shipping, I get an error on the primary that it cannot access the backup location (the second server). I have tried adding the network drive on the second server in explorer but then I get the error it cannot find the location of the backup drive.
On the second server the 'copy' job created will work fine but as it has no file, doesn't do any good.Could anyone point me in a direction to get this resolved? I've out of different ideas to try.
Thanks
You could follow the directions in this link as it goes into more details of how to do this. You will need to pay close attention to the details of any error messages you have as that generally will lead you to where thing are breaking in the process:
SQL Server Log Shipping to a Different Domain or Workgroup
Sue
November 20, 2017 at 1:30 pm
Hey Sue,
I'm getting the same error, even when the location the primary server is storing is its local drive.
I have both SQL server agents set to run as a local admin account. This is the error I get:
Message
2017-11-20 14:26:19.40 *** Error: Backup failed for Server 'DEV'. (Microsoft.SqlServer.SmoExtended) ***
2017-11-20 14:26:19.40 *** Error: An exception occurred while executing a Transact-SQL statement or batch.(Microsoft.SqlServer.ConnectionInfo) ***
2017-11-20 14:26:19.40 *** Error: Cannot open backup device 'E:\Backup\DEV\dms_cache\tLOG\dms_cache_20171120202619.trn'. Operating system error 5(Access is denied.).
BACKUP LOG is terminating abnormally.(.Net SqlClient Data Provider) ***
2017-11-20 14:26:19.41 ----- END OF TRANSACTION LOG BACKUP -----
I've gone round and round trying to re-set up this process but I cannot find where I am making a mistake.
Matt
November 20, 2017 at 1:47 pm
unematt - Monday, November 20, 2017 1:30 PMHey Sue,I'm getting the same error, even when the location the primary server is storing is its local drive.
I have both SQL server agents set to run as a local admin account. This is the error I get:
Message
2017-11-20 14:26:19.40 *** Error: Backup failed for Server 'DEV'. (Microsoft.SqlServer.SmoExtended) ***
2017-11-20 14:26:19.40 *** Error: An exception occurred while executing a Transact-SQL statement or batch.(Microsoft.SqlServer.ConnectionInfo) ***
2017-11-20 14:26:19.40 *** Error: Cannot open backup device 'E:\Backup\DEV\dms_cache\tLOG\dms_cache_20171120202619.trn'. Operating system error 5(Access is denied.).
BACKUP LOG is terminating abnormally.(.Net SqlClient Data Provider) ***
2017-11-20 14:26:19.41 ----- END OF TRANSACTION LOG BACKUP -----I've gone round and round trying to re-set up this process but I cannot find where I am making a mistake.
Matt
It looks like you may not have the permissions correct - that is why you get the error Access is denied. If it couldn't see the directory, you would usually get an error of path not found.
As that article explains, you need to add the permissions to the share and the directory.
You hopefully used SQL Configuration Manager when you setup the service accounts with the same name and passwords.
Sue
November 20, 2017 at 1:55 pm
On both servers I have created a windows user called 'logshipping' and they both have admin rights. In both of the backup folders they are added under 'backup properties-security' and under 'sharing-Advanced settings'. Both sql server agents are set to run as that user under sql server manager. I'm not sure what I'm missing.. Also the sql job is set to run under the sql server agent service account.
Matt
November 20, 2017 at 2:57 pm
Matt
[/quot
What are the permissions for E:\Backup\DEV\dms_cache\tLOG?
Are you intending for this to be the primary and is this the DEV server?
I believe I know but which job is failing and on which server?
Sue
November 20, 2017 at 3:02 pm
The permissions are set to 'Full control' for the 'logshipping' user. The E:\Backup\...ect directory is on both servers. The job is failing on both servers. The primary server is the DEV server, the second is the DEVBACKUP server.
Thanks for your help Sue 🙂
Matt
November 20, 2017 at 3:27 pm
Ok after completely rebuilding the backup directory and setting the permissions, I have the primary server script working.
The second server (after I did the same thing) is giving me:
Message
2017-11-20 16:25:34.48 *** Error: Could not retrieve copy settings for secondary ID 'bbb71711-7bb4-4f2b-a8ca-b0c30a111410'.(Microsoft.SqlServer.Management.LogShipping) ***
2017-11-20 16:25:34.48 *** Error: Failed to connect to server DEV.(Microsoft.SqlServer.ConnectionInfo) ***
2017-11-20 16:25:34.48 *** Error: Login failed for user 'DEV\logshipping'.(.Net SqlClient Data Provider) ***
2017-11-20 16:25:34.48 ----- END OF TRANSACTION LOG COPY -----
Keep in mind that the secondary server is named DEVBACKUP no I'm not sure where the DEV\logshipping is coming from.
Matt
November 20, 2017 at 3:51 pm
unematt - Monday, November 20, 2017 3:27 PMOk after completely rebuilding the backup directory and setting the permissions, I have the primary server script working.The second server (after I did the same thing) is giving me:
Message
2017-11-20 16:25:34.48 *** Error: Could not retrieve copy settings for secondary ID 'bbb71711-7bb4-4f2b-a8ca-b0c30a111410'.(Microsoft.SqlServer.Management.LogShipping) ***
2017-11-20 16:25:34.48 *** Error: Failed to connect to server DEV.(Microsoft.SqlServer.ConnectionInfo) ***
2017-11-20 16:25:34.48 *** Error: Login failed for user 'DEV\logshipping'.(.Net SqlClient Data Provider) ***
2017-11-20 16:25:34.48 ----- END OF TRANSACTION LOG COPY -----Keep in mind that the secondary server is named DEVBACKUP no I'm not sure where the DEV\logshipping is coming from.
Matt
You need to look in the SQL Server error log to find the state code for the login failure. The state tells you the reason for the login failure:
Troubleshooting Error 18456
Sue
November 20, 2017 at 4:05 pm
Ok, I was able to add a new user that cleared the login error. Now I am getting this error:
Message
2017-11-20 17:03:09.95 *** Error: Could not retrieve copy settings for secondary ID 'c0806128-4a81-4fe7-8f12-85d1284c9bd9'.(Microsoft.SqlServer.Management.LogShipping) ***
2017-11-20 17:03:09.95 *** Error: The specified agent_id C0806128-4A81-4FE7-8F12-85D1284C9BD9 or agent_type 1 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***
2017-11-20 17:03:09.96 *** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2017-11-20 17:03:09.96 *** Error: The specified agent_id C0806128-4A81-4FE7-8F12-85D1284C9BD9 or agent_type 1 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***
2017-11-20 17:03:09.96 *** Error: Could not cleanup history.(Microsoft.SqlServer.Management.LogShipping) ***
2017-11-20 17:03:09.96 *** Error: The specified agent_id C0806128-4A81-4FE7-8F12-85D1284C9BD9 or agent_type 1 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***
2017-11-20 17:03:09.96 ----- END OF TRANSACTION LOG COPY -----
I added the new user to the bulkadmin, sysadmin but this error still persists.
Thanks for all your help,
Matt
November 20, 2017 at 4:24 pm
unematt - Monday, November 20, 2017 4:05 PMOk, I was able to add a new user that cleared the login error. Now I am getting this error:Message
2017-11-20 17:03:09.95 *** Error: Could not retrieve copy settings for secondary ID 'c0806128-4a81-4fe7-8f12-85d1284c9bd9'.(Microsoft.SqlServer.Management.LogShipping) ***
2017-11-20 17:03:09.95 *** Error: The specified agent_id C0806128-4A81-4FE7-8F12-85D1284C9BD9 or agent_type 1 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***
2017-11-20 17:03:09.96 *** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2017-11-20 17:03:09.96 *** Error: The specified agent_id C0806128-4A81-4FE7-8F12-85D1284C9BD9 or agent_type 1 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***
2017-11-20 17:03:09.96 *** Error: Could not cleanup history.(Microsoft.SqlServer.Management.LogShipping) ***
2017-11-20 17:03:09.96 *** Error: The specified agent_id C0806128-4A81-4FE7-8F12-85D1284C9BD9 or agent_type 1 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***
2017-11-20 17:03:09.96 ----- END OF TRANSACTION LOG COPY -----I added the new user to the bulkadmin, sysadmin but this error still persists.
Thanks for all your help,
Matt
I've seen that series before when the server referenced in one of the jobs is messed up for some reason.
On the secondary, you should have a job named something like LSCopy_ServerName_LogShippedDBName
If you go to that job, right click, select properties and then to steps and for that one step (named Log shipping Copy Job Step), select edit.
It should be an Operating Command job step that has the full path to sqllogship.exe followed by -copy and some ID. And then after that is another parameter for -server
Look at that server name after -server. It should be the name of the secondary server. If it's not try changing it and see if that works. The restore job works the same except it has -Restore instead of -Copy. But they both should have the -server followed by the name of the secondary at that end of the commands.
Sue
November 20, 2017 at 4:46 pm
That worked! Now I'm getting the 'Database is not in recovery or standby mode' error. I selected not to need this when I setup the log shipping, not sure how to get the DB to restore without it in either of those modes.
Thanks Sue!
Matt
November 20, 2017 at 5:17 pm
unematt - Monday, November 20, 2017 4:46 PMThat worked! Now I'm getting the 'Database is not in recovery or standby mode' error. I selected not to need this when I setup the log shipping, not sure how to get the DB to restore without it in either of those modes.Thanks Sue!
Matt
When you set this up, I would guess you selected the option for the database already being initialized. It wasn't and the database is on the secondary fully recovered. That won't work. The logs can't be restored when the database is fully recovered. Sorry but now you get to try again. Try initializing from a new full backup - the option is something like Generate a full backup and restore it on the secondary. It's the first option on the initialization page.
Sue
November 21, 2017 at 9:17 am
That is true, I did. The issue is that when I try to initialize again, it fails with the error of not having access to load the .bak file. When I place the backup file on the second server, it fails to start with the same error.
Matt
November 21, 2017 at 10:34 am
unematt - Tuesday, November 21, 2017 9:17 AMThat is true, I did. The issue is that when I try to initialize again, it fails with the error of not having access to load the .bak file. When I place the backup file on the second server, it fails to start with the same error.Matt
You probably had the errors when the permissions weren't set correctly earlier. Let it initialize right away instead of trying to point it to existing backup files.
Sue
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply