Link to home
Start Free TrialLog in
Avatar of ASPwiz
ASPwiz

asked on

Copy SQL Server Database with PURE ASP!

Here is the senario.

We need to copy an SQL Server Database from it's current location to another SQL server on a completly different network (accessible over internet http)

We do not have file level access to the SQL Server...

The server sits behind a firewall, and is not exposed to the internet..... hence enterprise manager connection is not possible.

The only way we can go about this is with pure ASP script.   The Webserver is exposed to the internet, and is the only machine that can see the sql server. .... The SQL server the DB will be copied to is freely accessible also from the webserver.

I'm looking for code here.... not links to the many places I've already googled for.
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

>>We do not have file level access to the SQL Server...<<
Then it seems like your only choice it to build a DTS package to do this.  If (and I somehow suspect the answer will be no) you can create DTS packages.
Here's an alternative, since the SQL Server is the back end to your web site.

1. Create last-modfied column in all your tables and a tracking table (if you don't already have them)
2. Create web pages that pull data that has been added or changed since the last date/time in the tracking table
3. Use a full programming language (e.g., VB, C#, etc.) to create an application to open those web pages, loop through the data, inserting and updating your local copy of the database
4. When the application has updated a table, it can post to a web form that in turn updates the tracking table.

This is not a highly secure method, but if you use SSL and a require a login by the application, it won't be completely open to the public.
Avatar of crescendo
crescendo

Presumably you have full control of the webserver? Then run BCP on the webserver to dump the data into text files and you can then download them to the other server over the Internet.
Personally I'm a fan of DTS, which can be controlled from ASP.

However, I would guess that you are looking for a dynamic solution that is run with ADO recordsets.
Keep in mind that this can take a lot of time to run.

--------------------pseudo code-------------

query system table for list of table names
for each table in list of tablenames do
       query for all fields in the table and datatypes.
       build identical table in destination DB
       query select * from table name into recordset
        for each row in recordset
               insert row into destination
         end for
end for

-----------------end pseudo code----------

That would be the raw way of doing it. If you need more details on any of those steps, let me know.
Hi,
By my opinion, you can not use DTS cause destination server has no public ip (as you mentioned) and this server is located in another network then source one.
So, I suggest you to try SQLDMO interface for this task (I assume that both databases are accessable from the same web server).

I think about two ways to do so using method above"
1.Take a look on those samples : http://www.sqldev.net/sqldmo/SamplesVB6.htm 
   Specially on "Transfer with Events" (code is for VB6 and you can easylly transform that to asp VBScript).
   If this streight-forward method does not work for you then:
2. a)Using SQLDMO get a script text of all objects needed from your source DB(if you need system objects that ,for some reason are not avalible in SQLDMO. then connect to master DB directly). You can scrit entire database in couple of line s too.
    b)Run this script (assuming you have user with create permitions) on destination server.
       To create a database you should connect to master db and after to create your tables - to a newly created one.
    c) As crescendo suggested use bcp to get data from all your tables in text files,transfer them to a web server and run    bcp utility on destination server from your asp page to load this data to destination db.


Here is a sql-dmo reference form Microsoft : http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_con01_2yi7.asp




Avatar of ASPwiz

ASKER

Ok....

This needs to be PURE ASP code....

The destination server DOES have an internet IP.... both database are visible from webserver.

I cannot use SQLDMO as it is not installed, nor will it be installed.

I need some script that will basically walk the database tree in source DB, and recreate it in other DB.
Avatar of ASPwiz

ASKER

Explain DTS.......

What do i need for doing it in DTS?
why does it need to be pure ASP code? Also it depends what you mean by pure. Can you use ADO or other database connection tools or does this project require no third party tools?

DTS is a package that (among other things) copies database objects from one server to another. It is generally installed with SQL Server and it can be called directly from ASP. you would want to look at www.sqldts.com for examples.

If you need raw ADO walking the SQL tree, I suggest you look at my pseudo code above for exactly how to do it.
"So, I suggest you to try SQLDMO interface for this task"

SQLDMO would rely on SQL Server listening to a public port anyway.

When you say PURE ASP, do you mean you need to to route through port 80 because they won't allow any other inbound traffic???
ASP doesn't interface with SQL Server on port 80. The web user interfaces with the ASP on port 80 and then internally ASP interfaces with SQL Server however it needs to.

What I don't understand is if he has access to a server that can connect to the SQL Server then he can install any tool on that server and connect to the SQL Server in the same way that ASP does. ASP does not natively connect to any database, you have to use a database wrapper such as ADO or ODBC etc.. In that case any tool that used that protocol, being ASP or not, would be able to access the database.
"ASP doesn't interface with SQL Server on port 80. The web user interfaces with the ASP on port 80 and then internally ASP interfaces with SQL Server however it needs to."

Who said that ASP talked to the web server on port 80?  Wasn't me..I said "do you mean you need to to route through port 80 because they won't allow any other inbound traffic???"  I'm well aware of how a web server connects to sql server...
I apologize for the insinuation that you did not know how a web server connects to a database.

What I assume he meant is that the database is completely closed off to all external traffic, and needs to be connected through an internal program.
Avatar of ASPwiz

ASKER

Here is what I can, and Cant do:-

I CAN connect to the SOURCE Database from the webserver (Obviously)
I CAN connect to the destination Database from the webserver too
(both the above using standard ADO with sqloledb provider)

I CANNOT use SQLDMO on web server.... not installed.
I CANNOT connect via Enterprise Manager to SOURCE Database, although I can on the destination Database.

I MAY be able to use DTS, if someone has the code to do this.
ASPwiz:

Do you have the schema for the database? In other words, do you have, or can you create, a matching database to copy the data into, complete with primary and foreign keys etc?
Avatar of ASPwiz

ASKER

I have a duplicate schema in place.
Here's some (untested) code to copy the data. It in VB6 so you'll need to make some minor adjustments. I needed the Intellisense stuff to speed up writing it!

Private Sub CopyDatabase(sConnSource As String, sConnDest As String)
    Dim rsList As Recordset
    Dim rsSource As Recordset
    Dim rsDest As Recordset
    Dim cnSource As Connection
    Dim cnDest As Connection
    Dim sSQL As String
    Dim fld As Field
   
    ' Get connections to the databases
    Set cnSource = New Connection
    cnSource.Open sConnSource
    Set cnDest = New Connection
    cnDest.Open sConnDest
   
    ' Get a list of tables to copy
    sSQL = "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'"
    Set rsList = New Recordset
    rsList.Open sSQL, cnSource
   
    'Loop through the tables
    While Not rsList.EOF
   
        ' Open the source table and matching destination table
        Set rsSource = New Connection
        sSQL = "SELECT * FROM " & rsList("TABLE_NAME")
        rsSource.Open sSQL, cnSource
       
        Set rsDest = New Connection
        rsDest.Open sSQL, cnDest, adOpenDynamic, adLockOptimistic
       
        While Not rsSource.EOF
            rsDest.AddNew
            For Each fld In rsSource.Fields
                rsDest(fld.Name) = rsSource(fld.Name)
            Next
            rsDest.Update
            rsSource.MoveNext
        Wend
        rsSource.Close
        rsDest.Close
       
        rsList.MoveNext
    Wend
           
    Set rsList = Nothing
    Set rsSource = Nothing
    Set rsDest = Nothing
    Set cnSource = Nothing
    Set cnDest = Nothing
           
End Sub
The two parameters are connection strings, by the way.
Avatar of ASPwiz

ASKER

Ok...

Could the above be modified slightly to make sure the record being added does not already exist?

I essentially took the database I first started the site with (which contains records) ..... the live site however has a hell of a lot more records... but still need to check for each table I aint gonna be adding duplicate data.

Ideas?
ASKER CERTIFIED SOLUTION
Avatar of crescendo
crescendo

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ASPwiz

ASKER

hit the nail on the head...

on error just totally slipped my mind.   Doh!!