asked on

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.

Thanks!

Anthony Perkins

Is there any particular reason you want to use SSIS?

Cayman21

ASKER

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 Perkins

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.

Cayman21

ASKER

That sounds like it would work. What do I need to do to set that up?

Cayman21

ASKER

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?

ASKER CERTIFIED SOLUTION

Steve Hogg

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

nmcdermaid

>> 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.