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; }