1

I have two connection strings, the first is to an existing database, and the second is to a different SQL Server. I need to copy the entire first database to the second one. This includes creating all the tables, constraints, and keys. I want to use the SqlConnection object and do not have access to a hard drive.

EDIT 1:
I can create the db (code below) but I can't figure out how to programmatically set up the tables and then populate them. What is the best way to approach this?

public void Copy()
{
    var connectionForExisting = "REDACTED";
    var connectionForNew = "REDACTED";
    var targetDbName = "REDACTED";

    var source = new SqlConnection(connectionForExisting);
    var target = new SqlConnection(connectionForNew);

    try
    {
        source.Open();
    }
    catch (Exception e)
    {
        source.Close();
        return;
    }

    try
    {
        target.Open();
    }
    catch (Exception e)
    {
        source.Close();
        target.Close();
        return;
    }

    var myCommand = new SqlCommand(String.Format(CreateNewDatabaseScript, targetDbName), target);
    try
    {
        myCommand.ExecuteNonQuery();
    }
    catch (Exception e)
    {
        source.Close();
        target.Close();
        return;
    }
}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
James Madison
  • 337
  • 1
  • 4
  • 17
  • 1
    What is your question? – C.Evenhuis Oct 02 '15 at 19:19
  • Why would you want to do it this way, instead of for example restoring a backup into the second server? – James Z Oct 02 '15 at 19:20
  • @C.Evenhuis I apparently, derped. I edited the O.P. to include the question. – James Madison Oct 02 '15 at 19:23
  • @JamesZ I need to do it programmatically and I don't have access to any disk space. Is there a way to back up and restore that fits both of these criteria? – James Madison Oct 02 '15 at 19:24
  • I assumed you had backups of your database that you could just restore. You could also use replication to copy the database. Building this yourself sounds like a big task, depending of course how many objects and how much data you have. – James Z Oct 02 '15 at 19:40
  • Do you need to copy data or just copy the database's schema? – Ilya Chumakov Oct 02 '15 at 19:43
  • @JamesZ Yeah, I don't know what the best approach is. This was my attempt at starting it. And yes, there are a LOT of objects and data. – James Madison Oct 02 '15 at 19:44
  • @IlyaChumakov Both, but I could do it in two steps if that helps. – James Madison Oct 02 '15 at 19:44
  • 1
    @JamesMadison, If you have no possibility to place *.bak file on remote machine (probably it's a simplest way to restore db programmaticaly), try SMO and `Transfer` class http://stackoverflow.com/questions/268381/using-smo-to-copy-a-database-and-data – Ilya Chumakov Oct 02 '15 at 19:50

0 Answers0