Moving a SQL Server Database with SSIS's Transfer SQL Server Objects Task

On a recent client project, I was asked to move a backup file of a SQL Server 2008 database to another database. On the surface, that is about as simple of a task a SQL Server DBA can be asked to do. There were ten databases. No big deal I thought.

The databases were all for websites and were to be moved from one hosting server to another. The hosts will remain nameless to protect the innocent!

On the destination server, the issue became evident very soon. I created the blank database and I uploaded the .bak file and tried to restore. No dice, it can only restore its own backups, when a database is backed up using the web tool. I then tried creating the blank database then going in through SSMS. This was possible using the connection parameters. I figured I would outsmart the system by restoring the backup file with a replace. It said I had no rights to do so.

I was stuck with no answer. I decided to see if I could import tables somehow or doing some other task. I then though I would try SSIS (SQL Server Integration Services), where I hit pay dirt! The data was more important than the actual database information, so that is what I concentrated on.

SSIS offers a control flow item called “Transfer SQL Server Objects Task.” The obvious question is why not use a “Transfer Database Task”? Simple, the database task is good, but requires destination file source which I did not have. The “Transfer SQL Server Objects Task” was perfect.

First, you choose the source and destination connections. Next, choose the following options to move up the database contents.

I cleared out the connection information, but this is no simpler than any other SQL connection. You can choose more items than my example, but if all you need is the data transferred, this should do it.

So in summary, this task will help you move databases when you do not have the rights to move the data files (attach database) or restore (restore database).