A Whimsical Little MS-SQL Backupper

[Lyle Fairfield]

There have been posts about the difficulties of getting ones hands on
some failsafe kind of backup for data that lives on an MS-SQL server
accessible only through the Internet and for which backups are unavailable.

This little JScript script grabs the data and writes it to text files.
It seems to be quite fast. Yes, I know about bulk copy, but I haven't
been able to get bulk copy to connect to a Server across the Internet.

// ****** script begins ******
var c=new ActiveXObject('ADODB.Connection');
var f=new ActiveXObject('Scripting.FileSystemObject');
var r=new ActiveXObject('ADODB.Recordset');
var s=new ActiveXObject('ADODB.RecordSet');
var ts=new String();
var e=new Object();
var g=new String('PROVIDER=SQLOLEDB.1;');
g+='DATA SOURCE=my server;';
g+='INITIAL CATALOG=my database;';
g+='USER ID=moi;';
g+='PASSWORD=RedheadsIsHot';
c.ConnectionString=g;
try{
c.Open();
}
catch(e){
WScript.Echo('Connection failed!');
}
s = c.OpenSchema(20, Array(null, null, null, "Table"))
while(!s.EOF){
ts=s.Fields('TABLE_NAME').Value;
if(ts.substr(0,2)!='dt'){
r=c.Execute('SELECT * FROM [' + ts + ']');

f.CreateTextFile('D:/SpecificFiles/'+ts+'.txt').Write(r.GetString(2,r.RecordCount,'\t ','\n'));
}
s.MoveNext
}
// ****** script ends ******

Well, yes, I know David hates Scripts. That's another reason ....

[Tom van Stiphout]

On Thu, 20 Jan 2005 12:42:59 -0500, Lyle Fairfield <ly******@yahoo.ca>
wrote:

Thanks for sharing. Nice and compact.
Of course a db is much more than its data.
We should not do business with ISPs who make this too difficult. A
good ISP would allow you to make your own backups and restores.
Backups could be made to a location accessible via FTP.

-Tom. (not a readhead)

There have been posts about the difficulties of getting ones hands on
some failsafe kind of backup for data that lives on an MS-SQL server
accessible only through the Internet and for which backups are unavailable.

This little JScript script grabs the data and writes it to text files.
It seems to be quite fast. Yes, I know about bulk copy, but I haven't
been able to get bulk copy to connect to a Server across the Internet.

// ****** script begins ******
var c=new ActiveXObject('ADODB.Connection');
var f=new ActiveXObject('Scripting.FileSystemObject');
var r=new ActiveXObject('ADODB.Recordset');
var s=new ActiveXObject('ADODB.RecordSet');
var ts=new String();
var e=new Object();
var g=new String('PROVIDER=SQLOLEDB.1;');
g+='DATA SOURCE=my server;';
g+='INITIAL CATALOG=my database;';
g+='USER ID=moi;';
g+='PASSWORD=RedheadsIsHot';
c.ConnectionString=g;
try{
c.Open();
}
catch(e){
WScript.Echo('Connection failed!');
}
s = c.OpenSchema(20, Array(null, null, null, "Table"))
while(!s.EOF){
ts=s.Fields('TABLE_NAME').Value;
if(ts.substr(0,2)!='dt'){
r=c.Execute('SELECT * FROM [' + ts + ']');

f.CreateTextFile('D:/SpecificFiles/'+ts+'.txt').Write(r.GetString(2,r.RecordCount,'\t ','\n'));
}
s.MoveNext
}
// ****** script ends ******

Well, yes, I know David hates Scripts. That's another reason ....