467,920 Members | 1,101 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,920 developers. It's quick & easy.

Backing Up a Remote SQL DB as XML

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;';
WScript.Echo('Connection failed!');
s = c.OpenSchema(20, Array(null, null, null, "Table"))
r=c.Execute('SELECT * FROM [' + ts + ']');
r.Save('E:/BooksBackups/'+ts+'.xml', 1)
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

Nov 13 '05 #1
  • viewed: 983

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Ike | last post: by
5 posts views Thread by mark | last post: by
1 post views Thread by Hlpl | last post: by
9 posts views Thread by J. Frank Parnell | last post: by
9 posts views Thread by Jerry Porter | last post: by
10 posts views Thread by Lyle Fairfield | last post: by
2 posts views Thread by Victor Alvarez | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.