attempting to restore to a new DB and it keeps trying to save the mdf file as the original mdf file despite using the RelocateFiles stuff

Question

This is using SQL Server 2005.

I've had several problems and this is where I've ended up.

1. Despite the claim that this will create a new DB it doesn't.  I was getting errors about the new DB not having the specified logical file name (despite the new DB not existing).  At the time I was specifying the logical file name as it appeared in the backup.
2. if I manually create the DB I still get the error as above unless I manually change the logical file name via sql management studio.  This is meant to be an automated solution so that is not accceptable.
3. I am now giving it the logical file name of the DB that I want to restore onto (if it doesn't exist I create it), but now it's trying to save the file with the old physical file name, which is absolutely *not* what I want.

My instincts tell me I need to change the logical file name in the database I'm wanting to restore onto, but I don't know how to do this via SMO.  All of the explanations I've seen are through the SQL Management Studio and that is not acceptable as this needs to be an automated solution.

I have copy/pasted the code from the msdn examples verbatim changing only the dbName and the .bak file location and it has consistently failed.

Here is the code in it's current incarnation.

var serverName = @"myServer";
            var dbName = "MIQDesignTest3";
            var backupFileName = @"D:\myDirectory\MIQDesignTest.bak";

            var server = new Server(serverName);
            var createDB = !server.Databases.Contains(dbName);

            if (createDB)
            {
                var newDB = new Database(server, dbName);
                newDB.Create();
            }

            var bakDevice = new BackupDeviceItem(backupFileName, DeviceType.File);
            var restore = new Restore();

            restore.Database = dbName;
            restore.NoRecovery = true;
            restore.Action = RestoreActionType.Database;
            restore.Devices.Add(bakDevice);
            restore.ReplaceDatabase = true





            var mdf = new RelocateFile();
            var ldf = new RelocateFile();

            mdf.LogicalFileName = dbName;
            mdf.PhysicalFileName = String.Format(@"{0}\{1}.mdf", server.Information.MasterDBPath, dbName);
            ldf.LogicalFileName = dbName + "_log";
            ldf.PhysicalFileName = String.Format(@"{0}\{1}.ldf", server.Information.MasterDBPath, dbName);

            Console.WriteLine(mdf.PhysicalFileName);
            Console.WriteLine(ldf.PhysicalFileName);

            restore.RelocateFiles.Add(mdf);
            restore.RelocateFiles.Add(ldf);
            restore.SqlRestore(server);

What do I need to do to make this work?

Answer 1

Hello,

Please, could you give us some more informations ?
- the SQL Server version for the backuped database
- the version of the instance in which you want to restore your database

I think that you should use for the logical names the same name as in the backuped database ( the logical name is not always the same as the name of the database for myself , for a database BaseTest, the logical name is BaseTest_Data)

I suggest you not to use var in your VC# code when you post it : it is unreadable.
I know : it is quicker to write, with shorter code lines but difficult to debug when there is a problem

I will try to see what are the columns of the datatable returned by Restore.ReadMediaHeader(). I think you should find the logical name of the .mdf , .ndf, .ldf files.
The definition of these columns would be useful in the BOL but they are nearly always not given ( for example, EnumNamespaces() for the XmlSchemaCollection )

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.