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:

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);
    }
}

Was the db object null before `db = srvr.Databases[baseDatosDestino]'`? If not then your `srvr.Databases[baseDatosDestino]` object must be null. — John Odom, Aug 24 '16 at 16:38
`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? — Néstor Fabián Peña Estrella, Aug 24 '16 at 16:47
I think that `srvr.Databases[baseDatosDestino]` should not be null — Néstor Fabián Peña Estrella, Aug 24 '16 at 16:51
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. — John Odom, Aug 24 '16 at 16:54
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 — Néstor Fabián Peña Estrella, Aug 24 '16 at 18:59
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`? — John Odom, Aug 24 '16 at 20:00
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 — Néstor Fabián Peña Estrella, Aug 24 '16 at 20:42

score 1 · Answer 1 · answered Sep 13 '17 at 14:08

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);
}

score 0 · Answer 2 · answered Feb 01 '17 at 17:50

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.

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

2 Answers2