SMO Restore doesn't always work

Question

My question is basically stated in the title, but ill try to expand on it. I am trying to backup a database and then restore that said database to a another database located on the same server. For now I'm keeping it on the same server for testing purposes, but I may need to restore it to a new server.

The code works, no error or such. But if i run it once, there's no guarantee that the restored databases gets updated. If i run it lets say 5 times, it'll most likely update the database.

I'm just wondering why it doesn't update right away?

using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using System;

// This program is used to perform a full database backup. The code also includes a SMO      restore as well

namespace BackupDB
{
    class Program
    {
        static void Main(string[] args)
        {
            // Connecting to the server
            ServerConnection serverConn = new ServerConnection(@"T520-R9K0H1K\SQLEXPRESS");
            Server server = new Server(serverConn);

            backup(serverConn, server);
            restore(serverConn, server);

            Console.WriteLine("Closing Application");
            Environment.Exit(0);

        } // Main

        public static void backup(ServerConnection serverConn, Server server)
        {

            if (!serverConn.IsOpen)
            {
                serverConn.Connect();
            }

            Backup bkpDBFull = new Backup();
            bkpDBFull.Action = BackupActionType.Database;
            bkpDBFull.Database = "databaseTest";
            BackupDeviceItem bkpDevice = new BackupDeviceItem(@"C:\BakFiles\test.bak", DeviceType.File);
            bkpDBFull.Devices.Add(bkpDevice);
            bkpDBFull.BackupSetName = "databaseTest Backup";
            bkpDBFull.BackupSetDescription = " databaseTest - Full Backup";
            bkpDBFull.NoRecovery = false;

            bkpDBFull.Initialize = true;

            try
            {
                bkpDBFull.SqlBackupAsync(server);
                Console.WriteLine("Backup Complete");
            }

            catch (Exception ex)
            {
                Console.Write(ex.Message);
                Console.Write(ex.Source);
            }

            finally{ serverConn.Disconnect(); }
        } // Backup

        public static void restore(ServerConnection serverConn, Server server)
        {
            if (!serverConn.IsOpen)
            {
                serverConn.Connect();
            }

            Restore destination = new Restore();
            destination.Action = RestoreActionType.Database;
            destination.ReplaceDatabase = true;
            destination.Database = "RestoreDB";
            BackupDeviceItem source = new BackupDeviceItem(@"C:\BakFiles\test.bak", DeviceType.File);
            destination.Devices.Add(source);
            destination.NoRecovery = false;
            destination.RelocateFiles.Add(new RelocateFile("databaseTest", @"C:\Restored\test.mdf"));
            destination.RelocateFiles.Add(new RelocateFile("databaseTest_Log", @"C:\Restored\test_log.ldf"));

            try
            {
                destination.SqlRestoreAsync(server);
                Console.WriteLine("Restore Complete");
            }

            catch (Exception ex)
            {
                Console.Write(ex.Message);
                Console.Write(ex.Source);
            }

            finally{ serverConn.Disconnect(); } 
        } // Restore
    } // Class
} // Namespace

Answer 1

The problem is you are using SqlRestoreAsync(Server). SqlRestoreAsync(Server) is a non blocking function, you get notified that the restore is complete by listening to events on the Restore class. So you are not getting notified of any errors that happen (and calling serverConn.Disconnect() during the restore process likely does not help either).

Either replace SqlRestoreAsync(Server) with SqlRestore(Server) so it will stop on that line of code till the restore completes or rewrite your code so you do not close the connection and have event listeners on the Complete and Information to find out the status of any errors or successes.

---

P.S. You should also replace SqlBackupAsync with SqlBackup too for the same reasons, you have no way to tell if the backup failed or not and have no idea of when the backup has finished processing. I would bet money what is happening is you are starting the backup, then while the backup is running you try restoring and it fails. Then when you try a 2nd time both the backup and the restore fails (the file is still in use by the backup). Then finally you get lucky and you hit the restore after the backup from 6 or 7 runs ago finishes and then it finally starts to restore your database.