Server backup

Question

I've got this code to backup my dbs that seemed to work but the last time I did the backup was a few months ago and now I cannot seem to run it. I get an exception at the statemetn I marked:  NullReferenceException, although I am suer that the data and log folders are put in correctly.

Much of this code was given to me by Alok and I again entreat to him to make sense out of it. I am too preoccupied with other problems to do it myself.

        public void backupDBFull ( string dbName )
        {
            ProvideSecurity.provideSecurity ( );
            DoServer.getSrvAndConnOrSwitch ( dbName );
            BackupDevice dev = new BackupDevice ( );
            dev.Name = "SqlBackUp Default Instance_" + dbName;
            UserDefinedSqlServerData data = ( UserDefinedSqlServerData )Globals.srv.UserData;
            if ( Globals.srv.BackupDevices[ "SqlBackUp Default Instance_" + dbName ] == null )  // <== Exception here.
            {
                Globals.srv.BackupDirectory = ( ( UserDefinedSqlServerData )Globals.srv.UserData ).dataFolder;
                dev = new BackupDevice ( Globals.srv, "SqlBackUp Default Instance_" + dbName );
                dev.BackupDeviceType = BackupDeviceType.Disk;
                dev.PhysicalLocation = Globals.srv.BackupDirectory + dbName + ".bak";
                dev.Initialize ( );
                dev.Create ( );
            }
            foreach ( Database db in Globals.srv.Databases )
            {
                if ( db.Name.ToUpper ( ).IndexOf ( dbName.ToUpper ( ) ) != -1 )
                {
                    int recoverymod = 0;
                    recoverymod = ( int )db.DatabaseOptions.RecoveryModel;
                    Microsoft.SqlServer.Management.Smo.Backup bk = new Backup ( );
                    bk.Action = BackupActionType.Database;
                    bk.BackupSetDescription = "Full backup of " + dbName;
                    bk.BackupSetName = "sqlBack for " + dbName + "_1";
                    bk.Database = dbName;
                    BackupDeviceItem bdi = default ( BackupDeviceItem );
                    bdi = new BackupDeviceItem ( dev.Name, DeviceType.LogicalDevice );
                    //Add the device to the Backup object.
                    bk.Devices.Add ( bdi );
                    bk.Checksum = true;
                    string dated = DateTime.Now.Date.ToString ( "MM-dd-yy" );
                    bk.BackupSetName = "BackupFull_" + dated;
                    bk.ContinueAfterError = false;
                    bk.ExpirationDate = DateTime.Now.AddDays ( 1825 );
                    bk.MediaDescription = "Disk";
                    bk.FormatMedia = false;
                    bk.Incremental = false;
                    bk.LogTruncation = BackupTruncateLogType.Truncate;
                    bk.Information += new ServerMessageEventHandler ( back_Information );
                    bk.Complete += new ServerMessageEventHandler ( back_Complete );
                    bk.Initialize = true;
                    bk.PercentCompleteNotification = 10;
                    bk.PercentComplete += new PercentCompleteEventHandler ( bk_PercentComplete );

                    bk.SqlBackupAsync ( Globals.srv );
                    // Inform the user that the backup has been completed.
                    Console.WriteLine ( "Full Backup complete." );
                    // Remove the backup device from the Backup object.
                    bk.Devices.Remove ( bdi );
                }
            }
        }                                               // backupDBFull

        public class UserDefinedSqlServerData
        {
            public string dataFolder = @"G:\DataBackup\";
            public string logFolder = @"G:\LogBackup\";
        } 

Thanks.

---

AlexB

Answer 1

Hello Alex

BackupDevices Collection is retrieved using server object as follows:

srv.BackupDevices represents a collection
srv.BackupDevices["SqlBackUp Default Instance_" + dbName] represents a backupdevice
where srv represents the server object.

In your piece of code, you have used Globals.srv  representing the Server Object. I just wished to confirm if it represents the Server Object or not ?

-Sreekar

Answer 2

Thank you for the answer Sreekar and sorry for delay: been very, very busy with one of my applications. Yes, it is the Server itself. I should have made it clear. My server is Sql Server Ent Ed. This is how I retrieve it:

public static class DoServer
    {
        public static void getSrvAndConnOrSwitch ( string databaseName )
        {
            if ( Globals.srv != null )
            {
                if ( Globals.srv.ConnectionContext.SqlConnectionObject.State == ConnectionState.Open )
                {
                    if ( Globals.srv.ConnectionContext.SqlConnectionObject.Database == databaseName )
                    {
                        return; //  right connection is present
                    }
                    else
                    {
                        getServer ( databaseName );
                    }
                }
                else
                { // connection is established but closed.
                    getServer ( databaseName );
                }
            }
            else
            {
                getServer ( databaseName );
            }
        }                                               // getSrvAndConnOrSwitch

        private static void getServer ( string databaseName )
        {
            Globals.srv = null;
            Server srv = new Server ( "." );
            srv.ConnectionContext.DatabaseName = databaseName;
            Microsoft.SqlServer.Management.Common.ServerConnection srvConn = srv.ConnectionContext;
            srvConn.LoginSecure = true;
            srv.ConnectionContext.Connect ( );  // this will open the connection
            Globals.srv = srv;
        }                                               // getServer
    }                                               // class DoServer
although, this code is from a different app. There is a chance that in the code I posted I get it via an instance, not static class.

Globals is a static class that contains some most frequently used  properties I want to access globally.

Thanks again. Appreciate your help.

---

AlexB

Answer 3

Sreekar, I am sorry, you've neglected to answer my OP question. Could you please?

Thanks.

---

AlexB

Answer 4

Hi Alex,

Sorry for delayed response, I had not monitored forums for a while.
Few quick questions what is the server version you are connecting to and what is version of your tools/SMO.
Is it failing for all dbName or is it for a particular dbName.

Can you post the stack trace of exception and exception here.

Regards,
Alok

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 5

Thank you Alok. I will try it today. I am running a dozen projects at once and temporarily put this aside. Version is 100. Smo I presume is associated with Visual Studio 2008. Does it tell you all you need?

I will get the exception stack trace for you later today.

---

AlexB

Answer 6

OK, the call stack is coming:

> SqlBackupAndRestore.exe!SqlBackupAndRestore.SqlTypes.backupDBFull(string dbName = "DailyClose") Line 55 C#
  SqlBackupAndRestore.exe!SqlBackupAndRestore.Form1.serverActionTwo() Line 118 + 0x17 bytes C#
  SqlBackupAndRestore.exe!SqlBackupAndRestore.Form1.comboAction_SelectionChanged(object sender = {System.Windows.Forms.ComboBox, Items.Count: 11}, System.EventArgs e = {System.EventArgs}) Line 252 + 0xa bytes C#
  [External Code] 
  SqlBackupAndRestore.exe!SqlBackupAndRestore.Program.Main() Line 18 + 0x28 bytes C#
  [External Code] 

Not much as you can see?

Stack trace:

System.NullReferenceException was unhandled
  Message="Object reference not set to an instance of an object."
  Source="SqlBackupAndRestore"
  StackTrace:
       at SqlBackupAndRestore.SqlTypes.backupDBFull(String dbName) in C:\VCSharp_Projects\SqlBackupAndRestore\SqlBackupAndRestore\SqlTypes.cs:line 55
       at SqlBackupAndRestore.Form1.serverActionTwo() in C:\VCSharp_Projects\SqlBackupAndRestore\SqlBackupAndRestore\Form1.cs:line 118
       at SqlBackupAndRestore.Form1.comboAction_SelectionChanged(Object sender, EventArgs e) in C:\VCSharp_Projects\SqlBackupAndRestore\SqlBackupAndRestore\Form1.cs:line 252
       at System.Windows.Forms.ComboBox.OnSelectedIndexChanged(EventArgs e)
       at System.Windows.Forms.ComboBox.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.SendMessage(HandleRef hWnd, Int32 msg, IntPtr wParam, IntPtr lParam)
       at System.Windows.Forms.Control.SendMessage(Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.Control.ReflectMessageInternal(IntPtr hWnd, Message& m)
       at System.Windows.Forms.Control.WmCommand(Message& m)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.CallWindowProc(IntPtr wndProc, IntPtr hWnd, Int32 msg, IntPtr wParam, IntPtr lParam)
       at System.Windows.Forms.NativeWindow.DefWndProc(Message& m)
       at System.Windows.Forms.Control.WmCommand(Message& m)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ComboBox.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at SqlBackupAndRestore.Program.Main() in C:\VCSharp_Projects\SqlBackupAndRestore\SqlBackupAndRestore\Program.cs:line 18
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException:


SqlTypes is one of my classes and it may be confusing with some of the system information, I guess.

Thanks.

---

AlexB

Answer 7

Hi,

I am not seeing any SMO related error or information in the callstack.
So I am going to ask you  to confirm by debugging that at the following code line 

if ( Globals.srv.BackupDevices[ "SqlBackUp Default Instance_" + dbName ] == null )  // <== Exception here

Globals.srv is not null and has value.

Regards,
Alok

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 8

Thank you very much, Alok. it is an unexpected hint. I will check but I don't think so. The thing is I have perhaps two hundred places in at least twenty projects where I use the same code and every time I do connect. Sql Server is in a perfect shape. Besides it it were null it would have thrown an exception way before that. Could it become null for some strange reason at that line?

---

AlexB

Answer 9

Sql Server is not null but I found something that perhaps can explain the exception. The default backup directory is in fact:

  Globals.srv.BackupDirectory "C:\\Program Files\\Microsoft SQL Server\\MSSQL10.MSSQLSERVER\\MSSQL\\Backup"

and I want to backup in a different directory. I set it in the routine UserDefinedSqlServerData. I thougth it was enough. Could it be the reason? If so, I don't understand it. The directory is get/set, so I can assign it in code, correct?

If this is the resaon for the directory to be null and it is null then could you throw in a piece of code for me to change tha backup directory via Smo code?

Thanks.

---

AlexB

Answer 10

Server. BackupDirectory property is the directory path that is only used when the user does not specify a full path but just a file name for the Backup device.

BACKUP DATABASE [aa] TO  DISK = N'aa.bak'
GO

will create a file aa.bak in the Server. BackupDirectory folder.

Its not used when you specify the full path in the BACKUP. (This is what you are doing.)
You don't need to change the
Globals.srv.BackupDirectory = ( ( UserDefinedSqlServerData )Globals.srv.UserData ).dataFolder;

but if you do want to change it, remember to call svr.Alter() before using that property.

---

Kunal Chourasia

Answer 11

but if you do want to change it, remember to call svr.Alter() before using that property.

It may be a good point. I will give it a try. I've been very busy to try it again but my hunch is that the reason for the failure is the fact that I did not specify security for the backup folder.

---

AlexB