October 22, 2014 at 6:41 am
Hello, I hope someone could give me a hand. I have had to remove log shipping from a database in order to reflect some schema changes. Going through the wizard, I set everything up and the primary database begins backing up, but fails to restore on the secondary. I have used the existing folders from when log shipping was working before and all the same settings so permissions can't be an issue.
If I was to backup and restore the database to the secondary myself (with norecovery), and set up log shipping using the 'no database is already initialised) do I have to backup and restore any log backups that may have occurred after the back up but before the restore, before setting up the secondary, or does the log shipping process work it out for me? If I do have to restore a log backup, would I have set up log shipping quickly before another occurred on the primary? Also, do I still have the option of using stand by mode this way?
Backup primary.
Restore to secondary with norecovery.
Restore any log backups that may have occurred since the full backup with norecovery.
Start the log shipping wizard.
Choose ' no, the secondary database is already initialised'
And just go from there choosing to have the database in standby mode for read only purposes.
Does that sound right? Thank you!
Regards, D.
October 22, 2014 at 7:05 am
what errors do you see in the error log?
What detail does the wizard dispaly, click the error hyperlink for more detail
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 22, 2014 at 1:49 pm
Hi Perry, thanks for getting back, curiously I did not see anything in the logs, I did click the hyperlink but I can't remember what was in it. I read the error at work, but posted from home. I'll have to post again later.
Regards
D
October 22, 2014 at 2:19 pm
its easier and more reliable to do the full database backup and restore yourself.
You would have to manually restore any log backups (with norecovery) taken beween that full backup and the first log backup taken by the logshipping jobs. For that reason it's best not to take any. Logshipping log backups need to be the only ones taken so any other log backup jobs must be removed
so your task list sounds right
---------------------------------------------------------------------
October 22, 2014 at 3:43 pm
Hi George,
Thanks for getting back. I'll see if I can try that.
Perry, my error is as follows
Cannot open backup device G:\SQLBackup\dbname.bak. Operating System error2(failed to retrieve text for this error. Reason: 15105).
RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 3201)
Could this still be a permission issue?
Regards,
D.
October 22, 2014 at 7:42 pm
OK, so basically I ended up backing up the database, moving it to secondary, restoring the database in NoRecovery/Stand By, and went back to the primary and finished log shipping there, no problem, just wish I'd done that in the first place!
I reckon it was down to permissions, set up by someone else and I had to find my way around.
One thing I would say about this to anyone experiencing the same issue. Try moving the .bak file to the secondary server and restoring the database from the same place you was getting this issue. You will get the same error, move the file around until you find a place where the permissions are correct, I ended up copying it to place SQL keeps the MDF files, and getting round it that way. And its so much easier to restore to the seconday first, then choosing the 'No, the database is already initialized' option when setting up on the primary. As George said above, so much easier and reliable.
Thanks for getting back Perry and George.
Cheers,
D.
October 23, 2014 at 4:49 am
Duran (10/22/2014)
Hi George,Thanks for getting back. I'll see if I can try that.
Perry, my error is as follows
Cannot open backup device G:\SQLBackup\dbname.bak. Operating System error2(failed to retrieve text for this error. Reason: 15105).
RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 3201)
Could this still be a permission issue?
Regards,
D.
Operating system error 2 is file not found and typically if a file exists but cant be accessed you'll get this error. Check the NTFS permissions on the folder to ensure that subfolders and files are inheriting.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply