Alter schema on SQL Server 2008 R2 Express database

Question

Sorry that this post is so long, for such a straight-forward issue. I've been working on this now for almost 3 days, and it's driving me totally crazy, so I'm trying to put as much detail here as I possibly can so that someone might be able to get me out of this nightmare!

I basically have two SQL Server 2008 R2 database back-up files from a shared host: database-1.bak and database-2.bak. Each of these databases were set up on different shared hosting accounts on the same shared server with one database user each (named 'database-1' and 'database-2' accordingly). The structure of the databases is identical, but the data isn't. Database-1 has content, database-2 is empty. I want to copy all data from database-1 to database-2 and then re-upload database-2 back to the shared host. The two databases should then be exactly the same, but database-1 should be owned by database-1 schema and database-2 by the database-2 schema.

I've logged into SQL Server Management Studio Express 2008 R2 as sa and restored these databases, so in there I now have two databases, one with a load of tables called e.g. database-1.tblTest, database-1.tblTest2 etc; and the other with a load of tables called e.g. database-2.tblTest, database-2.tblTest2 etc. So all of the tables in database-1 are 'owned' by the database-1 schema (the table names are prepended with database-1.) and all of the tables in database-2 are 'owned' by the database-2 schema (the table names are prepended with database-2.).

I've tried right-clicking database-1 and selecting Tasks > Export Data and exporting it to database-2. The odd thing about this is that when it automatically maps the objects for exporting, it does this:

database-1.tblTest (on 'source' database-1) -> database-1.tblTest (on 'destination' database-2).  

That's odd, because I would hope that it would do this instead:

database-1.tblTest (on 'source' database-1) -> database-2.tblTest (on 'destination' database-2). 

I would hope that the tables in database-2 would keep their database-2. owner. If I map each of the tables like this:

database-1.tblTest -> database-2.tblTest

The export fails. If I keep the mapping as default (database-1.tblTest -> database-1.tblTest) it succeeds, but I then have a whole load of tables in database-2 which are owned by the database-1 schema.

This is now where I'm at. Database-1 is the same as it was when I restored it. But database-2 now has a set of tables prepended by database-2. and the same set of tables prepended by database-1..

This is a problem because when I try to re-upload database-2 back to the host server it errors because the schema is trying using database-1 which is on the same server but a different account. So I need the tables in database-2 to maintain their database-2 schema owner but just be filled with the data from database-1.

I've tried running a query like this to try to change the schema on my imported tables in database-2 from database-1 to database-2:

USE [database-2]
ALTER SCHEMA [database-2] TRANSFER [database-1.tblTest]

However, this just errors:

Msg 15151, Level 16, State 1, Line 2
Cannot find the object 'database-1.tableTest', because it does not exist or you do not have permission.

I'm logged into Management studio as sa throughout, so permissions shouldn't be an issue.

Can anyone please shed any light on how to just get this data into database-2 and keep the schema owner the same?

Any help would be massively appreciated, thank you!

Answer 1

Your syntax is just a little off... because you enclose schema + table in a single square bracket, it assumed you had a table name with a period and a dash in it. Try this one:

ALTER SCHEMA [database-2] TRANSFER [database-1].[tblTest];

Strongly recommend staying away from dashes in database, schema, object and column names. Using them makes square brackets necessary and can complicate a lot of other tools and scripts that aren't expecting them.