How to Implement SQL Server Log Shipping Using Visual Basic

Copy the Files

The next step is to copy the files to the remote server(s). This step is relatively easy because the FileCopy function works with UNC names. The only complaint that I have of this function is that it does not make use of the CopyFileEx API function, and capture events as the file is copied. Unfortunately, implementing the API call would have required too much time for the low reward, so I did not make use of it here. An interesting change to this code would be to add FTP capabilities to the file copy routine.

It should be noted that copying the files is not strictly needed over a LAN as SQL Server can backup files over a network. However, copying the files can provide some benefits. The primary benefit is that you will have more than one copy of the files available in the case of a catastrophic event.

Restore the Database

Restoring the database works essentially the same as backing the database up. We simply create the connection, define the SQL statement to restore the database, and execute the statement. As an additional step, there is the option of performing a DBCC CHECKDB on the target database after a restore has taken place. Again, for versions prior to SQL Server 7.0, this is a necessity.

You will notice in the demo application that there are two different versions of the file path used in this application. The first is the UNC version, and it is used for copying the files across the network. The second is the local path, using drive lettering rather than a UNC. The reason that this code was added, was to increase performance a bit. If a UNC is used, SQL Server must run requests through the networking layer in order to access the file data. Generally, there will be some performance degradation as a result of the added indirection. However, you might find it easier to maintain the settings in the registry if you use UNC for any path information.

With respect to the SQL statement to restore the database, I left out a key piece if you are going to store multiple backups per file. If multiple backups exist per file, you will need to track the current backup number, then append it to the end of the statement using the WITH FILE= number syntax. To help you track the number of backups in a file, I created the function GetBackupCountInFile in the clsLoadData class. There is more information available about the WITH FILE= syntax in Books Online.

Some Other Details

Deleting Old Files

Once you have successfully begun using Log Shipping, it won't take long to build up a fairly large number of backup files on your servers. In some cases, you may not wish to keep files for any length of time. The function FileCleanup in the module modMain, checks the dates of the files, and deletes any files greater than n days old, as specified in the registry settings. If you do not wish to retain any files on that server, just specify 0.

Batch SQL Restore

While testing this application, I found myself spending a rather obnoxiously large amount of time restoring the backups by hand. So, rather than manually typing out the backup and restore statements, I started logging them as they were executed, even if the execution resulted in a failure. This helped tremendously, when the backup files were still present (learn from my mistake, and don't set the DelFile registry setting to 0 until you have had this running for a couple of weeks, and know it works). The routine to create the log is located in the clsLoadData class as Dump2SQL.

Exit Codes

The easiest way to execute this program is to run it from the SQL Agent as a scheduled task. To test for errors, SQL Server checks the exit code from the process. Typical VB applications do not issue error codes, regardless of their state when they end. To correct that, I used the ExitProcess API call, which satisfies that need wonderfully, until you are debugging within VB. If you hit the ExitProcess call while in the VB environment, VB will be shut down as well, along with any changes that you made while stepping through the code (another unhappy moment in the developing of this code). So, you may wish to place compiler conditions around any calls to that function.

Conclusion

There are many different approaches you can take to creating a solid Log Shipping solution. This sample application is not intended to fit every company's needs, so you should evaluate it thoroughly before staking your business on it. However, it is functional, and can at least provide you with a solid head start. As time allows, I will be making modifications to the code to provided greater fault tolerance and new reporting functionality.

Reprinted with the express written permission of the author. Copyright 2002.