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.
Who is Participating?

Improve company productivity with a Business Account.Sign Up

crescendoConnect With a Mentor Commented:
Ah, so it's not exactly a copy that you want? When you say "does not already exist", do you mean that the primary key does not exist? If so, then you don't have a problem because the insert will fail anyway, but you need to trap and reject the error. That's no problem, just change the rsDest.Update line to:

    On Error Resume Next
    On Error Goto 0

This will effectively ignore errors on the update.

But, if you want to see if some other field already exists you will have to write code that is specific to each table, whereas the code I supplied in answer to your original question does a generic copy of every field in every table.

You will need to add code something like

    Dim rsCheck As Recordset
    Set rsCheck = New Recordset

    sSQL = "SELECT * FROM YourTableName WHERE YourField = " & rsSource("YourField")
    rsCheck.Open sSQL, rsDest

    If rsCheck.EOF Then
        ' The record doesn't exist, so put the copy code here
    End If

However, for this to work you would have to write a separate routine for each table. The simple solution is add a UNIQUE constraint on the destination table so that duplicates can't be copied, then reject errors as above.

Anthony PerkinsCommented:
>>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.
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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.
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 : 
   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 :

ASPwizAuthor Commented:

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.
ASPwizAuthor Commented:
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 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.
ASPwizAuthor Commented:
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.

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?
ASPwizAuthor Commented:
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
    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
            For Each fld In rsSource.Fields
                rsDest(fld.Name) = rsSource(fld.Name)
    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.
ASPwizAuthor Commented:

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.

ASPwizAuthor Commented:
hit the nail on the head...

on error just totally slipped my mind.   Doh!!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.