[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1703
  • Last Modified:

SSIS Help: Backup a Database and then Restore it to a Different server

Hello fellow experts,

I'm not very familiar with SSIS, I've poked around at a few wizard created import and export scripts, but that's about the extent of it.

What I need to do is create an SSIS package that will take a Backup of database on one server, then (delete and restore or) restore over a database on another server.

1 Solution
Anthony PerkinsCommented:
Is there any particular reason you want to use SSIS?
Cayman21Author Commented:
Well, I guess that's not a requirement.  But they are on different servers, so how would you suggest to do this in a automated fashion?
Anthony PerkinsCommented:
You could do it as a batch file that has the following:
1. Executes a SQLCmd to backup the source database.
2. Copies the backup from the source server to the target server.
3. Execute a SQLCmd to restore to the target server.
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Cayman21Author Commented:
That sounds like it would work.  What do I need to do to set that up?
Cayman21Author Commented:
Actually that won't work.

I don't have access to save the database backup on the server.

With SSIS, can't I stream the data to my local machine?
SSIS has the Maintenance Plan Task, Back Up Database. You can backup to a local file. Restoring is a different issue as no delivered task is available to restore. But you can use a SQL Task, or Maint. Plan Task, Execute T-SQL Statement Task to do the restore.
>> I don't have access to save the database backup on the server.
But the SQL Agent might and if you run that task under the SQL Agent it may work.
It all depends on how everything has been set up.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now