Simple Database Tricks with SQL Server Express

Copy database from one server to another

This is suprisingly easy.  On the source PC:

  • In SQL Server Management Studio Express, detach the database
  • Copy the .mdf and .log files for the database.  These are located in a path similar to:  “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data”
  • Attach the database again

On the target PC:

  • Copy the database .mdf and .log files to the SQL Server data path
  • In SQL Server Management Studio Express, attach the database
  • Voila!  There it is!

Copy a table from one database to another

This suggestion is courtesy of Dan Guzman.  It’s a real quick and simple way to copy a table, including the format and the data.  It doesn’t copy the constraints and indexes.  It does not work across servers, alas – at least, you would have to have “linked servers”, which is a mystery for another day…

SELECT *
INTO MyNewTable
FROM MyTable

 Here is another example:

INSERT INTO People(Name,Address,City,Phone)
SELECT ContactName, Address, City, Phone
FROM OtherTable.dbo.Customers 

Copy a table from one server to another

This is clumsy and inelegant and requires code, but it did the job for me. 

  • In SQL Server Management Studio Express, select the table to be copied.  Right-click and select “Script Table as/Create To/New Query Editor Window” to automatically generate SQL commands to create the table.
  • In Management Studio, open up a connection to the target database.  Open a SQL Query window and past a copy of the script generated from the source database.  Execute the SQL query.  This will generate the table in the correct format, but without data.
  • In Visual Studio, call a function similar to the one below to read the data from the first database and write it to the second.  This code is very specific to the table I was copying, but the idea is valid.  I got some ideas from edgemeister.
private void CopyData()
{
DataTable resultTable = null;
string query = “SELECT * From DeviceNames”;
string connectionString = @”Data Source=(local)\SQLExpress;Initial Catalog=DatabaseName;Integrated Security=True”;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand currentCmd = new SqlCommand(query, connection);
resultTable = new DataTable();
SqlDataReader reader = currentCmd.ExecuteReader(CommandBehavior.CloseConnection);
resultTable.Load(reader);
reader.Close();
}
connectionString = @”Data Source=RemoteName\SQLExpress;Initial Catalog=DatabaseName;Integrated Security=True”;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
foreach (DataRow row in resultTable.Rows)
{
SqlCommand currentCmd = new SqlCommand(“INSERT INTO DeviceNames(Timestamp,DeviceID,LongName,Units) VALUES (@value1,@value2,@value3,@value4)”, connection);
currentCmd.Parameters.AddWithValue(“@value1″, row[0]);
currentCmd.Parameters.AddWithValue(“@value2″, row[1]);
currentCmd.Parameters.AddWithValue(“@value3″, row[2]);
currentCmd.Parameters.AddWithValue(“@value4″, row[3]);
currentCmd.ExecuteNonQuery();
}
connection.Close();
}
}
About these ads

4 Comments

  1. Bill
    Posted April 4, 2008 at 4:54 pm | Permalink

    SELECT *
    INTO MyNewTable
    FROM MyTable

    Wow — so elegant and it works. Thank you!

  2. Sara
    Posted October 21, 2008 at 9:56 pm | Permalink

    Big thank you. You saved me hours of fumbling around.

  3. Ken
    Posted July 23, 2010 at 1:10 pm | Permalink

    Here’s how I copied a table from one server to another, constraints and data. NO CODING REQUIRED. I built on what I learned here and improvised further.
    All with MS SQL Server Manager Studio Express 2005.
    1) Create a new temp database on source server. Right-click on Databases “New Database…”.
    2) Script the creation of the source table.
    3) Change the “use” to point at the temp database & run it.
    4) Do the INSERT & SELECT, like:
    INSERT INTO TEMPdb.dbo.MyTable
    SELECT *
    FROM ORIGINALdb.dbo.MyTable;
    Steps 3 & 4 give the the structure & data. You’ve copied the table intact from one database to another on the same server.
    5) Now move the temp database to the destination server and attach it as described at the top of the article.
    6) Now repeat steps 2,3,4 copying from temp database to destination database. Remember to change the database & table names to reverse the process.

    I had to do this because I trashed the structure of a table in my test system so I had to clone a copy of the table from a production machine.

  4. Guy
    Posted December 21, 2010 at 12:18 pm | Permalink

    I used Linq to solve my problem:
    I have a list of cities which I wanted to copy from one project to another.

    I added a connection in VS2010 express to my secondDBFile.mdf, and used sqldatasource to get the data.

    in the Page_Load of my default page I used dataview that I get from SqlDataSource:

    DataSourceSelectArguments a=new DataSourceSelectArguments();

    DataView t = (DataView)SqlDataSource1.Select(a);

    foreach (DataRowView xx in t)
    {
    var nu=xx.Row.ItemArray;
    City newCity = new City();
    newCity.ID = Guid.Parse(nu[0].ToString());
    newCity.Name = nu[1].ToString();
    DB.getDB().Cities.InsertOnSubmit(newCity);
    }
    DB.update();

    I have a class called DB that handles the linq stuff: DB.getDB() get my linq’s dbdatacontext.


Post a Comment

Required fields are marked *
*
*

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: