public void TestGenerateRestoreSqlWithoutLdfPath()
{
var file = new BackupFile
{
Created = new DateTime(2014, 6, 19, 10, 19, 0),
DatabaseName = "comsec",
FileList = new List<FileListEntry>
{
new FileListEntry
{
LogicalName = "comsec",
PhysicalName = @"D:\SQL\comsec.mdf",
Type = "D"
},
new FileListEntry
{
LogicalName = "comsec_log",
PhysicalName = @"L:\SQL\comsec_log.LDF",
Type = "L"
}
},
FileName = "comsec_backup_2014_06_19_010004_7953268",
Length = 100000
};
var query = SqlService.GenerateRestoreSql(file, @"D:\SQL\");
Assert.AreEqual(@"RESTORE DATABASE [comsec]
FROM DISK = 'comsec_backup_2014_06_19_010004_7953268' WITH REPLACE,
MOVE 'comsec' TO 'd:\sql\comsec.mdf',
MOVE 'comsec_log' TO 'd:\sql\comsec_log.ldf'", query);
}
/// <summary>
/// Gets the logical names from the backup file
/// </summary>
/// <param name="server">The server.</param>
/// <param name="backupFile">The backup file.</param>
/// <returns></returns>
public IEnumerable<FileListEntry> GetLogicalNames(string server, BackupFile backupFile)
{
var connectionString = $@"Data Source={server};Integrated Security=SSPI;Initial Catalog=master;";
using (var connection = new SqlConnection(connectionString))
{
var sql = $@"RESTORE FILELISTONLY FROM DISK = '{backupFile.FileName}'";
return connection.Query<FileListEntry>(sql);
}
}
/// <summary>
/// Generates a restore database from file to specified location SQL statement.
/// </summary>
/// <param name="backupFile">The backup file.</param>
/// <param name="mdfRestorePath">The restore path for MDF files.</param>
/// <param name="ldfRestorePath">The restore path for LDF (optional, MDF path used when not set).</param>
/// <returns>A restore database from file SQL statement</returns>
/// <remarks>This method is public only so that it can be unit tested</remarks>
public static string GenerateRestoreSql(BackupFile backupFile, string mdfRestorePath, string ldfRestorePath = null)
{
// LDF restore path defaults to the MDF location when not set
ldfRestorePath = ldfRestorePath ?? mdfRestorePath;
var builder = new StringBuilder()
.Append("RESTORE DATABASE [").Append(backupFile.DatabaseName).AppendLine("]")
.Append("FROM DISK = '").Append(backupFile.FileName).AppendLine("' WITH REPLACE,");
for (var i = 0; i < backupFile.FileList.Count; i++)
{
var fileListEntry = backupFile.FileList[i];
var fileName = Path.GetFileName(fileListEntry.PhysicalName) ?? string.Empty;
string fullFileName;
var isDataFile = fileListEntry.Type == "D";
if (isDataFile)
{
fullFileName = Path.Combine(mdfRestorePath, fileName).ToLower();
if (!fullFileName.EndsWith(".mdf"))
{
fullFileName += ".mdf";
}
}
else
{
fullFileName = Path.Combine(ldfRestorePath, fileName).ToLower();
if (!fullFileName.EndsWith(".ldf"))
{
fullFileName += ".ldf";
}
}
builder.Append("MOVE '").Append(fileListEntry.LogicalName).Append("' TO '").Append(fullFileName).Append("'");
if (i != backupFile.FileList.Count - 1)
{
builder.AppendLine(",");
}
}
return builder.ToString();
}
/// <summary>
/// Parses the directory and returns all the database backups inside.
/// </summary>
/// <param name="directory">The directory.</param>
/// <returns></returns>
public IList<BackupFile> ParseDirectory(string directory)
{
var backupFileList = new List<BackupFile>();
var filePaths = Directory.GetFiles(directory, "*.bak");
foreach (var file in filePaths)
{
var fi = new FileInfo(file);
var l = fi.Name.IndexOf("_backup", StringComparison.Ordinal);
var dbName = fi.Name.Substring(0, l);
var item = new BackupFile
{
Created = fi.CreationTime,
DatabaseName = dbName,
FileName = fi.FullName,
Length = fi.Length
};
backupFileList.Add(item);
}
return backupFileList;
}
/// <summary>
/// Restores the specified backup file to the given server.
/// This method uses a trusted SQL connection
/// </summary>
/// <param name="server">The server.</param>
/// <param name="backupFile">The backup file.</param>
/// <param name="dataFilesPath">The path to restore physical files to</param>
/// <param name="logFilesPath">The LDF file path.</param>
public void Restore(string server, BackupFile backupFile, string dataFilesPath, string logFilesPath = null)
{
var connectionString = $"Data Source={server};Integrated Security=SSPI;Initial Catalog=master;";
using (var connection = new SqlConnection(connectionString))
{
if (connection.DatabaseExists(backupFile.DatabaseName))
{
connection.DropDatabase(backupFile.DatabaseName);
}
if (!connection.DatabaseExists(backupFile.DatabaseName))
{
var sql = GenerateRestoreSql(backupFile, dataFilesPath, logFilesPath);
connection.Execute(sql, commandTimeout: 0);
}
}
}
private static List<BackupFile> CreateBackupFiles()
{
var files = new List<BackupFile>();
var file1 = new BackupFile { DatabaseName = "Database", Length = 2 };
var file2 = new BackupFile { DatabaseName = "Database", Length = 3 };
files.Add(file1);
files.Add(file2);
return files;
}