title: SQL Server database remote transfer - best method
body: <p>I have two databases, one on a remote server the other local. (SQL Server 2008)</p>
  
  <p>The database on my local server has the entire structure setup but no data. I would like to copy the data from the remote server to my server and I am wondering the best method in which to do this.</p>
  
  <p>The main issue I am experiencing is the user that I have to the remote database has limited permissions. I cannot read the stored procedures, user defined functions so when I use Import/Export wizard I do not get the schema etc. So a regular dump/restore is not working for me as it restores the tables without the Primary Keys/Foreign Keys and the stored procedures.</p>
  
  <p>I'd like to do this,</p>
  
  <pre><code>INSERT INTO localtable SELECT * FROM remotedb.table
  </code></pre>
  
  <p>I was having issues because of the <code>IDENTITY</code> fields and I had to explicitly name all of the columns. Also I am not sure if SQL Server Management Studio allows you to use two different databases, remote and local, so I was looking for any advice. </p>
  
  <p>I have also tried applications like SQL FTP and Backup and it fails because it runs out of memory (I have 16GB of memory on the machine and the DB is like 4GB). I also can use the SQL Server import/export wizard but then I don't get the schema information. I also tried SQL Compare from Red Gate and it runs into issues with the permissions. Unfortunately I do not have the time to request and gain access to a new user so I was hoping someone had a creative idea.</p>