x

Does anyone know of any step by step instructions on how to send a file from one server to another server?

Hello all,

I currently have a backup scheduled to run at 6am everyday under the maintenance plan in SQL Server 2005. This is working fine. When I try to send the backup from one server to another server, I always get an error. I have modified credentials under security, made sure that the shared drive can actually share files. I need to know 1 is there a written guide or website with step-by-step instructions to see if one sql server can communicate with another sql server. 2. Is there a written guide or website with step-by-step instructions on how to just send a file from one server to another server, regardless of what type of program can do this. If there is an SSIS step-by-step guide on how to send a backup from a source sql server (in my case the source is named sql1) to a destination server (the destination server is named sql test). I wrote about this about 2 days ago. I am very thankful of the knowledgable people here. I tried to implement everything that people have given me knowledge about. I have about 3 to 4 books on SQL Server 2008 R2 and they do not cover this topic very well. I am not realy familiar with security and credentialing and it seems kind of confusing. I am a DBA with some experience in SQL 2005 and SQL 2008 R2. Any help would be appreciated.
more ▼

asked Mar 01, 2012 at 12:53 PM in Default

cdurham gravatar image

cdurham
230 21 22 24

So far you have asked a lot of questions on this forum and have had a large number of answers but have not marked any of them as having helped you. This wont encourage people to keep offering solutions to questions you pose. Would you be able to find some time to review the answers that you have had on other questions and mark appropriate one as helpful by clicking the tick beside them please? It isnt much to ask for all the assistance that is offered across this site.
Mar 01, 2012 at 01:26 PM Kev Riley ♦♦
I'm sorry, I will keep that in mind. I will try to rerun the backup again and post the error message its giving me.
Mar 01, 2012 at 03:30 PM cdurham
Don't apologise, we just like to gently nudge people along in the right direction. It benefits both parties then!
Mar 01, 2012 at 03:31 PM Kev Riley ♦♦
@Kev Riley I have attached the messages that I am getting.
Mar 01, 2012 at 03:49 PM cdurham
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Try to break the problem down.

How is this trying to work, is it backing up to another server (via a UNC) or is it backing up to local then attempting to copy?

If you are trying to backup via UNC, then remove SSIS for a moment. Can you backup to the UNC via T-SQL?

There are no SQL specific credentials/security to worry about, it's a simple file creation action. If the account running the execution has the permissions, then you should be fine.

Have you got any more details about the error. Is the error coming from the SSIS package, or the Agent, or the SQL,.....

How far does the process get?

Can you run the backup to a local drive OK?
more ▼

answered Mar 01, 2012 at 01:19 PM

Kev Riley gravatar image

Kev Riley ♦♦
52.8k 47 49 76

@Kev Riley

I do not know what a UNC is. Sorry, I'm just a begginer at this, but basically I have a job run on a server called SQL1. This job creates a backup on SQL1 at 6am every morning. If I view the jobon the maintenance plan, it opens a ssis package within SQL Server Management Studio. I have a little bit of experience writing ssis packages. I even tried to write an ssis package that would grab the backup from the source (SQL1) and try t send it to drive g to the destination server (called SQL-Test) I use sql_test to test our company's Sales Database. Since I get an error when I try to send it to \sql-test\g or try to send it to \sql-test\g$, i get an error. I even used the ip address of SQL-Test and I have gotten an error from that also. The only way that I can get the backup from SQL1 to SQL-Test is by going into the C drive, copying the backup, pasting the backup in a server called Data-Center. I will then log into SQL-Test, Open data center, copy the file from data center, and paste it to drive g:. I can then restore the db in SQL-Test like this. I was just wanting to automate this process. As for your question, I can back it up to my local drive on SQL1, I only have problems when I try to back it up to any other server(s). I have a picture here of what is happening when i try to backup to another server. http://ask.sqlservercentral.com/questions/87181/i-am-trying-to-send-a-backup-from-one-server-to-an.html
Mar 01, 2012 at 02:53 PM cdurham

OK A few things here.

UNC = Uniform Naming Convention - is the name of the syntax used to specify a path on the network e.g. \\SERVER\SharedFolder\Kev. It sounds like you are already using this.

When you manually copy the file, you are having to use a 3rd server (Data-Center) - that suggests that there isn't a direct path between your source server SQL1 and target server SQL-Test. Can you get clarification on that from your network admin?

The screen shots from the other post are good, but still don't give an actual error message or error number. I'm not a big fan of SSIS for things like this, it just adds layers that are difficult to wade through when there's an issue (seem familiar?)

Mar 01, 2012 at 03:06 PM Kev Riley ♦♦

The simplest check you can do is to while sitting on the SQL1 server, use Windows button + R OR open any folder and type the specified address. If you are prompted for credentials, then ask you network admin to get it configured. But If the shared drive is accessed then try to create a file there. If you would not have the rights, you will find it out. Whatever happens do let us know.

Moreover, what exactly the SQL Server Agent Job history says? There would have been some details for the error message there as well.
Mar 01, 2012 at 03:28 PM Usman Butt
(comments are locked)
10|1200 characters needed characters left
more ▼

answered Mar 01, 2012 at 03:50 PM

cdurham gravatar image

cdurham
230 21 22 24

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x161
x84

asked: Mar 01, 2012 at 12:53 PM

Seen: 1128 times

Last Updated: Mar 01, 2012 at 03:50 PM