static public void BackupDB(string sDatabaseName, string sBackupFilename)
{
if (!Globals.DatabaseExists(sDatabaseName))
{
return;
}
// If the optional filename is not included, default to the database name.
if (sBackupFilename == null)
{
sBackupFilename = sDatabaseName + ".bak";
}
// If the filename does not include the path, default to the DbDir path.
if (sBackupFilename.IndexOf('\\') == -1)
{
sBackupFilename = Globals.DbFolder + sBackupFilename;
}
string ssql = "backup database [" + sDatabaseName + "] to disk = " + Globals.CSqlN(sBackupFilename) + " with init";
using (SqlConnection oConn = Globals.Conn)
using (SqlCommand oCommand = new SqlCommand(ssql, oConn))
{
oCommand.ExecuteNonQuery();
}
}
static public void AttachDB(string sDatabaseName)
{
using (SqlConnection oConn = Globals.Conn)
{
// Remove the extension from the end if it is passed.
if (sDatabaseName.EndsWith(".mdf"))
{
sDatabaseName = sDatabaseName.Substring(0, sDatabaseName.Length - 4);
}
string sDBPath = Globals.DbFolder;
string sDbFile = sDBPath + sDatabaseName + ".mdf";
if (!File.Exists(sDbFile))
{
sDBPath = "c:\\Program Files\\FieldWorks\\Data\\"; // Location before FW4.9
sDbFile = sDBPath + sDatabaseName + ".mdf";
if (!File.Exists(sDbFile))
{
Console.WriteLine("Neither file \"" + Globals.DbFolder + sDatabaseName + ".mdf\" nor \"" + sDbFile + "\" exist.");
return;
}
}
// Make sure db files are not readonly. SQL Server does nasty things in this case.
if ((File.GetAttributes(sDbFile) & FileAttributes.ReadOnly) == FileAttributes.ReadOnly)
{
File.SetAttributes(sDbFile, File.GetAttributes(sDbFile) & ~FileAttributes.ReadOnly);
}
string sLogFile = "";
if (File.Exists(sDBPath + sDatabaseName + ".ldf"))
{
sLogFile = sDBPath + sDatabaseName + ".ldf";
}
if (File.Exists(sDBPath + sDatabaseName + "_log.ldf"))
{
sLogFile = sDBPath + sDatabaseName + "_log.ldf";
}
if (sLogFile.Length > 0 && (File.GetAttributes(sLogFile) & FileAttributes.ReadOnly) == FileAttributes.ReadOnly)
{
File.SetAttributes(sLogFile, File.GetAttributes(sLogFile) & ~FileAttributes.ReadOnly);
}
string ssql =
"exec sp_attach_db " +
" @dbname = " + Globals.CSqlN(sDatabaseName) + ", " +
" @filename1 = " + Globals.CSqlN(sDbFile);
if (sLogFile.Length > 0)
{
ssql += ", @filename2 = " + Globals.CSqlN(sLogFile);
}
SqlCommand oCommand = new SqlCommand(ssql, oConn);
oCommand.ExecuteNonQuery();
}
}
static public void LoadDB(string sDatabaseName)
{
// Make sure the XML file exists.
string sXMLFilename = Globals.DbFolder + sDatabaseName + ".xml";
if (!File.Exists(sXMLFilename))
{
Console.WriteLine("Cannot load " + sDatabaseName + " because the XML file " + sXMLFilename + " does not exist.");
return;
}
string sTemplateMDF = Globals.TemplateFolder + "BlankLangProj.mdf";
if (!File.Exists(sTemplateMDF))
{
Console.WriteLine("Cannot load " + sDatabaseName + " because the template file " + sTemplateMDF + " does not exist.");
return;
}
string sTemplateLDF = Globals.TemplateFolder + "BlankLangProj_log.ldf";
if (!File.Exists(sTemplateLDF))
{
Console.WriteLine("Cannot load " + sDatabaseName + " because the template file " + sTemplateLDF + " file does not exist.");
return;
}
// See if the database exists.
string ssql = "select dbid from master.dbo.sysdatabases where name = " + Globals.CSqlN(sDatabaseName);
using (SqlConnection oConn = Globals.Conn)
{
using (SqlCommand oCommand = new SqlCommand(ssql, oConn))
{
object o = oCommand.ExecuteScalar();
if (o != null)
{
DeleteDB(sDatabaseName);
}
}
}
File.Copy(sTemplateMDF, Globals.DbFolder + sDatabaseName + ".mdf", true);
File.Copy(sTemplateLDF, Globals.DbFolder + sDatabaseName + "_log.ldf", true);
AttachDB(sDatabaseName);
System.Diagnostics.ProcessStartInfo psi = new System.Diagnostics.ProcessStartInfo("loadxml", "-i \"" + sXMLFilename + "\"");
psi.WindowStyle = System.Diagnostics.ProcessWindowStyle.Hidden;
System.Diagnostics.Process oProcess = System.Diagnostics.Process.Start(psi);
oProcess.WaitForExit();
}
static public void DetachDB(string sDatabaseName)
{
if (!Globals.DatabaseExists(sDatabaseName))
{
return;
}
string ssql = "exec sp_detach_db " + Globals.CSqlN(sDatabaseName);
using (SqlConnection oConn = Globals.Conn)
{
SqlCommand oCommand = new SqlCommand(ssql, oConn);
oCommand.ExecuteNonQuery();
}
}
static public bool DatabaseExists(string sDatabaseName)
{
string ssql = "select name from sysdatabases where name = " + Globals.CSqlN(sDatabaseName) + "";
using (SqlConnection oConn = Globals.Conn)
using (SqlCommand oCommand = new SqlCommand(ssql, oConn))
using (SqlDataReader oReader = oCommand.ExecuteReader())
{
if (oReader.Read() && oReader.GetString(0).ToLower() == sDatabaseName.ToLower())
{
return(true);
}
}
Console.WriteLine("Database \"" + sDatabaseName + "\" does not exist.");
return(false);
}
static public void RestoreDB(string sDatabaseName, string sBackupFilename)
{
// If the optional filename is not included, default to the database name.
if (sBackupFilename == null)
{
sBackupFilename = sDatabaseName + ".bak";
}
// If the filename does not include the path, default to the DbDir path.
if (sBackupFilename.IndexOf('\\') == -1)
{
sBackupFilename = Globals.DbFolder + sBackupFilename;
}
if (!File.Exists(sBackupFilename))
{
Console.WriteLine("Cannot restore " + sBackupFilename + " because the backup file does not exist.");
return;
}
using (SqlConnection oConn = Globals.Conn)
{
// Under some rare situations, the restore fails if the database already exists, even with move
// and replace. It may have something to do with duplicate logical names, although that's not
// consistent. So to be safe, we'll delete the database first if it is present.
string ssql = "If exists (select name from sysdatabases where name = '" + sDatabaseName + "') " +
"begin " +
"drop database [" + sDatabaseName + "] " +
"end";
using (SqlCommand oCommand = new SqlCommand(ssql, oConn))
{
oCommand.ExecuteNonQuery();
}
// Get the list of the logical files in the backup file and reset the path for each one.
ssql = "restore filelistonly from disk = " + Globals.CSqlN(sBackupFilename);
using (SqlCommand oCommand = new SqlCommand(ssql, oConn))
using (SqlDataReader oReader = oCommand.ExecuteReader())
{
ssql = "restore database [" + sDatabaseName + "] from disk = " + Globals.CSqlN(sBackupFilename) + " with replace";
int iLogFile = 0;
while (oReader.Read())
{
string sFilename = Globals.DbFolder + sDatabaseName;
if (oReader["Type"].ToString().ToUpper() == "D")
{
sFilename += ".mdf";
}
else
{
string sExt = (oReader["PhysicalName"].ToString().IndexOf("_log") > -1) ? "_log.ldf" : ".ldf";
if (iLogFile++ == 0)
{
sFilename += "_log.ldf";
}
else
{
sFilename += iLogFile + "_log.ldf";
}
}
ssql += ", move " + Globals.CSqlN(oReader["LogicalName"].ToString()) + " to " + Globals.CSqlN(sFilename);
}
}
using (SqlCommand oCommand = new SqlCommand(ssql, oConn))
{
// Default is 30 which is too short when restoring a big database from SQL Server 2000
// on slower machines. 0 is unlimited time.
oCommand.CommandTimeout = 0;
oCommand.ExecuteNonQuery();
}
}
}
