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.Connection');
var f=new ActiveXObject("Scripting.FileSystemObject");
var s=new ActiveXObject('ADODB.Recordset');
var r=new ActiveXObject('ADODB.Recordset');
var ts=new String();
var g=new String('PROVIDER=SQLOLEDB.1;');
g+='DATA SOURCE=YourServer;';
g+='INITIAL CATALOG=YourDatabase;';
g+='USER ID=YourUserID;';
g+='PASSWORD=YourPassword';
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.Collect('TABLE_NAME');
if(ts.toUpperCase()!='DTPROPERTIES'){
r=c.Execute('SELECT * 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\',\'IF\',\'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
--