A question about SMO.Restore

Question

I am using SMO.Restore to create a restore database form. Here is my Code:

Server srv = new Server(.......);

Restore rs = new Restore();
rs.NoRecovery = true;
BackupDeviceItem bdi = default(BackupDeviceItem);
bdi = new BackupDeviceItem("d:\\abc.bak", DeviceType.File);
rs.Devices.Add(bdi);
rs.Database = "abcd";

rs.SqlRestore(srv);

The file "d:\abc.bak " is backed by SMO.Backup . And the database name is "abc". Because I don't want to replace it, I want to restore to another database, I use:rs.Database = "abcd" , but not "abc". it's wrong. I change it to : rs.Database = "abc" . It can restore, but that's not what I want.

How to finish it?

Answer 1

Hello,

If the second database "abcd" already exists, you have to set the Restore.ReplaceDatabase Property to true.

The backup saves the origin database file names incl folder; but the point to the first database "abc". For restoring you have to relocate the database files, see Restore.RelocateFiles Property.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 2

I want to restore to a new database, and "abcd" is the new database name. Databse "abcd" is not exist. I add the relocate code, but is doesn't work too. The code is :

Server srv = new Server(.......);

Restore rs = new Restore();
rs.NoRecovery = false;
BackupDeviceItem bdi = default(BackupDeviceItem);
bdi = new BackupDeviceItem("d:\\abc.bak", DeviceType.File);
rs.Devices.Add(bdi);
rs.Database = "abcd";

rs.ReplaceDatabase = true;
RelocateFile rf = new RelocateFile();
rf.LogicalFileName = "abcd";
rf.PhysicalFileName = "d:\\abcd.Mdf";
RelocateFile rfl = new RelocateFile();
rfl.LogicalFileName = "abcd_Log";
rfl.PhysicalFileName = "d:\\abcd_Log.ldf";

rs.RelocateFiles.Add(rf);

rs.SqlRestore(srv);

And when I use rs.Database = "abc", "abc" is a exists database which I backed to the file "abc.bak", it works!

Answer 3

Hello,

1. You forgot to add "rfl" to te RelocateFile collection, you added onyl "rf"
2. You can't rename the LogicalFileName at this point, you have to use the origin name to address the file you want to relocate.

I created a simple database "Test" and with the code below it works fine:

        static void Main(string[] args)
        {
            Server srv = new Server(@".\SQLEXPRESS");
         
            Restore rs = new Restore();
            rs.NoRecovery = false;
            BackupDeviceItem bdi = default(BackupDeviceItem);
            bdi = new BackupDeviceItem(@"c:\temp\test-20120622.bak", DeviceType.File);
            rs.Devices.Add(bdi);
            rs.Database = "abcd";

            rs.ReplaceDatabase = true;
            RelocateFile rf = new RelocateFile();
            rf.LogicalFileName = "Test";
            rf.PhysicalFileName = @"C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\abcd.mdf";
            rs.RelocateFiles.Add(rf);

            RelocateFile rfl = new RelocateFile();
            rfl.LogicalFileName = "Test_Log";
            rfl.PhysicalFileName = @"C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\abcd.ldf";
            rs.RelocateFiles.Add(rfl);

            rs.SqlRestore(srv);

        }

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 4

I alter the code, and it works. Thanks! But I found another problem.

RelocateFile rf = new RelocateFile();
rf.LogicalFileName = "Test";

rf.LogicalFileName must as same as the backup file. If I don't know the LogicalFileName, It doesn't work. For example, the backup database's name is "test", its LogicalFileName is "test" and "test_log", If I use the code below:

RelocateFile rf = new RelocateFile();
rf.LogicalFileName = "abcd";
rf.PhysicalFileName = "D:\\abcd.mdf";
RelocateFile rf = new RelocateFile();
rf.LogicalFileName = "abcd_log";
rf.PhysicalFileName = "D:\\abcd.ldf";

and it doesn't work. When I change the LogicalFileName to "test" and "test_log", it works. If I don't know the file's LogicalFileName ,what can I do? I didn't find a method to get it.

Answer 5

You can use the ReadFileList method to get the file information from the backup file:

Server srv = new Server(@".\SQLEXPRESS");
         
Restore rs = new Restore();
rs.NoRecovery = false;
BackupDeviceItem bdi = default(BackupDeviceItem);
bdi = new BackupDeviceItem(@"c:\temp\test-20120622.bak", DeviceType.File);            
rs.Devices.Add(bdi);

DataTable files = rs.ReadFileList(srv);
foreach (DataRow row in files.Rows)
{
    Console.WriteLine(row[0].ToString());
}

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 6

It's Ok now! Thank you very much.