Software Technology Tips

To Move a database from one location to another we basically need to do three things

• Detach the Detabase from SQL Server
• Move the datafile and log file to the new location
• Attach the database back to SQL Server

// Move the database files to new destination

private void MoveDataBaseFiles()

{

try

{

//sp_helpfile is a System Store Procedure that returns the current location of all the files of the database

string sQryHelp = "use " + myDBName + " exec sp_helpfile";

DataTable dtHelp = GetDataTable(sQryHelp);

String sPathDatafile = "";

String sPathLogFile = "";

String sDataFileName = "";

String sLogFileName = "";

if (dtHelp.Rows.Count > 0)

{

sPathDatafile = dtHelp.Rows[0]["filename"].ToString().Trim();

sPathLogFile = dtHelp.Rows[1]["filename"].ToString().Trim();

sDataFileName = dtHelp.Rows[0]["name"].ToString().Trim() + ".mdf";

sLogFileName = dtHelp.Rows[1]["name"].ToString().Trim() + ".ldf";

}

// Destination Path for DataFile

String sDataDestination = Path.Combine(myDBPath, sDataFileName);

//Destination Path for Log file

String sLogDestination = Path.Combine(myDBPath, sLogFileName);

//This function detaches the database from server

DetachDataBase();

//Check if the path Exists

if (!Directory.Exists(myDBPath))

{

//If Not then Create the Directory where Db has to be moved

Directory.CreateDirectory(myDBPath);

}

//Move Data File

if (File.Exists(sPathDatafile))

{

File.Move(sPathDatafile, sDataDestination);

}

//Move Log File

if (File.Exists(sPathLogFile))

{

File.Move(sPathLogFile, sLogDestination);

}

// Attach the database to the server

AttachDataBase(sDataDestination, sLogDestination);

}

catch (Exception ee)

{

WriteErrorLog(ee, "DBSQLSCRIPTS - MoveDatabaseFiles");

MessageBox.Show(ee.Message);

}

}

private void DetachDataBase()

{

string sQryDetach = "use master " +

"exec sp_detach_db " + myDBName;

SqlCommand cmdDetach = new SqlCommand(sQryDetach, sqlConn);

int i = cmdDetach.ExecuteNonQuery();

cmdDetach.Dispose();

}

private void AttachDataBase(string sDataFile, string sLogFile)

{

string sQryAttach = "use master " +

"exec sp_attach_db '" + myDBName + "', " +

"'" + sDataFile + "','" + sLogFile + "'";

SqlCommand cmdAttach = new SqlCommand(sQryAttach, sqlConn);

int i = cmdAttach.ExecuteNonQuery();

cmdAttach.Dispose();

}