Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Custom Log Shipping using VB.NET

By provodnik,

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: 1084 | Views in the last 30 days: 4
 
Related Articles
FORUM

Program to install on database servers

Program to install on database servers

FORUM

Fresh install no database server

Fresh install no database server

FORUM

connect Database server

connect Database server

ARTICLE

Beginning SQL Server 2000 Programming

A review of Beginning SQL Server 2000 Programming

FORUM

Server Connection Timeout

Server Connection

Tags
 
Contribute