article

Backup data from the Remote Server using the power of ASP and ADO

Email
Submitted on: 1/5/2015 11:52:00 PM
By: Anoj Kumar (from psc cd)  
Level: Advanced
User Rating: By 5 Users
Compatibility: ASP (Active Server Pages)
Views: 749
 
     Backup data from the Remote Server using the power of ASP and ADO

This article has accompanying files

 
				

Backup data from the Remote Server using the power of ASP and ADO

Requiress : IIS 5 , ADO 2.5 and above


So. Your application is up and running smoothly until one day when Murpy's Law catches up. A measure of robustness of a software is not only how well it runs but also how well it is going to recover from unforeseen circumstances like a server crash etc. In the end it is going to depend on one thing: backups.The normal way would be to maintain a copy(or copies) of databases, there are even software available that can do this job. Most probably you are going to store a copy of the database on the server by ftping it to your local machine(in case of MS-Access and like, not MS-SQL Server, Oracle mind you). The database size is also going to be around so much MBs. Dragging such amount of data over the network through unreliable dial-up connections like those available here in India (for example) can be a dreadful chore and also lead to hair loss :-).

Here is a much faster way of accomplishing it. To give you a comparison we measured the time to store an 4MB (approx.) MS-Access 2000 DB to our local machine from a web server. Time: 6 mins at 21000 bps. The same thing through FTP took around 30 mins. But here we are only storing the tables, not the other database objects. Still that is a lot of time difference.

The sample just gives you the basic working of the technique and there is lot of room for improvement from the side of the user. This article assumes
that the user is comfortable with ADO and ASP. Click here to download the sample application .

The Application is divided into two parts , one ASP page which will serve the requested data from the server and the other is the client side application in VB which sends request to the server Application ( using ADO ) for the data of a particular table and after receiving it saves the data to a file at the client end. This datafile can be later manipulated as per user's need.

Backup.asp can process two different inputs. (i) ' option=table ' in this case it will return the Table Schema as recordset. (ii) The second input is the table name eg. 'option=employees' in this case the recordset will be opened using the input table name passed and then returned.
At the end ' rs.save response ' saves the recordset to the response object. Pls note here that to save a recordset in response stream you need IIS 5.0 or above.

Clicking the command button starts the process of download at the client end. At first the table list is retrieved by sending request
rs.Open "http://yoursite.com/backup/backup.asp?option=tables"
and list box is filled with table names Me.List1.AddItem rs("table_name")

For i = 0 To Me.List1.ListCount - 1
Me.Label1 = "Processing Table : " & Me.List1.List(i)
rs.Open "http://yoursite.com/backup/backup.asp?option=" & Me.List1.List(i)
' pass the Table name to retrieve data
Me.Label1 = "Downloaded data of Table : " & Me.List1.List(i)
rs.Save app.path & "\" & Me.List1.List(i) ' save the table locally as file
rs.Close ' records can also be saved in a local database
Next

Finally a recordset is opened for each table in the Listbox by sending the request to the backup.asp with table name and then saved to a local disk file using rs.Save Me.List1.List(i) method.

Backup.asp


<%
dim con,rs,query query=request("option")

set con=Server.Createobject("ADODB.Connection") con.open "give your connectionstring here" set rs=Server.CreateObject("ADODB.Recordset") if query="tables" then ' if table list is requested return the table list set rs=con.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "Table")) else ' else return the requested records rs.open "select * from [" & query & "]",con end if rs.save response ' save the recordset in response object rs.close ' and it will be retrieved by ADO at the client side set rs=nothing con.close set con=nothing

%>

Below is the code for the sample VB application which will retrive the data from the above ASP
and save it locally as data files.

Backup.frm

' Declare the Recordset object in general section 
dim rs As ADODB.Recordset 

Private Sub Command1_Click()

set rs = New ADODB.Recordset
rs.Open "http://yoursite.com/backup/backup.asp?option=tables" ' change the above URL to your URL

Me.Label1 = "Table list opened"
While Not rs.EOF
Me.List1.AddItem rs("table_name") ' Add the table list to ListBox
rs.MoveNext
Wend

rs.Close

Me.Label1 = "Processing tables .."
Dim i As Integer
For i = 0 To Me.List1.ListCount - 1
Me.Label1 = "Processing Table : " & Me.List1.List(i)
rs.Open "http://yoursite.com/backup/backup.asp?option=" & Me.List1.List(i)
' pass the Table name to retrieve data
Me.Label1 = "Downloaded data of Table : " & Me.List1.List(i)
rs.Save app.path & "/" & Me.List1.List(i) ' save the table locally as file
rs.Close ' records can also be saved in a local database
Next
End Sub


winzip iconDownload article

Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. Afterdownloading it, you will need a program like Winzip to decompress it.Virus note:All files are scanned once-a-day by Planet Source Code for viruses, but new viruses come out every day, so no prevention program can catch 100% of them. For your own safety, please:
  1. Re-scan downloaded files using your personal virus checker before using it.
  2. NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.

If you don't have a virus scanner, you can get one at many places on the net including:McAfee.com


Report Bad Submission
Use this form to tell us if this entry should be deleted (i.e contains no code, is a virus, etc.).
This submission should be removed because:

Your Vote

What do you think of this article (in the Advanced category)?
(The article with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor (See voting log ...)
 

Other User Comments


 There are no comments on this submission.
 

Add Your Feedback
Your feedback will be posted below and an email sent to the author. Please remember that the author was kind enough to share this with you, so any criticisms must be stated politely, or they will be deleted. (For feedback not related to this particular article, please click here instead.)
 

To post feedback, first please login.