Copy database offline mode

Question

I have beeen using SMO for a while for transfering databases. It was pretty easy to handle from c# by using the TransferDatabase task.

For my current project this gets to slow. I have to switch to offline mode, where the database is detached and atached.

What is the least troublesome way to start such a process from c#? I know that there is SSIS, but if possible I would not like to use it. Installing SSIS on my machine is a bit painfull.

Answer 1

Hi Malcolm, I do this work in my project. you can use SqlBulkCopy class in C#. this class is very powerful and you can copy all of data with best performance. this class make a bcp command and run it on your server. this commands are like this:

bcp pubs.dbo.authors out c: emppubauthors.bcp –n –Sstevenw –Usa –P
bcp pubs2.dbo.authors in c: emppubauthors.bcp –n –Sstevenw –Usa –P

there is many switches for this command. please see this code:

// Create source connection
SqlConnection source = new SqlConnection(connectionString);
// Create destination connection
SqlConnection destination = new SqlConnection(connectionString);

// Clean up destination table. Your destination database must have the
// table with schema which you are copying data to.
// Before executing this code, you must create a table BulkDataTable
// in your database where you are trying to copy data to.

SqlCommand cmd = new SqlCommand("DELETE FROM BulkDataTable", destination);
// Open source and destination connections.
source.Open();
destination.Open();
cmd.ExecuteNonQuery();
// Select data from Products table
cmd = new SqlCommand("SELECT * FROM Products", source);
// Execute reader
SqlDataReader reader = cmd.ExecuteReader();
// Create SqlBulkCopy
SqlBulkCopy bulkData = new SqlBulkCopy(destination);
// Set destination table name
bulkData.DestinationTableName = "BulkDataTable";
// Write data
bulkData.WriteToServer(reader);
// Close objects
bulkData.Close();
destination.Close();
source.Close();

Answer 2

Just issue the SQL Server Detach and Attach command from c#

Something like this...

var sourceConnectionString = "Data Source=sourceServer;Initial Catalog=MyDB;Integrated Security=True;";
var destinationConnectionString = "Data Source=destinationServer;Initial Catalog=MyDB;Integrated Security=True;";

var sourceLocalPath = @"C:\MSSQL\DATA\MyDB.mdf";
var destinationLocalPath = @"C:\MSSQL\DATA\MyDB.mdf";

var sourceRemotePath = @"\\ServerNameA\ShareName\MyDB.mdf";
var destinationRemotePath = @"\\ServerNameB\ShareName\MyDB.mdf";

// Make connections
var sourceConnection = new SqlConnection(sourceConnectionString);
sourceConnection.Open();
var destinationConnection = new SqlConnection(destinationConnectionString);
destinationConnection.Open();

// Detach source database
var sourceCommand = new SqlCommand("sp_detach_db MyDB", sourceConnection);
sourceCommand.ExecuteNonQuery();

// Detach destination database
var destinationCommand = new SqlCommand("sp_detach_db MyDB", destinationConnection);
destinationCommand.ExecuteNonQuery();

// Copy database file
File.Copy(sourceRemotePath, destinationRemotePath);

// Re-attach source database
sourceCommand = new SqlCommand("CREATE DATABASE MyDbName ON (FILENAME = '" + sourceLocalPath + "') FOR ATTACH", sourceConnection);
sourceCommand.ExecuteNonQuery();

// Re-attach destination database
destinationCommand = new SqlCommand("CREATE DATABASE MyDbName ON (FILENAME = '" + destinationLocalPath + "') FOR ATTACH", destinationConnection);
destinationCommand.ExecuteNonQuery();