Solved

Copy SQL Server Database with PURE ASP!

Posted on 2004-08-22
20
1,879 Views
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.
0
Comment
Question by:ASPwiz
  • 6
  • 5
  • 4
  • +4
20 Comments
 
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.
0
 
LVL 15

Expert Comment

by:jdlambert1
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.
0
 
LVL 9

Expert Comment

by:crescendo
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.
0
 
LVL 4

Expert Comment

by:szacks
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.
0
 
LVL 4

Expert Comment

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




0
 

Author Comment

by:ASPwiz
ID: 11872300
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.
0
 

Author Comment

by:ASPwiz
ID: 11872320
Explain DTS.......

What do i need for doing it in DTS?
0
 
LVL 4

Expert Comment

by:szacks
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.
0
 
LVL 34

Expert Comment

by:arbert
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???
0
 
LVL 4

Expert Comment

by:szacks
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.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 34

Expert Comment

by:arbert
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...
0
 
LVL 4

Expert Comment

by:szacks
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.
0
 

Author Comment

by:ASPwiz
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.
0
 
LVL 9

Expert Comment

by:crescendo
ID: 11895806
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?
0
 

Author Comment

by:ASPwiz
ID: 11895974
I have a duplicate schema in place.
0
 
LVL 9

Expert Comment

by:crescendo
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
    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
0
 
LVL 9

Expert Comment

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

Author Comment

by:ASPwiz
ID: 11896266
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?
0
 
LVL 9

Accepted Solution

by:
crescendo earned 500 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
    rsDest.Update
    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.

0
 

Author Comment

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

on error just totally slipped my mind.   Doh!!
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS SQL Backup 24 70
The duplicate key value is (<NULL>) 14 44
SQL JOIN 6 31
Mssql SQL query 14 26
Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

708 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now