473,322 Members | 1,241 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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;';
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
0 1228

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Ike | last post by:
I am running off of a MySQL database on a remote server. I am truly concerned about backup. I have phpMyAdmin running on the remote server, and I see I can "Export" to various formatsd from there,...
5
by: mark | last post by:
Hi I'm hoping that some could point me in the right direction for "best practice" in: 1) securely backing up a complete sql7 and 2000 server containing many databases. 2) backing up an...
1
by: Hlpl | last post by:
Hi I have a VB.net windows application that uses an MSDE database I need to give the user the option to backup this data (and later restore it). Ideally I want to back the data up to an empty...
9
by: J. Frank Parnell | last post by:
hello, i dont know asp at all, but i have been asked to backup a database that is used on a site which uses .aspx. i dont need to do anything with it, just copy it and send it along to someone...
9
by: Jerry Porter | last post by:
Is there a way to back up the design changes in a SQL Server database without backing up all the data? It's just test data at this point.
3
by: war_wheelan | last post by:
I am having a problem backing up my database and TLog files due to a lack of local diskspace. The db file is about 30GB and the TLog is about 20GB each on a different hard disk. Each disk doesn't...
10
by: Lyle Fairfield | last post by:
I have a tiny db on a remote sql server. The owner of the sever backs up the db daily, but these backups are not freely available to me. I want my data (only my data) backed up in some form that is...
1
by: Pavs | last post by:
This may not be the place to ask however i am looking for resources on how i may document data I am backing up from an oracle database to MS Access. Basically I have my oracle database and i...
2
by: Victor Alvarez | last post by:
Hi, I'm not sure if anyone can help with this issue I have, but I would appreciate any thoughts. We are running DB2 8.2, fp11 on windows 2003. The goal is to backup to an external EMC SAN...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.