InstantForum.NET v4.1.4SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 08 Nov 2009 03:46:31 GMT20http://www.sqlservercentral.com/Forums/Topic611098-146-1.aspx[quote][b]tosscrosby (12/30/2008)[/b][hr][quote][b]Brandie Tarvin (12/30/2008)[/b][hr]But again, he doesn't seem to be listening to that advice.[/quote]BINGO! That's when I signoff on the OP.[/quote]so, what to do?Tue, 30 Dec 2008 23:30:27 GMTParesh Prajapatihttp://www.sqlservercentral.com/Forums/Topic611098-146-1.aspx[quote][b]Brandie Tarvin (12/30/2008)[/b][hr]But again, he doesn't seem to be listening to that advice.[/quote]BINGO! That's when I signoff on the OP.Tue, 30 Dec 2008 06:39:36 GMTtosscrosby-60625http://www.sqlservercentral.com/Forums/Topic611098-146-1.aspxRohit,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.Tue, 30 Dec 2008 05:53:45 GMTBrandie Tarvinhttp://www.sqlservercentral.com/Forums/Topic611098-146-1.aspxHello 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.RohitMon, 29 Dec 2008 22:11:35 GMTRamji-480085http://www.sqlservercentral.com/Forums/Topic611098-146-1.aspxYes, 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 automatedIs there any way?Mon, 29 Dec 2008 05:08:20 GMTParesh Prajapatihttp://www.sqlservercentral.com/Forums/Topic611098-146-1.aspxParesh,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.Wed, 10 Dec 2008 04:22:44 GMTBrandie Tarvinhttp://www.sqlservercentral.com/Forums/Topic611098-146-1.aspxI 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?Mon, 08 Dec 2008 21:47:59 GMTParesh Prajapatihttp://www.sqlservercentral.com/Forums/Topic611098-146-1.aspxThe 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.Mon, 08 Dec 2008 05:09:40 GMTBrandie Tarvinhttp://www.sqlservercentral.com/Forums/Topic611098-146-1.aspxI 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 1Cannot 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 1BACKUP 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.Fri, 05 Dec 2008 21:48:28 GMTParesh Prajapatihttp://www.sqlservercentral.com/Forums/Topic611098-146-1.aspxThe 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).Fri, 05 Dec 2008 06:01:33 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic611098-146-1.aspxOpps! 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.Fri, 05 Dec 2008 05:41:44 GMTBrandie Tarvinhttp://www.sqlservercentral.com/Forums/Topic611098-146-1.aspxThe 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.Fri, 05 Dec 2008 05:39:21 GMTBrandie Tarvinhttp://www.sqlservercentral.com/Forums/Topic611098-146-1.aspxDear I agree with Tosscrosby and Jerry and Prajesh you can download filezilla or cuteftp for using the FTP.ThanxThu, 04 Dec 2008 21:37:54 GMTguptaajay1985http://www.sqlservercentral.com/Forums/Topic611098-146-1.aspx[quote][b]Jerry Hung (12/4/2008)[/b][hr][b]Backup & Restore 101[/b][/quote]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:Thu, 04 Dec 2008 08:52:23 GMTtosscrosby-60625http://www.sqlservercentral.com/Forums/Topic611098-146-1.aspx+1 for Re-read my replies :DYesterday on a SQL ServerWe changed the SQL Server Service account from "Local System" to "DOMAIN\user" (and restart SQL Server after) that has access to remote backup locations, so that we can restore from network directlyBackup & Restore 101Thu, 04 Dec 2008 08:41:04 GMTJerry Hunghttp://www.sqlservercentral.com/Forums/Topic611098-146-1.aspxParesh, I've held my tongue for too long. You've been given solid advice and refuse to act upon it. Your network login obviously has rights to the network path. The account running the SQL (or SQL Agent) services does not. Get your network admin involved to correct the rights on the share. Whatever account is running SQL (or the Agent) needs, at a minimum, write rights to the network share. Without it, we can go on for days as to why this is not working and never get a resolution. Take the advice given, grant the proper rights and you'll be fine. This is NOT rocket science, many of us stumbled upon this early in our careers. Do NOT turn off the firewall settings - as already stated by Cath. Re-read Jerry's reply. Take the advice given - it WILL resolve your problems.Thu, 04 Dec 2008 06:29:33 GMTtosscrosby-60625http://www.sqlservercentral.com/Forums/Topic611098-146-1.aspxBut how can i copy to network storage device...when i run commandxp_cmdshell 'dir "nas01\folder"'or backup database master to disk = '\aso1\folder\master.bak'raised error : Specified path unavailable..so i can not take backup to yhat location..But when i open ppath from start->run --> type ptah---it open.or how can i take backup to FTP ?so, i can directly to take it on FTP of that.Help pls..Wed, 03 Dec 2008 21:51:51 GMTParesh Prajapatihttp://www.sqlservercentral.com/Forums/Topic611098-146-1.aspxthe "SQL Server Service" account needs READ/WRITE access to the folderI don't know if you can EXECUTE AS for the BACKUP DATABASE code..Backup to LOCAL and copy over works too as suggestedWed, 03 Dec 2008 11:19:22 GMTJerry Hunghttp://www.sqlservercentral.com/Forums/Topic611098-146-1.aspxI have taken backup on networh storage device, and the folder have username and password required to open that folder,When i take backup to taht folder,,i got above error which i have mentationed..So, how i can take backuop ti\o that folder..or how can i have to access that folder for backup..how?or is there any way in script we can autonicate username and paswword..is there any sql script or windows scirpt?But, i can not gave acess of folder to eveyone ..bec..it's for security..Tue, 02 Dec 2008 22:26:14 GMTParesh Prajapatihttp://www.sqlservercentral.com/Forums/Topic611098-146-1.aspxWhy not backup to a local drive and copy the backup to the network drive?Tue, 02 Dec 2008 08:50:30 GMTrlondonhttp://www.sqlservercentral.com/Forums/Topic611098-146-1.aspxI wouldn't switch my firewall off on either side ... If you want to do that, definitely make sure someone senior gives you the go ahead - preferably in writing. Though more likely they'll tell you that there's no need to switch the firewall off to grant folder access. I wouldn't grant 'Everyone' access on a directory either. Things like that may seem no big deal at the time, but then someone forgets to change the permissions back, data gets into the wrong hands and everyone's looking at you.Start at the beginning and check out the directory permissions as has been suggested.Tue, 02 Dec 2008 02:55:58 GMTCath Trimblehttp://www.sqlservercentral.com/Forums/Topic611098-146-1.aspxTo confirm this is a permissions problem, set the share [i]and [/i]NTFS permissions on the remote backup folder to "Everyone" - Change Control / ModifyThen try and re-run the backup.Mon, 01 Dec 2008 04:30:12 GMTFNShttp://www.sqlservercentral.com/Forums/Topic611098-146-1.aspxDear 1. Please off your firewall from both side(from system and from NAS(Network Attached Storage device))2. Then check the RW(read / write)permission on the folder.3. Then see that the sql user's (UserId and Password) and Folder's (UserId and Password) are how related?ThanxMon, 01 Dec 2008 04:21:40 GMTguptaajay1985http://www.sqlservercentral.com/Forums/Topic611098-146-1.aspxSQL logins..But i think concern with network storege device?Mon, 01 Dec 2008 03:44:32 GMTParesh Prajapatihttp://www.sqlservercentral.com/Forums/Topic611098-146-1.aspxThen the problem is with the User account executing the backup job in SQL . Is it a SQL login or a windows Authenticated login ?Mon, 01 Dec 2008 03:26:51 GMTdaryljhttp://www.sqlservercentral.com/Forums/Topic611098-146-1.aspxnot it is on,and when i have mapped that drive, i can see and open..and aslo open from directly \etdev\backup\ from my local machine..buit backup can not doneMon, 01 Dec 2008 03:22:32 GMTParesh Prajapatihttp://www.sqlservercentral.com/Forums/Topic611098-146-1.aspxFirst test your domain authentication by clicking on start then goto run and then type in the path :\\ folder\folderIt will then ask for username and password, when typing in username put domain first : "domain"\"username" and then enter password . It should then authenticate.Same should be done for the backup job.Mon, 01 Dec 2008 03:20:59 GMTdaryljhttp://www.sqlservercentral.com/Forums/Topic611098-146-1.aspxDear Is your firewall off?ThanxMon, 01 Dec 2008 03:16:52 GMTguptaajay1985http://www.sqlservercentral.com/Forums/Topic611098-146-1.aspxI have one network device ..in thos i have created folder backup and it open with username and password only.. i have take backup to this '\etdev\bakup\master.bakIt raised error liike: Msg 3201, Level 16, State 5, Line 1Cannot open backup device '\\etdev\bakup\master.bak. Operating system error 86(The specified network password is not correct.).Msg 3013, Level 16, State 1, Line 1BACKUP DATABASE is terminating abnormally.and i have tried it with mapping those drive in my local..and then backup to it..but getting error like 'network name not found'Mon, 01 Dec 2008 03:06:07 GMTParesh Prajapati