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

x
?
Solved

VB Code that Copy ALL DataBase Structure and Data From SQLServer 2000 DB  To another SQLServer2000 DB

Posted on 2007-12-05
3
Medium Priority
?
160 Views
Last Modified: 2013-12-25
Hi there,
As the title implies I want a VB 6.0 code that copy a SQL Server DB to another SQL Server DB with the whole data in the database including the structure of the tables
0
Comment
Question by:Co0L_DBA
  • 2
3 Comments
 
LVL 38

Expert Comment

by:PaulHews
ID: 20413204
You can use SQLDMO to transfer a database from one server to another.  Scroll down to the Sql Transfer samples

http://www.sqldev.net/sqldmo/SamplesVB6.htm
0
 
LVL 17

Accepted Solution

by:
Shanmuga Sundaram earned 2000 total points
ID: 20418492

Dim rs As New ADODB.Recordset
Dim cn As New ADODB.Connection
Dim sourcedatabase As String
Dim targetdatabase As String
   
Private Sub Command1_Click()
Set rs = New ADODB.Recordset
rs.Open "select *  FROM INFORMATION_SCHEMA.TABLES", cn, adOpenKeyset, adLockOptimistic
Do Until rs.EOF
sql = "select * into " & targetdatabase & ".dbo." & rs.Fields(2) & " from " & sourcedatabase & ".dbo." & rs.Fields(2)
cn.Execute sql
rs.MoveNext
Loop
End Sub

Private Sub Form_Load()
sourcedatabase = "KR_DB"
targetdatabase = "KR_DB3"
Set cn = CreateObject("ADODB.Connection")
Set m_objRecordset = CreateObject("ADODB.Recordset")
cn.Open "dsn=dss;uid=youruserid;pwd=yourpassword" 'this uses dsn to create connection.
cn.DefaultDatabase = sourcedatabase
End Sub
0
 
LVL 17

Expert Comment

by:Shanmuga Sundaram
ID: 20418498
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
Loops Section Overview

782 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