Change to Sql2008.Database to support WITH MOVE Restores

Oct 29, 2009 at 6:34 PM


I've made the changes that support the equivalent of running a RESTORE WITH MOVE in (T-SQL). This is a scenario where you want a new MDF and LDF path from what is referenced in the .BAK file. This is very helpful for Staging, Development or local builds where you want to ensure that the latest ASP.NET build, for example, also matches an Upgraded Database build. This ensures for an existing database, for which changes are applied, there is always a common starting point when doing a build.

Change to the Database.cs file in Sql2008 SOLUTION.

Add this import:

    using DATA = System.Data;

Add the following properties:

        /// <summary>
/// Set to true to restore the database's files to a new location
/// </summary>
public bool MoveFiles { get; set; }


        /// <summary>
/// Sets the RowDataFilePath.
/// </summary>
[TaskAction(CreateTaskAction, false)]
[TaskAction(RestoreTaskAction, false)]
public ITaskItem RowDataFilePath { get; set; }

then modify the Restore method (I realize this is a breaking addition to the ReplaceDatabase parameter but I haven't found this useful in my efforts to date)...


        private void Restore() {
if (this.DatabaseItem == null) {
this.Log.LogError("DatabaseItem is required");

this.LogTaskMessage(string.Format(CultureInfo.CurrentCulture, "Restoring SQL {2}: {0} from {1}", this.DatabaseItem.ItemSpec, this.DataFilePath.GetMetadata("FullPath"), this.RestoreAction));
Restore sqlRestore = new Restore { Database = this.DatabaseItem.ItemSpec, Action = this.restoreAction };
sqlRestore.Devices.AddDevice(this.DataFilePath.GetMetadata("FullPath"), DeviceType.File);
sqlRestore.PercentCompleteNotification = this.NotificationInterval;
sqlRestore.ReplaceDatabase = true;
sqlRestore.PercentComplete += this.ProgressEventHandler;

// New Support for RESTORE WITH MOVE
if (this.MoveFiles) {
if (this.restoreAction != RestoreActionType.Database) {
this.Log.LogError("RestoreAction must be set to \'null\' or \'Database\' if MoveFiles is true.");
if (this.RowDataFilePath == null || this.LogFilePath == null) {
this.Log.LogError("RowDataFilePathRoot and LogFilePathRoot must be specified if MoveFiles is true.");

/// Must use ReadFileList to look at the current logical names of the files. Note: this solution
/// currently only works for straightforward databases, without filegroups, without multiple RowDatafiles,
/// on the primary filegroup, etc.

DATA.DataTable ReadFileListResult = sqlRestore.ReadFileList(this.sqlServer);
string MDFLogicalName = ReadFileListResult.Select(" Type = 'D'")[0]["LogicalName"].ToString();
string LDFLogicalName = ReadFileListResult.Select(" Type = 'L'")[0]["LogicalName"].ToString();

if (MDFLogicalName == null || LDFLogicalName == null) {
this.Log.LogError("Unable to read FileList from backup referenced.");

sqlRestore.RelocateFiles.Add(new RelocateFile(MDFLogicalName, this.RowDataFilePath.GetMetadata("FullPath")));
sqlRestore.RelocateFiles.Add(new RelocateFile(LDFLogicalName, this.LogFilePath.GetMetadata("FullPath")));

if (this.ReplaceDatabase) {
sqlRestore.ReplaceDatabase = true;
if (string.IsNullOrEmpty(this.LogName)) {
this.LogName = this.DatabaseItem.ItemSpec + "_log";

if (this.LogFilePath == null) {
this.Log.LogError("LogFilePath must be specified if ReplaceDatabase is true.");

sqlRestore.RelocateFiles.Add(new RelocateFile(this.DatabaseItem.ItemSpec, this.DataFilePath.GetMetadata("FullPath")));
sqlRestore.RelocateFiles.Add(new RelocateFile(this.LogName, this.LogFilePath.GetMetadata("FullPath")));


You'll need to modify the MSBuild.ExtensionPack.Sql2008.xsd to include reference to the new "MoveFiles" parameter in the task per the below:

<xs:attribute name="MoveFiles" />

Note: I think Mike and the guys on this project can clean this up a little bit but the hack works currently for what I need and thought I would share to help it maybe get implemented a little more quickly in a release.

Thanks for the project guys! Great work and progress so far!

Oct 31, 2009 at 9:40 AM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.