December 4, 2008 at 8:52 am
Jerry Hung (12/4/2008)
Backup & Restore 101
Thanks Jerry for bluntly stating this fact. While I am not a big fan of backing up/restoring over the network for several reasons (a network hiccup on the wire can kill you, for one), I know it works and it has been stated how to get it to work. If the advice given is not followed, there isn't a whole lot we can do to help further.....it's kind of like beating your head against a wall - it feels sooo good when you stop! :w00t:
-- You can't be late until you show up.
December 4, 2008 at 9:37 pm
Dear
I agree with Tosscrosby and Jerry and Prajesh you can download filezilla or cuteftp for using the FTP.
Thanx
Thanks
December 5, 2008 at 5:39 am
The issue is that the SQL login is just that: a SQL (non-windows/non-network) login. Because SQL Logins aren't mapped to domain accounts, they aren't able to see network drives.
Change your SQL Server Agent Service account from local admin to a Domain account. Then make sure that domain account has read/write access to the network share. Once that's done, the backup will complete fine.
There is NO NEED to disable your firewall for this. If you have to do that, then something is seriously wrong or someone forgot to burn a port hole into the firewall.
December 5, 2008 at 5:41 am
Opps! I didn't realize there was a page 2 to this thread until after I posted. My bad. :blush:
Sorry, Guys. Didn't mean to reply to replies that were already made.
December 5, 2008 at 6:01 am
The simple answer to how to copy is - don't do it from within SQL Server. Schedule something outside of SQL server (windows scheduled task, etc...) to do the copy for you. You won't hav to worry about the security wranglings that way (since you will be able to easily pick a Windows user that has the requisite access.
Besides being easier to set up - it should actually be quite a bit faster and more reliable (backups are very "chatty", so making that happen over your network tends to sop up any available bandwidth).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 5, 2008 at 9:48 pm
I have set to network acess,
But the problem is :
In network storage devive , we have given username and password of that network device domain user for security purpose.Means we have to require username and password to open folder.
When i take backup from local to that network device.
Backup database master to disk = '\as01\folder '
Here i got error :
Msg 3201, Level 16, State 5, Line 1
Cannot open backup device '\as01\folder\master.bak. Operating system error 86(The specified network password is not correct.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Because, i have to give username and password to take backup on that folder..
But my question is , how can i give username and password (how to aunthicate user and password)
in query..
Is there and sql statement/query to give username and password in query, and we can take backup on that folder.
I hope that you are very clear about ny question.
December 8, 2008 at 5:09 am
The backup commands of T-SQL do not have a place for user name and password for the folder level. Only for the backup access.
Sorry, but you can't implement that kind of security for the backup process. You need to remove the folder requirement for the username & pwd, use Windows Authentication / AD authentication, and make sure that the domain account in question (and the folder permissions) isn't used by any other person or process. That's the only way to secure the folder.
December 8, 2008 at 9:48 pm
I have heard about some procedure like..SP_OAcreate ,...
which is used with OS Objects.
But i don't know how to use it?
can it solve my problem?
December 10, 2008 at 4:22 am
Paresh,
Have you actually looked at the Books Online explaination of that proc?
Sorry, but you can't accomplish what you're trying to accomplish using SQL Server straight out of the box. If you're not willing to take off the password of the network, then you have two choices.
You could try coding an assembly (and I'm not even sure if that'll work) and then register the assembly and then set it to use resources outside of SQL Server (a dangerous thing to do).
Or, as others have suggested, you could make the backup locally then use an existing tool (like SSIS File System Tasks or a Windows Task or RoboCopy) to send the backup to the network drive.
December 29, 2008 at 5:08 am
Yes, i am do as same way.
First take backup on Local..then plan to move on network backup device.
But problem is thar the destination folder has required username and password top open that folder.
So, how can i copy backup files from local to thet device.Bec i have to make it automated not manually.
I don't want open that folder by giving username and password and the copy all backup file.
I want it all done automated
Is there any way?
December 29, 2008 at 10:11 pm
Hello Paresh,
How you doing the backups?
Through a maintenance plan or sql job. First thing I'll suggest you is go with SQL JOB and follow the below steps.
If you are using Windows Authentication then cross check the account you are using the run sql server & server agent services (Make sure that it's not Local system) . Now remote desktop the destination server and browse to the shared folder and in security add that user which you are using to start sql server services.
Now remote desktop you db server and through run browse to the destination server using \\server_name\share_name then try to create a folder there just to check that you are having read and write permissions. After that open dos and browse to the same network folder and try adding a file or directory there. if you are able to do that then this means the login you are using to start the services have read-write permissions there.
In the sql job I'll suggest you to follow the below steps.
1. Take backup to local folder and in type choose T-SQL Script.
2. On Success rename or drop the destination backup file and in type choose Operating System.
3. On Success copy the local file to network and in type choose Operating System.
4. On Success Delete the local file if you need to free up the space.
Hope this helps.
Rohit
December 30, 2008 at 5:53 am
Rohit,
He already knows all that. He's trying to automate the copy process to a secured network drive and he refuses to listen to any of our advice on how to resolve the issue (ie, remove the security on that network drive).
I don't know if robocopy or xcopy can be automated with pass-thru security and I don't know how to write an assembly (if one would even work for what he's doing). And now he wants us to tell him specifically how to do both options instead of figuring it out for himself. Unfortunately, I don't think there is a way to do what he wants via SQL Server. I think he has to go to other tools after the point of backup.
But again, he doesn't seem to be listening to that advice.
December 30, 2008 at 6:39 am
Brandie Tarvin (12/30/2008)
But again, he doesn't seem to be listening to that advice.
BINGO! That's when I signoff on the OP.
-- You can't be late until you show up.
December 30, 2008 at 11:30 pm
tosscrosby (12/30/2008)
Brandie Tarvin (12/30/2008)
But again, he doesn't seem to be listening to that advice.
BINGO! That's when I signoff on the OP.
so, what to do?
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply