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…
Here is another example:
INSERT INTO People(Name,Address,City,Phone)
SELECT ContactName, Address, City, Phone
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.