title: Alter schema on SQL Server 2008 R2 Express database
body: <p>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!</p>
  
  <p>I basically have two SQL Server 2008 R2 database back-up files from a shared host: <code>database-1.bak</code> and <code>database-2.bak</code>. 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. <code>Database-1</code> has content, <code>database-2</code> is empty. I want to copy all data from <code>database-1</code> to <code>database-2</code> and then re-upload <code>database-2</code> back to the shared host. The two databases should then be exactly the same, but <code>database-1</code> should be owned by <code>database-1</code> schema and <code>database-2</code> by the <code>database-2</code> schema.</p>
  
  <p>I've logged into SQL Server Management Studio Express 2008 R2 as <code>sa</code> and restored these databases, so in there I now have two databases, one with a load of tables called e.g. <code>database-1.tblTest</code>, <code>database-1.tblTest2</code> etc; and the other with a load of tables called e.g. <code>database-2.tblTest</code>, <code>database-2.tblTest2</code> etc. So all of the tables in <code>database-1</code> are 'owned' by the <code>database-1</code> schema (the table names are prepended with <code>database-1.</code>) and all of the tables in <code>database-2</code> are 'owned' by the <code>database-2</code> schema (the table names are prepended with <code>database-2.</code>).</p>
  
  <p>I've tried right-clicking <code>database-1</code> and selecting <code>Tasks &gt; Export Data</code> and exporting it to <code>database-2</code>. The odd thing about this is that when it automatically maps the objects for exporting, it does this: </p>
  
  <pre><code>database-1.tblTest (on 'source' database-1) -&gt; database-1.tblTest (on 'destination' database-2).  
  </code></pre>
  
  <p>That's odd, because I would hope that it would do this instead:</p>
  
  <pre><code>database-1.tblTest (on 'source' database-1) -&gt; database-2.tblTest (on 'destination' database-2). 
  </code></pre>
  
  <p>I would hope that the tables in database-2 would keep their <code>database-2.</code> owner. If I map each of the tables like this:</p>
  
  <pre><code>database-1.tblTest -&gt; database-2.tblTest
  </code></pre>
  
  <p>The export fails. If I keep the mapping as default (<code>database-1.tblTest -&gt; database-1.tblTest</code>) it succeeds, but I then have a whole load of tables in <code>database-2</code> which are owned by the <code>database-1</code> schema.</p>
  
  <p>This is now where I'm at. <code>Database-1</code> is the same as it was when I restored it. But <code>database-2</code> now has a set of tables prepended by <code>database-2.</code> and the same set of tables prepended by <code>database-1.</code>.</p>
  
  <p>This is a problem because when I try to re-upload <code>database-2</code> back to the host server it errors because the schema is trying using <code>database-1</code> which is on the same server but a different account. So I need the tables in <code>database-2</code> to maintain their <code>database-2</code> schema owner but just be filled with the data from <code>database-1</code>.</p>
  
  <p>I've tried running a query like this to try to change the schema on my imported tables in <code>database-2</code> from <code>database-1</code> to <code>database-2</code>:</p>
  
  <pre><code>USE [database-2]
  ALTER SCHEMA [database-2] TRANSFER [database-1.tblTest]
  </code></pre>
  
  <p>However, this just errors:</p>
  
  <blockquote>
    <p>Msg 15151, Level 16, State 1, Line 2<br>
    Cannot find the object 'database-1.tableTest', because it
    does not exist or you do not have
    permission.</p>
  </blockquote>
  
  <p>I'm logged into Management studio as <code>sa</code> throughout, so permissions shouldn't be an issue.</p>
  
  <p>Can anyone please shed any light on how to just get this data into <code>database-2</code> and keep the schema owner the same?</p>
  
  <p>Any help would be massively appreciated, thank you!</p>