Custom Log Shipping using VB.NET

Total article views: 298 | Views in the last 30 days: 19

 Rate this |   Join the discussion |  Briefcase

This is a first version of a custom solution for SQL Server log shipping using VB.NET 2005 and .NET Framework v.2.0. The script has to run on a server where you have SQL Server 2005 (or at least tools) installed because it uses new SMO. For some functions of the script you will have to install .NET Framework v.2.0. I tested this program against SQL Server 2000 on both ends running the program from the third SQL Server with SQL Server 2005 installed. The code was written in VS.NET 2005.
I used 2 ways to get the list of databases to be backed up. The first one queries a table with databases names, logical file names, etc. (DBname, LogicalName, FileName, srvSource, SrvTarget, RelocDirectory, NetworkDestination, NetworkSource) so that you I don't have to hard code that and anybody can add/subtract databases for replication. The commented out part of the script provides another way of getting databases names by querying system tables. But then you will have to edit the program for each server you want to connect to.
The program reads the table info, builds arrays, connects to the source server and source databases, backes them up, moves to the destination SQL Server, restores, deletes the backup files, e-mails the results. my next step will be to create a table with e-mail addresses and query it instead of hard coding recepients.
The program goes through database names twice because I need to have both DB name and logical file names (data and log).
When the databases are restored they are left in read-only mode with replacing the data/log files.
If you want to connect to SQL Server using SQL login there is a commented out part of the rpogram that shows you how to do that.
There is plenty of room for improvement I just did not want ot give you all a perfect, finished product. You'll get more from it if you work on it yourself and share with others the results.

Total article views: 298 | Views in the last 30 days: 19