How Do I Copy and Overwrite a Database in SQL Server 2005 with SSIS?

Question

I HAVE to be missing something really simple here!

I have a database in my development environment called Project. I have a database in my test environment called Project_UAT.

I've created an SSIS package that successfully copied my database from Project to Project_UAT. I'm pretty sure this eliminates most permission and configuration issues.

Now, I want to re-create the package and this time allow it to overwrite the destination, which is Project_UAT. This is simply because from time to time I want to click a button in the Microsoft SQL Management Studio that pushes the new database schema, data, users, and everything, out to my testing environment. I WANT to overwrite the data.

So I create the package just like I did before, but this time I specify the already-existing database name as the "Destination database" and I select the radio button called "Drop any database on the destination server with the same name, then continue with the database transfer, overwriting existing database files."

I click Next, and what does it tell me?

"Database name already exists at destination"

Well, I KNOW! I just told you I want to overwrite it!

How do I make this work?

Answer 1

Hi Chris,

Not sure if I am missing the point but why do you not use a task to drop/delete the existing database prior to your deployment step?

Perhaps you could qualify the SSIS Component Tasks you are using within your SSIS package.

Cheers, John

Answer 2

You can add an Execute SQL Task into the Control Flow to drop the database. Just set the SQLStatement property to

DROP DATABASE Project_UAT

After this step is executed the new copy of the Project_UAT database won't have to overwrite the old one.

Answer 3

Guys this is a common sense solution. A lot of complexity for nothing.

Backup the destination database you want to copy to and delete the destination database.

Open the copy database wizard and follow the steps.

Use the detach and attach method.

When you get to the configure destination database use the option if destination database exists select drop database on destination server with same name.

Now it will continue to the next screen.

But this only works if you delete the destination database first before starting the wizard.

I may have missed something but this worked for me.

Answer 4

I have this problem because I deleted the database before hand. The database is not in the destination folder, but SQL Server 2008 still thinks it is there. Refresh didn't work. And SQL Server wouldn't honor the selection of "Drop any database on the destination server..." It just complained that the database already existed.