Backing up a database from PROD to Dev

[mike509123]

I saw the following code on the codeproject which allows you to copy a table from one server to another server. Is there a simple way to do the same thing but instead copy a database?

SqlConnectionStringBuilder cb = new SqlConnectionStringBuilder();
cb.DataSource = "SQLProduction";
cb.InitialCatalog = "Sales";
cb.IntegratedSecurity = true;
SqlConnection cnn = new SqlConnection(cb.ConnectionString);

// Getting source data
SqlCommand cmd = new SqlCommand("SELECT * FROM PendingOrders",cnn);
cnn.Open();
SqlDataReader rdr = cmd.ExecuteReader();

// Initializing an SqlBulkCopy object
SqlBulkCopy sbc = new SqlBulkCopy("server=.;database=ProductionTest;" +
"Integrated Security=SSPI");

// Copying data to destination
sbc.DestinationTableName = "Temp";
sbc.WriteToServer(rdr);

// Closing connection and the others
sbc.Close();
rdr.Close();
cnn.Close();

 

[jmeckley]

technically your not copying the table from 1 db to another. Your just copying the data within the table from 1 db to another. The db and tables already exist on both servers.

look into Data Transformation Services (DTS) for SQL Server 2K. there is a copy database object you can use to accomplish this. This is tedious and slow, but it works. There is also an administrative option to detach/attach database files to for SQL Server. This only takes seconds.

Jason Meckley
Programmer
Specialty Bakers, Inc.