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