CODEWITHSUNDEEP
c#asp.netc#-4.0sql-server-2012smo
Ismail
Ismail

Reputation: 993

Restore database using c# and smo

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

Upvotes: 0

Views: 2128

Answers (2)

Ismail
Ismail

Reputation: 993

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.

Upvotes: 1

Danson Mwangi
Danson Mwangi

Reputation: 61

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

Upvotes: 0

Related Questions