Creation of SQL2000 db from VB 3

[StormbringerX]

This may be the wrong forum for this question, if so I apologize.

I have a VB6 application, connecting to a SQL2000 server database. My app needs to be able to CREATE a database. This databse needs to be a duplicate of an existing database. All tables, permissions, etc... need to be recreated in its entirety. The reason for this is the users need to be able to perform statistical analysis on data WITHOUT damaging the original database.

As the users aren't database professionals, I need a front end VB program that can initiate the creation of the database AND copy the contents of an existing DB into the newly created one.

Is the easiest way for me to accomplish this to create a stored procedure(s) that will perform these tasks with just a simple VB initiation?

Any ideas would be SO appreciated.

THanks,
Dave

 

[gmmastros]

My recommendation would be to use SQL2000's ability to backup and restore. First, backup the database, and then restore it to a new database (database with a different name).

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[zemp]

Look into using SQLDMO. I found a VB clone of the Enterprise Manager on

http://www.planetsourcecode.com

that gave examples of everything using SQLDMO. I am not sure if it is still there. Here is the link that I had.

http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=42651&lngWId=1

zemp

 

[StormbringerX]

Thanks so much George and Zemp. The Planet Source link was bad but I'll research it and find it. Stars to both for your answers.

 

[chiph]

You can also create databases using just SQL commands. Look into CREATE DATABASE, CREATE TABLE, etc.

But it's probably easier to create a template database that you just rename, and then attach using the SP_ATTACH_DB and SP_DETACH_DB stored procedures.

Chip H.


____________________________________________________________________
Click here to learn Ways to help with Tsunami Relief
If you want to get the best response to a question, please read FAQ222-2244 first

 

[zemp]

The link seems to be working now. I think that the planet source code site was down earlier today.

zemp