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.
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?
answered Mar 01 '12 at 01:19 PM
Kev Riley ♦♦