SMO: restoring to a different DB, why db is null?

Question

This is my problem, I have a restore function in C# guided by this answers:

SMO: restoring to a different DB

But when the program tries to execute this code db.SetOnline(); it throws an exception: Object reference not set to an instance of an object.. The problem is ... db object is null. But why is the db object NULL?

This is my function:

public void restaurarBackup(string baseDatosDestino, string rutaBackUp, Server srvr)
{
    try
    {
        if (System.IO.Directory.Exists(DBpath))
        {
            // Si el usuario ha elegido el archivo desde el que quiere que la base de datos para ser restaurado
            // Crear una nueva base de datos de la operación de restauración
            Restore rstDatabase = new Restore();

            // Set the backup device from which we want to restore, to a file
            BackupDeviceItem bkpDevice = new BackupDeviceItem(DBpath + rutaBackUp, DeviceType.File);

            // Add the backup device to the restore type
            rstDatabase.Devices.Add(bkpDevice);

            // Set the database that we want to perform the restore on
            rstDatabase.Database = baseDatosDestino;

            DataTable dtFileList = rstDatabase.ReadFileList(srvr);
            string mdf_logicalFileName = dtFileList.Rows[0][0].ToString();
            string mdf_PhysicalFileName = String.Format(@"{0}\{1}.mdf", srvr.Information.MasterDBPath, baseDatosDestino);
            string ldf_logicalFileName = dtFileList.Rows[1][0].ToString();
            string ldf_PhysicalFileName = String.Format(@"{0}\{1}_log.ldf", srvr.Information.MasterDBPath, baseDatosDestino);

            rstDatabase.RelocateFiles.Add(new RelocateFile(mdf_logicalFileName, mdf_PhysicalFileName));
            rstDatabase.RelocateFiles.Add(new RelocateFile(ldf_logicalFileName, ldf_PhysicalFileName));
            srvr.KillAllProcesses(rstDatabase.Database);
            rstDatabase.Wait();

            Database db = srvr.Databases[rstDatabase.Database];

            if (db != null)
            {
                db.DatabaseOptions.UserAccess = DatabaseUserAccess.Single;
                db.Alter(TerminationClause.RollbackTransactionsImmediately);
                srvr.DetachDatabase(rstDatabase.Database, false);
            }

            // Set the restore type to a database restore
            rstDatabase.Action = RestoreActionType.Database;

            // If the database already exists, replace it
            rstDatabase.ReplaceDatabase = true;
            rstDatabase.NoRecovery = false;

            // Perform the restore
            rstDatabase.SqlRestore(srvr);
            db = srvr.Databases[baseDatosDestino];
            db.SetOnline(); // In this line the db object is null, why?
            db.DatabaseOptions.UserAccess = DatabaseUserAccess.Multiple;
            srvr.Refresh();
        }
        else
        {
            _infoError = "Verifique la existencia de la ruta de donde se va a restaurar el Backup!";
        }
    }
    catch (Exception e)
    {
        ManejoExcepcion.RegistrarExcepcion(e, out _infoError);
    }
}

Garboil

Was the db object null before db = srvr.Databases[baseDatosDestino]'? If not then your srvr.Databases[baseDatosDestino] object must be null. – Interpreter

srvr.Databases[baseDatosDestino] has 2 calls in lines: 32 and 50, but only in line 50 the object "db" is NULL, why? ... OR why srvr.Databases[baseDatosDestino] is NULL? – Rocray

I think that srvr.Databases[baseDatosDestino] should not be null – Rocray

It could be that it does not find a database at the "baseDatosDestino" index. One way to know for sure is to debug and step through your code and see what the value of srvr.Databases[baseDatosDestino] is. – Interpreter

in debug mode, the string baseDatosDestino = "SII_MYT_Contingencia_5395", when db = srvr.Databases[baseDatosDestino] running on the line 32 "db" has a value other than null (the access to the database). But after rstDatabase.SqlRestore(srvr) does the job, the same code db = srvr.Databases[baseDatosDestino] in the line 50 returns a null value in the "db" object – Rocray

You might have to check what this SqlRestore function is doing to your srvr object then. Is this a function that you made or a built-in function? Could you paste the code for SqlRestore? – Interpreter

SqlRestore is a method from Microsoft.SqlServer.Smo.dll – Rocray

I think I've found what happens ... when srvr.DetachDatabase(rstDatabase.Database,false); is executed, the database is marked as droped or something, then db = srvr.Databases[baseDatosDestino] in line 50 returns NULL because it appears as nonexistent. I comment that line and all run ok – Rocray

Answer 1

I think that process of DB resore is async. So if you try to get DB right after restore, db can be in middle retoring state and not available through SMO. So you should try to wait/pull while db will be created/resotred in sql server and will available through SMO. For example you can add folowing code instead of db = srvr.Databases[baseDatosDestino]:

while( ( db = srvr.Databases[baseDatosDestino] ) == null ) 
{
    Thread.Sleep(10);
}

Immunize

Answer 2

I have seen this issue when restoring a database using the SQL Server database restore wizard. That occurs when the destination version of SQL Server is lower than the Source version of SQL Server. So maybe you will be trying to restore the database backup from SQL Server 2012 into an older version such as 2008R2. So please confirm that the Destination version is same as or higher that the source database.

Brookite