Backing Up a Remote SQL DB as XML

[Lyle Fairfield]

I have posted previously a procedure for backing up a remote MS-SQL db
as text. This is a revised version in Javascript/JScript. This backups
data and procedures as XML.
My purpose in creating this is to preserve my data in simple form. When
one rents a remote MS-SQL db, one may find that getting a backup is more
expensive than the renting.
One could add other parts, say indexes, of the db to this procedure
quite easily. I want only those things I may have a problem
refabricating from memory.
DTS? Sure! I find this script simpler; it runs in the background with no
window/indication and it can be scheduled as needed. And it's fast (I
think). If you decide you want to use it, paste it into a text file and
give it an extension of js or jse. I use jse as js opens my editor.
Change the connection string and the file paths.

XML can be opened as a ADO recordset. This may simplify data recovery.

To run the script one must have Microsoft Windows Script Technologies
and ADO installed.

var c=new ActiveXObject(' ADODB.Connectio n');
var f=new ActiveXObject(" Scripting.FileS ystemObject");
var s=new ActiveXObject(' ADODB.Recordset ');
var r=new ActiveXObject(' ADODB.Recordset ');
var ts=new String();
var g=new String('PROVIDE R=SQLOLEDB.1;') ;
g+='DATA SOURCE=YourServ er;';
g+='INITIAL CATALOG=YourDat abase;';
g+='USER ID=YourUserID;' ;
g+='PASSWORD=Yo urPassword';
c.ConnectionStr ing=g;
try{
c.Open();
}
catch(e){
WScript.Echo('C onnection failed!');
}
s = c.OpenSchema(20 , Array(null, null, null, "Table"))
while(!s.EOF){
ts=s.Collect('T ABLE_NAME');
if(ts.toUpperCa se()!='DTPROPER TIES'){
r=c.Execute('SE LECT * FROM [' + ts + ']');
try{
f.DeleteFile('E :/BooksBackups/'+ts+'.xml');
}
catch(e){
}
r.Save('E:/BooksBackups/'+ts+'.xml', 1)
}
s.MoveNext
}
f.DeleteFile('E :/BooksBackups/Procedures.xml' );
g='SELECT so.ID, so.name, sc.text';
g+='\nFROM SysObjects so';
g+='\nLEFT JOIN SysComments sc';
g+='\nON so.ID=sc.ID';
g+='\nWHERE so.xtype IN (\'P\',\'V\',\' TF\',\'FN\',\'I F\',\'U\')';
g+='\nAND LEFT(so.name,3) NOT IN (\'dt_\',\'sys\ ')';
r= c.Execute(g);
r.Save('E:/BooksBackups/Procedures.xml' , 1)

You can see sample XML files (raw) that this script creates at:

Data ... http://www.virtualtimetable.com/schools.xml

Procedures ... http://www.virtualtimetable.com/procedures.xml

--
--
Lyle
--