SQL Server Performance Forum – Threads Archive

How to backup out of the server

Whenever I issue a database backup command from my computer, the result is created on the server itself and I have to FTP the .BAK file into my computer. Is it possible to set the database backup command to create the file on my computer?

---

Yes, is Box is Main Domain and you have permission on that box, you can share a new folder in your PC, map this folder on server and now SQL can see your folder like other drive. Luis Martin
Moderator
SQL-Server-Performance.com

---

BACKUP DATABASE database_name
TO DISK = ‘\serversharedirectoryfile.bak’
WITH options
MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

Having said that, I would not recommend using network drive for databae backups as the operation is genreally slow and more prone to errors. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

---

It’s always better to backup/copy/restore. However, I prefer backing up over the network as opposed to restoring. That’s really, really costly. We do our backups over the network but it’s a really beafy backbone and we don’t get any latency at all. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

Derrick, I didn’t get your "I prefer backing up over the network as opposed to restoring". What you mean? Backing up on server itself and copy to local machine OR backing up directly into local machine both creat a .BAK file that we have to restore it later.

---

I have a VPN connection with my server. I accept Gaurov’s comment but I can set to create two backup one on server and the other on my local. My first attempts to use Luis / Derrick’s solutions was not successful. I think I have problem to "see" my local shared folder.

---

If server can see local shared folder, you can’t do it.
Luis Martin
Moderator
SQL-Server-Performance.com

---

If you don’t have the space and you have to choose to: 1. Backup locally. Restore over Network.
2. Backup to a network share. Restore locally. I would prefer to backup the files and clone or copy them, then restore from those. However, many times this option isn’t available due to space constraints. Your local shared folder has to be accessible to the account on SQL Server doing the backup/copy/etc. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

I have three servers. To connect to one of them, I have to run a VPN connection program (iPassConnect). After that I am able to login the server and work with it. EM can see the server and work with it. I am able to see the server screen using "Remote Desktop Connection". But I can not ping the server or ping my computer when pining from the server. Does it mean that I would not be able to use your suggestions? Any idea? Thanks,

---

?? If you can’t even get access from one box to another, there’s not a whole lot you can do. I would talk to your network people and get something setup with them. Otherwise, you’re pretty much dead in the water. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

Ping doesnt have to mean anything. It could be blocked in a firewall. You can block ping and still allow sql or network share traffic. As mentioned check with your network people that the necessary ports are open to be able to use microsoft shares (445 on win 2000, 135 etc on win NT).

---

Yes, I can work with EM connected to the SQL-Server box when the VPN is up. I can run Terminal Services, too. But I am not able to FTP from it. I had used NetMeeting for some weeks but due to VPN disconnection, I lost my session opened and now, NetMeeting is asking to reboot server to be able to use it. It’s not possible to reboot the server at the moment. Question is that if NetMeeting was able to work and download/upload files, does it mean that FTP programs can do that? And is it possible to use NetMeeting without reboot the server? Thanks,

---

That Netmeeting works says nothing about if FTP should work or not. They are using different ports to communicate. Are you saying you have a FTP program installed on the SQL server and you can not connect to an external FTP server and transfer data? If so what error do you get and where do you get it? Do you get errors when trying to connect to the ftp server or errors when you try to transfer data? To test that you can reach the FTP server get information of which port it uses (often 21) and what IP it’s on (say 123.123.123.123). Then open a command prompt on the sql server and type:
telnet 123.123.123.123 21 and see of you get an error message or not. If you do either the FTP server is not responding, there are network issues or port 21 is blocked.

---

I was unaware that you could backup across a network. So if I have Server2 and a //Server3/Shared, do I create a backup device to that file? (I do have a firewall and will check for access)
the
Bugster

---

As you see, our folks are saying it’s possible, if your Server2 can see the //Server3/Shared folder and has the R/W rights. Then you can set your SQL-Server to creat its backup directly into that folder. Farhad R
[email protected]

---

Was it mentioned anywhere what the size of the dumps are? Brett

---

Yes, you can do a backup across the network. The syntax is: BACKUP DATABASE name
TO DISK = ‘\serversharefolderfile.bak’
WITH
blah,blah,blah
As you see, our folks are saying it’s possible, if your Server2 can see the //Server3/Shared folder and has the R/W rights. Then you can set your SQL-Server to creat its backup directly into that folder. The account that runs SQL Server needs to have access to the share. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

]]>