C#/SQL:通过复制和替换数据库文件进行备份和还原?

Question

首先，这是一种分享知识，而不是一个问题。

我在使用备份和还原命令的默认方法创建数据库备份和还原时遇到了一些问题，因此我通过复制数据库文件并在需要时将它们取回来开发自己的数据库。

我会分享它以帮助他人。

Answer 1

解决方案：

首先，您必须知道，在任何复制或替换数据库文件之前，您必须将数据库设置为脱机状态，并在完成后使其重新联机。

1( 使用方法

// fullPath : the path for your database
// executablePath : the path for your exe folder
void setFullPath()
{
string executable = System.Reflection.Assembly.GetExecutingAssembly().Location;
executablePath = (System.IO.Path.GetDirectoryName(executable));
fullPath = executablePath + "\Database.mdf";
}
void takeOffline(string fullPath)
{
homeObject.connection.Open();
homeObject.cmd.CommandText = "ALTER DATABASE [" + fullPath + "] SET OFFLINE";
homeObject.cmd.ExecuteNonQuery();
homeObject.cmd.Clone();
}
void bringOnline(string fullPath)
{
homeObject.cmd.CommandText = "ALTER DATABASE [" + fullPath + "] SET ONLINE"; ;
homeObject.cmd.ExecuteNonQuery();
homeObject.cmd.Clone();
homeObject.connection.Close();
}

2(复制数据库文件/备份

bool getDatabaseCopy()
{
try
{
//
takeOffline(fullPath);
// copy database.mdf
copyDBMDF();
// copy database_log.ldf
copyDB_logLDF();
//
bringOnline(fullPath);
return true;
}
catch
{
//
}
return false;
}
// txtPath.txt :
// folder location to save database files in
void copyDBMDF()
{
string fileName = "Database.mdf";
string sourceFile = fullPath;
string targetPath = txtPath.Text;
// Use Path class to manipulate file and directory paths.
string destFile = System.IO.Path.Combine(targetPath, fileName);
// To copy a folder's contents to a new location:
// Create a new target folder, if necessary.
if (!System.IO.Directory.Exists(targetPath))
{
System.IO.Directory.CreateDirectory(targetPath);
}
// To copy a file to another location and 
// overwrite the destination file if it already exists.
System.IO.File.Copy(sourceFile, destFile, true);
}
void copyDB_logLDF()
{
string fileName = "Database_log.ldf";
string sourcePath = executablePath;
string targetPath = txtPath.Text;
// Use Path class to manipulate file and directory paths.
string sourceFile = System.IO.Path.Combine(sourcePath, fileName);
string destFile = System.IO.Path.Combine(targetPath, fileName);
// To copy a folder's contents to a new location:
// Create a new target folder, if necessary.
if (!System.IO.Directory.Exists(targetPath))
{
System.IO.Directory.CreateDirectory(targetPath);
}
// To copy a file to another location and 
// overwrite the destination file if it already exists.
System.IO.File.Copy(sourceFile, destFile, true);
}

3(将当前数据库文件替换为您复制/还原的文件

bool restoreTheBackup()
{
try
{
//
takeOffline(fullPath);
// load .mdf
loadMDFDatabaseFile();
// load _log.ldf
loadLDFDatabaseFile();
//
bringOnline(fullPath);
return true;
}
catch
{
//
}
return false;
}
// txtPath.txt :
// location to get database files from to replace with current files.
void loadLDFDatabaseFile()
{
string fileName = "Database_log.ldf";
string targetPath = executablePath;
string sourcePath = txtPath.Text;
// Use Path class to manipulate file and directory paths.
string sourceFile = System.IO.Path.Combine(sourcePath, fileName);
string destFile = System.IO.Path.Combine(targetPath, fileName);
// To copy a folder's contents to a new location:
// Create a new target folder, if necessary.
if (!System.IO.Directory.Exists(targetPath))
{
System.IO.Directory.CreateDirectory(targetPath);
}
// To copy a file to another location and 
// overwrite the destination file if it already exists.
System.IO.File.Copy(sourceFile, destFile, true);
}
void loadMDFDatabaseFile()
{
string fileName = "Database.mdf";
string targetPath = executablePath;
string sourcePath = txtPath.Text;
// Use Path class to manipulate file and directory paths.
string sourceFile = System.IO.Path.Combine(sourcePath, fileName);
string destFile = System.IO.Path.Combine(targetPath, fileName);
// To copy a folder's contents to a new location:
// Create a new target folder, if necessary.
if (!System.IO.Directory.Exists(targetPath))
{
System.IO.Directory.CreateDirectory(targetPath);
}
// To copy a file to another location and 
// overwrite the destination file if it already exists.
System.IO.File.Copy(sourceFile, destFile, true);
}