Go Premium for a chance to win a PS4. Enter to Win


Copy SQL Server Database with PURE ASP!

Posted on 2004-08-22
Medium Priority
Last Modified: 2010-08-05
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.
Question by:ASPwiz
  • 6
  • 5
  • 4
  • +4
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11865176
>>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.
LVL 15

Expert Comment

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

Expert Comment

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

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


Expert Comment

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

Expert Comment

ID: 11871981
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


Author Comment

ID: 11872300

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.

Author Comment

ID: 11872320
Explain DTS.......

What do i need for doing it in DTS?

Expert Comment

ID: 11873851
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.
LVL 34

Expert Comment

ID: 11876215
"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???

Expert Comment

ID: 11889419
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.
LVL 34

Expert Comment

ID: 11893368
"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...

Expert Comment

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

Author Comment

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

Expert Comment

ID: 11895806

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?

Author Comment

ID: 11895974
I have a duplicate schema in place.

Expert Comment

ID: 11895998
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

Expert Comment

ID: 11896014
The two parameters are connection strings, by the way.

Author Comment

ID: 11896266

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.


Accepted Solution

crescendo earned 2000 total points
ID: 11897405
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.


Author Comment

ID: 11897562
hit the nail on the head...

on error just totally slipped my mind.   Doh!!

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question