Is it possible to copy entire database from one server to another using SSIS

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

hi All,

I am new to SSIS. I am trying to copy all the tables and their data in a Database from one server to another server say from test database to dev database.

I tried creating a ssis package by creating 2 connections(dev and test). create a data flow and inside it I selected oledb source and ole db destination and selected the db accordingly but it is asking me to select table..but I want to copy entire db

Can some one please help if there is a way.

I tried creating backup/restore and import/export but I am getting access denied..

is there any other way for this??

Posted 1-Jul-15 4:08am

anupama962010

Add a Solution

Comments

virusstorm 1-Jul-15 10:19am

You can, but you need to add activity for every table which obviously isn't ideal. You do have a few other options. To point you in the right direction, I need to know if the dev database is used for active development or this is purely to copy down test data for developers to debug with. I won't want to give a technique that messes up your development and deployment process.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Abhinav S · Answer 1 · 2015-07-01T04:19:00

Solution 1

Transfer SQL Server Objects Task should help you.
Try samples at -
Transfer Database Task and Transfer SQL Server Objects Task in SSIS[^]
Transfer SQL Server Objects Task in SSIS 2008 R2 With Example[^]

Posted 1-Jul-15 4:19am

Abhinav S