Can't restore a .bak file to a database

Question

I have successfully restored a database using smo restore method. But when I try to restore the same .bak file to another database it returns an exception as seen below

I have created the .bak file from a database template I used which is GC_BranchName. GC_Muntinlupa is the first database I have created and restored the .bak file on to it successfully. Can anyone advise me on this?

Here is my restore code:

bool RestoreDB(string name)
    {

        try
        {
            var connection = new ServerConnection(Properties.Settings.Default.Well);
            var sqlServer = new Server(connection);
            var rstDatabase = new Restore();

            rstDatabase.Database = name;
            rstDatabase.Action = RestoreActionType.Database;
            rstDatabase.Devices.AddDevice(System.Environment.CurrentDirectory + "\\GC.bak", DeviceType.File);
            rstDatabase.ReplaceDatabase = true;
            rstDatabase.SqlRestore(sqlServer);

            connection.Disconnect();
            return true;
        }
        catch (Exception ex)
        {
            MessageBox.Show("A problem occured when building the branch!" + ex, "Monytron Consolidator", MessageBoxButtons.OK, MessageBoxIcon.Error);
            return false; 
        }

I think when you do the restore, the database name should not exist or the location of the files being created should be distinct. As per the log message it seems there is already a database file with name GC_BranchName.mdf in the same location where you are trying to restore. Either ensure that there is no database with the same name present already or move the files in those locations to a separate area.. — vmachan, Jan 03 '16 at 15:35
Your error states that `GC_Muntinlupa` is using it's .mdf file... — PaulShovan, Jan 03 '16 at 15:40
either you delete the "GC_Muntinlupa" database before your restore operation or you should relocate/rename your restored database files (what ends up "WITH MOVE" SQL option) ... see http://stackoverflow.com/questions/14376615/smo-restore-database — Tom, Jan 03 '16 at 15:43
@Tom Can't I use a .bak file as a template when creating databases? — J.P Masangcay, Jan 03 '16 at 15:49
@gypsyCoder So after creating GC_Muntinlupa, and I would like to create another db and restore that bak file onto the new database, I would need to detach GC_Muntinlupa? — J.P Masangcay, Jan 03 '16 at 15:51
@gypsyCoder I just found out that when I restore and got to rstDatabase.SqlRestore(sqlServer); my GC_Muntinlupa.mdf would be renamed to GC_BranchName which was the database the .bak was created from — J.P Masangcay, Jan 03 '16 at 16:03
@J.P Masangcay sure! you can use a bak file as a template, but you have to make sure, that a) there are no database name conflicts and b) no file name conflicts. the second means that youe have to rename your database files during(!!!) restore operation. in your case for example ... GC_BranchName.mdf -> GC_BranchName2.mdf GC_BranchName_log.ldf -> GC_BranchName2_log.ldf — Tom, Jan 03 '16 at 16:05
no, a detach is not needed. see my link above ... you are able to rename the files with the help of the RelocateFile() class during restore operation! — Tom, Jan 03 '16 at 16:12
@Tom Wait, so what's happening is normal where GC_Muntinlupa's mdf keeps changing to GC_BranchName? and I need to rename it when I am about to create another database? — J.P Masangcay, Jan 03 '16 at 16:15
@J.P Masangcay Yes, if you do not care about it, the files are always restored with names from the original database (not renamed). that's normal - see the restore database dialog in SQL Server Management Studio - there it is the same. — Tom, Jan 03 '16 at 16:18
@Tom Thanks for the info but I am having trouble renaming my mdf since my database is still attached. I tried to detach but am having trouble attaching it. I added sqlServer.DetachDatabase(name, false); File.Move(Properties.Settings.Default.Fountain + "GC_BranchName.mdf", Properties.Settings.Default.Fountain + name + ".mdf"); File.Move(Properties.Settings.Default.Fountain + "GC_BranchName_log.log", Properties.Settings.Default.Fountain + name + "_log.ldf"); but am unable to attach it. — J.P Masangcay, Jan 03 '16 at 16:57

Tom · Accepted Answer · 2016-01-05T10:35:02.747

Sorry J.P Masangcay but i didn't had time to put it all together for you until now. Here is the solution that works for you, verified by me.

Your problem is that you have file conficts if you restore your database backup to a new database.

Why file conflicts?

The default behavior of SQL Server is to restore the database using file paths and names for data and log files as they have been at the time of the backup. Therefore, if that originating database exists at the time of the restore process this leads to filesystem conflicts. They same conflict arises if you restore your database backup template to another server multiple times.

Your Case:

Database: GC_BranchName

Data File: GC_BranchName.mdf

Log File: GC_BranchName_log.ldf

Resore Database: GC_Muntinlupa

Data File: GC_BranchName.mdf

Log File: GC_BranchName_log.ldf

To solve the proplem you have to relocate files to have distinct filenames for your databases. Here is a solution, which simply adds/prepends the "new database name" to all files. This relocating under the hood restores the database using the TSQL "RESTORE DATABASE ... WITH MOVE" option, as your exception above is suggesting.

Resore Database: GC_Muntinlupa

Data File: GC_Muntinlupa_GC_BranchName.mdf

Log File: GC_Muntinlupa_GC_BranchName_log.ldf

bool RestoreDB(string name)
{
    try
    {
        var connection = ServerConnection(Properties.Settings.Default.Well);
        var sqlServer = new Server(connection);
        var rstDatabase = new Restore();

        rstDatabase.Database = name;
        rstDatabase.Action = RestoreActionType.Database;
        rstDatabase.Devices.AddDevice(System.Environment.CurrentDirectory + "\\GC.bak", DeviceType.File);
        rstDatabase.ReplaceDatabase = true;

        foreach (DataRow r in rstDatabase.ReadFileList(sqlServer).Rows)
        {
            var relocateFile = new RelocateFile();

            relocateFile.LogicalFileName = r["LogicalName"].ToString();

            // move/rename physical filename by prepending database name to prevent FileSystem conflicts
            var physicalName = r["PhysicalName"].ToString();
            var path = System.IO.Path.GetDirectoryName(physicalName);
            var filename = System.IO.Path.GetFileName(physicalName);
            physicalName = System.IO.Path.Combine(path, string.Format("{0}_{1}", name, filename));

            relocateFile.PhysicalFileName = physicalName;

            rstDatabase.RelocateFiles.Add(relocateFile);
        }

        rstDatabase.SqlRestore(sqlServer);

        connection.Disconnect();
        return true;
    }
    catch (Exception ex)
    {
        MessageBox.Show("A problem occured when building the branch!" + ex, "Monytron Consolidator", MessageBoxButtons.OK, MessageBoxIcon.Error);
        return false;
    }
}

As it is, this answer isn't a good answer and won't be helpful to any readers in the future. How does this answer the question? *Why* does it answer the question? What does it do? The explanation should be in the answer itself, instead of referring to comments. Comments in SO can be deleted at any time, without any warning. — Panagiotis Kanavos, Jan 05 '16 at 10:00
In this case, you are trying to relocate the DB files using new names? Why not *overwrite* the existing database? To be fair, the OP should explain what he wants to do in the question itself, not the comments — Panagiotis Kanavos, Jan 05 '16 at 10:03
@Panagiotis Kanavos you are right, i'll modify my answer and add more info — Tom, Jan 05 '16 at 10:14

martin p david · Answer 2 · 2019-06-10T06:39:38.287

This should solve the issue

namespace DB_Restore
{
    class Program
    {
        static void Main(string[] args)
        {
            RestoreDatabase();
        }
        public static void RestoreDatabase()
        {
            try
            {
                ServerConnection connection = new ServerConnection(@"Server\instance", "uname", "PWD");
                Server sqlServer = new Server(connection);
                Restore rstDatabase = new Restore();
                rstDatabase.Action = RestoreActionType.Database;
                rstDatabase.Database = "H5MI_Automation_Restore_Backup";
                BackupDeviceItem bkpDevice = new BackupDeviceItem(@"E:\DATA\QA_SP\MSSQL11.QA_SP\MSSQL\Backup\H5MI_Automation.bak", DeviceType.File);
                rstDatabase.Devices.Add(bkpDevice);
                rstDatabase.ReplaceDatabase = true;
                //As mentioned in the above solution this code will take care .mdf and .ldf file location issue
                foreach (DataRow r in rstDatabase.ReadFileList(sqlServer).Rows)
                {
                    var relocateFile = new RelocateFile();
                    relocateFile.LogicalFileName = r["LogicalName"].ToString();
                    Console.WriteLine(relocateFile.LogicalFileName);
                    var physicalName = r["PhysicalName"].ToString();
                    Console.WriteLine(physicalName);
                    var path = System.IO.Path.GetDirectoryName(physicalName);
                    Console.WriteLine(path);
                    var filename = System.IO.Path.GetFileName(physicalName);
                    Console.WriteLine(filename);
                    physicalName = path + @"\H5MI_Automation_Restore_Backup_" + filename;
                    Console.WriteLine(physicalName);
                    relocateFile.PhysicalFileName = physicalName;
                    Console.WriteLine(relocateFile.PhysicalFileName);
                    Console.WriteLine(relocateFile);
                    rstDatabase.RelocateFiles.Add(relocateFile);
                }
                rstDatabase.SqlRestore(sqlServer);
                connection.Disconnect();
            }
            catch (Exception e)
            {

                Console.Write(e);
            }
        }
    }
}

Add the explanation of the code. Essentially the issue in the above-attached screenshot is because of the .mdf and .ldf file location. When you try to manually restore the DB u will also face the same issue. attaching the screenshot.Manual DB restore

Why its throwing error? Its because the file is used by the actual DB(from where .bak file is genrated)

So, this piece of code will help you to change the location of both .mdf and .ldf file path.

 foreach (DataRow r in rstDatabase.ReadFileList(sqlServer).Rows)
                {
                    var relocateFile = new RelocateFile();
                    relocateFile.LogicalFileName = r["LogicalName"].ToString();
                    Console.WriteLine(relocateFile.LogicalFileName);
                    var physicalName = r["PhysicalName"].ToString();
                    Console.WriteLine(physicalName);
                    var path = System.IO.Path.GetDirectoryName(physicalName);
                    Console.WriteLine(path);
                    var filename = System.IO.Path.GetFileName(physicalName);
                    Console.WriteLine(filename);
                    physicalName = path + @"\H5MI_Automation_Restore_Backup_" + filename;
                    Console.WriteLine(physicalName);
                    relocateFile.PhysicalFileName = physicalName;
                    Console.WriteLine(relocateFile.PhysicalFileName);
                    Console.WriteLine(relocateFile);
                    rstDatabase.RelocateFiles.Add(relocateFile);
                }

Could you please add some information about your code? Why and how does it resolve OP's problem? — deHaar, Jun 07 '19 at 09:38

Can't restore a .bak file to a database

2 Answers2