October 30, 2008 at 6:55 pm
I have been trying to perform a log shipping exercise on my home pc with 2 instances of sql. I have created 2 folders on the c drive, LogPrimary and LogSecondary. I have set the permissions on the folder but keep getting the following error whils trying to restore the database to the secondar server. The error is as follows:
TITLE: Microsoft SQL Server Management Studio
------------------------------
SQL Server Management Studio restore database 'AdventureWorks'
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (SqlManagerUI)
------------------------------
Cannot open backup device '\\USER-2XU1SYYFYZ\LogPrimary\AdventureWorks.bak'. Operating system error 1231(error not found).
RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 3201)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=3201&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
What is operating system error 1231. I have not seen it before. Any help would be much appreciated.
October 30, 2008 at 8:55 pm
I don't know what this error means either but I suspect that it is being caused because you are trying to use UNC names for the folders but do not have a functional network (or no network at all) so that the UNC names can be resolved to the actual server that the share is on. Even though the UNC refers to the host that you are trying to do this from, you still need some network.
Can you update what you are doing with log shipping to use path names like C:\SQL\logs instead ?
October 31, 2008 at 1:42 am
WHen I use the SQL server Logshipping wizard it will not allow me proceed unless I specify prmary folder with UNC. How can I overcome this.
October 31, 2008 at 2:04 am
Does the '\\USER-2XU1SYYFYZ\LogPrimary\ SHARE exist i.e. if you go to Start/Run and enter '\\USER-2XU1SYYFYZ\LogPrimary\ will that take you to your .bak file?
/Elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
October 31, 2008 at 4:00 am
When I use run to try and access folder it does not take me there. It says I do not have network privelidges or permissions and that I should check with the administrator. However my log on account is the administrator account.
October 31, 2008 at 4:10 am
OK.. Navigate in Explorer to the folder where your backup file is. Right-click that folder and either Share if it has not been done - with the name you used in your Logshipping Setup - or set the Permissions. Repeat for the other Folder/Share involved.
I apologize but I don't know which operating system you are on so I can't give you the exact steps click-by-.click (with that I am not implying that I would know them off the top of my head for any and all operating systems 😛 ).
/Elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
October 31, 2008 at 4:18 am
Just done that and still no luck
October 31, 2008 at 5:25 am
Hm There is something fundamentally wrong if you cannot even browse to that share using UNC path although your permissions on the share (not folder) has been set up properly.
What do you get when you go to Start/Run and enter "\\ " ?
/Elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
October 31, 2008 at 5:41 am
it says i do not have access to this network resource and that I should contact my network administrator. The computer used to be an old pc from work which was connected to a network. will that have any effect on the permissions.
October 31, 2008 at 6:40 am
That should have been \\YourServerName. Are your Server and Workstation sevices up and running?
There is a Quick 'n Dirty way if you just want it up and running for test purposes and that is to change the path in log_shipping_primary_databases (backup_directory) and log_shipping_secondary (backup_source_directory)and (backup_destination_directory) . The tables reside in the msdb database.
I haven't done this since SQL Server 2000 and haven't got Log Shipping up at the moment so I cannot confirm this though.
/Elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
October 31, 2008 at 8:02 am
How do I enebale server and workstation services and I am not sure what I am supposed to do in the msdb database. Could you expand a little more. Sorry to be a pain in the arse. Just that this thing has been bothering me for 2 nights now.
October 31, 2008 at 8:46 am
Hi
Control Panel - Services, right-click and choose Start (in case they are not started).
I need to setup Log Shipping myself to give you the step by steps. Bear with me a little and I'll see if I can do it straight away.
🙂
/Elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
October 31, 2008 at 1:28 pm
Ouch, must have been a loooong time since I did this. I remember running into the same thing with paths constantly on SQL 2K but apparently I had suppressed all memories.
Anyway, got around them and hope you will as well...I find it a bit disturbing that the UNCs are not working. It must be permission related so I will poke into that and post any findings but at least this should get you going for now.
1. You're right, the GUI won't let you OK until you enter an UNC path but if you in addition enter the local path (c:\BackupFiles in my case) that will be entered into log_shipping_primary_databases (backup_directory column) on the Primary. I did this after I discovered that my backup and log copy jobs were failing (so not at initial setup).
2. I then manually changed the backup_source_column in the log_shipping_secondary table on the Secondary server but of course I should have used the sp_change_log_shipping_secondary_primary
stored procdure instead (documented in BoL) 🙂
So, in short, change the Backup Directory on the Primary in the GUI (keep the UNC path in the text box above and you will be able to proceed) and then use sp_change_log_shipping_secondary_primary procedure to change the Backup Source directory on your Secondary server.
Still confused about you not being able to browse to your shares but that's another story I suppose.
HTH!
/Elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply