Hello, I've seen this post: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/e4ef653e-00a8-40b4-8a15-0c86335dfe33 Is it possible to do the linked server setup within SSIS? All I want to do is from one SQL Server do a database backup, copy it over to another SQL Server and run the restore on the destination SQL Server all within one SSIS package. I got the first part to work, not the restore part. Any hints would be appreciated. Thanks! -Chris

Remotely backing up a database is simple - use an Execute SQL Task to issue a BACKUP DATABASE command. That will make a backup somewhere on server A's filesystem. You'll then need to get this backup over to server B's filesystem somehow. This will take either xp_cmdshell to do the copy, or some other method. SSIS can be used to do this using a File System Task. The difficulty is that two processes might need to be executed. One on server A to copy the backup to a "shared" location, and one on server B to copy from the "shared" location to a local drive. You could make do with one process if either server A or B shares a local drive to the other server. You can remotely start an SSIS package by constructing a SQL Agent job that runs it on the remote server, then call sp_start_job on that remote server. Remotely restoring a database is just as easy with the RESTORE DATABASE command. No linked servers are necessary. Talk to me now on

Hi Todd, Just so that I don't get the syntax wrong where do you specify the remote sql server's name so that I don't accidently restore to the same sql server (which would be VERY bad)? RESTORE DATABASE YourDB FROM DISK = 'D:BackUpYourBaackUpFile.bak' WITH MOVE 'YourMDFLogicalName' TO 'D:DataYourMDFFile.mdf', MOVE 'YourLDFLogicalName' TO 'D:DataYourLDFFile.ldf' Thanks.

Hi Todd, Just so that I don't get the syntax wrong where do you specify the remote sql server's name so that I don't accidently restore to the same sql server (which would be VERY bad)? RESTORE DATABASE YourDB FROM DISK = 'D:BackUpYourBaackUpFile.bak' WITH MOVE 'YourMDFLogicalName' TO 'D:DataYourMDFFile.mdf', MOVE 'YourLDFLogicalName' TO 'D:DataYourLDFFile.ldf' Thanks. you should run this command in a CONNECTION TO REMOTE SQL SERVER. you can run it in a new query window in SSMS which connects to remote sql server. or in your application with a connection to remote sql server.http://www.rad.pasfu.com

Check Backup database task in Maintenance plan tasks which can place the backups on shared locations as well provided you have the access.Request to please mark my post as an answer if I helped you to resolve the issue or vote up if I helped you.Thanks. Regards and good Wishes, Deepak.

You don't specify a server name in the statement itself - as Reza has said, you need to connect to the server you want to restore the database to, and issue the command to it. As I've said, the biggest part of this fairly simple problem is moving the backup file from server A to server B. After you've done that, then you can issue the restore command. Talk to me now on

Thanks guys. Todd: The part of copying the .bak file from serverA to serverB is already completed in my SSIS package with the code below using a Script Task: Private Sub CopyFiles() For Each strFile As String In My.Computer.FileSystem.GetFiles("\\serverB\somefolder\") My.Computer.FileSystem.DeleteFile(strFile) Next For Each strFile As String In My.Computer.FileSystem.GetFiles("D:\SQLBackup\serverAfolder\") If My.Computer.FileSystem.GetFileInfo(strFile).CreationTime.ToShortDateString = Now.ToShortDateString Then My.Computer.FileSystem.CopyFile(strFile, "\\serverB\somefolder\" & My.Computer.FileSystem.GetFileInfo(strFile).Name, True) Exit For End If Next End Sub All I have to do is create a new connection manager object then point that to a Execute SQL Task, correct?

All I have to do is create a new connection manager object then point that to a Execute SQL Task, correct? yes, and also paste restore command in the sqlstatement property of execute sql task let us know if you have any problem. http://www.rad.pasfu.com

Yes, the connection manger should be poined to the destination server . Question : Can back up database task be used to copy the backup to other server ? I think yes, but just wanted to confirm. If so this task can replace the script above but only that there is only database backup that is performed. Any one can post ideas. Thanks. Please mark my post as an answer if I helped you to resolve the issue or vote up if I helped you.Thanks. Regards and good Wishes, Deepak.

I'm using a global variable within my package. Is there a way to check if there's a variable value prior to running the Execute SQL Task? I don't want to run the SQL Task if that variable isn't populated. Thanks.

you can check variable values with expression in the precedence constraint and do appropriate action based on it. this is a sample of how to implement IF condition based on variable values in ssis package control flow: http://www.rad.pasfu.com/index.php?/archives/11-Implement-If-condition-in-SSIS-package.htmlhttp://www.rad.pasfu.com

No - there is no way to back up a database to a non-local drive. You can't back up to mapped network drives or UNC shared drives. You can only back up to DAS drives (Direct Attached Storage). You also do not need the Script Task to copy the file. You can (and should) use the File System Task to copy the file. Doing so will make your package much more understandable to the next developer that needs to debug or modify your package. Talk to me now on

Thanks for all of your advice. I was able to solve my problem.