Restore database using c# and smo

Question

I am trying to restore a sql server .bak into an empty database using the following c# code:

                string dbBakFile = GetBackFileFromZip(restoreConfig.TmpUnZipFolder,restoreConfig.DatabaseFileToRestore);

            if (string.IsNullOrEmpty(dbBakFile))
            {
                response.Status = DatabaseResponseStatus.Error;
                response.Message = "No .bak file found in " + restoreConfig.DatabaseToRestore;
                return response;
            }

            var builder =
                new SqlConnectionStringBuilder(
                    ConfigurationManager.ConnectionStrings["myserver"].ConnectionString);

            var smoServer =
                new Server(new ServerConnection(builder.DataSource,builder.UserID,builder.Password));                

            var db = smoServer.Databases[restoreConfig.DatabaseToRestore];

            if (db != null)
            {
                smoServer.KillAllProcesses(restoreConfig.DatabaseToRestore);
                log.Debug("all processes on db killed");
            }


            string dbPath = Path.Combine(db.PrimaryFilePath, restoreConfig.DatabaseToRestore + ".mdf");

            log.Debug("db path is " +dbPath);

            string logPath = Path.Combine(db.PrimaryFilePath,restoreConfig.DatabaseToRestore + "_Log.ldf");

            log.Debug("log path is " + logPath);

            var restore = new Restore();

            var deviceItem =
                new BackupDeviceItem(dbBakFile, DeviceType.File);

            restore.DatabaseFiles.Add(dbPath);

            restore.DatabaseFiles.Add(logPath);              

            restore.Devices.Add(deviceItem);

            restore.Database = restoreConfig.DatabaseToRestore;

            restore.FileNumber = 1;

            restore.Action = RestoreActionType.Files;

            restore.ReplaceDatabase = true;

            restore.PercentCompleteNotification = 10;

            restore.PercentComplete +=restore_PercentComplete;

            restore.Complete += restore_Complete;

            restore.SqlRestore(smoServer);

            db = smoServer.Databases[restoreConfig.DatabaseToRestore];

            db.SetOnline();

            smoServer.Refresh();

            db.Refresh();

I get the following error:

Microsoft.SqlServer.Management.Smo.FailedOperationException: Restore failed for Server 'IM-M4500\SQLEXPRESS'. ---> Microsoft.SqlServer.Management.Smo.SmoException: System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'new-test-44444' database

Yes it does hold different backup and I want to overwrite and replace it also want to move mdf and log files to new files. Am I missing something here in the options of restore?

Many thanks

Ismail

score 1 · Answer 1 · answered Jan 28 '14 at 13:46

Ok fixed the issue I need to give it the current db logical file name what i was actually doing was giving it the new db logical file name so

            //get the logical file names
            DataTable dtFileList = restore.ReadFileList(smoServer);

            string dbLogicalName = dtFileList.Rows[0][0].ToString();

            string logLogicalName = dtFileList.Rows[1][0].ToString();

            restore.RelocateFiles.Add(GetRelocateFile(dbLogicalName, dbPath));

            restore.RelocateFiles.Add(GetRelocateFile(logLogicalName, logPath));

This works nicely.

score 0 · Answer 2 · answered Jan 25 '14 at 09:28

0

Don't create a new database and try to restore on it. Instead use the below query.

    RESTORE DATABASE dbname from disk='location' WITH  MOVE 'data' TO  'name.mdf' MOVE  '_Log'  TO 'name_log.ldf'

To Replace the existing database, put its name on the dbname and use WITH REPLACE on the query

answered Jan 25 '14 at 09:28

Danson Mwangi

61
5

Danson, thanks for the reply. Just to give you a bit more information i have a process where users fill in a form and that creates an iis website and new blank db for them. After this process if they have an existing db i want them to be able to restore that db. The creation process uses a naming convention for the site and db and I want to keep that and just over write the db over the newly created db. Is there no way of doing this using tsql? I tried your sql and i still get same error. Thanks Ismail – Ismail Jan 28 '14 at 10:40

Restore database using c# and smo

2 Answers2