By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,292 Members | 1,379 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,292 IT Pros & Developers. It's quick & easy.

Backing Up a Remote SQL DB as XML

P: n/a
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
--
Nov 13 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.