Clone SQL DB (using VB?)

Hi all,
I'm having a SQL Server database called X. For testing and training, I
would like to make a copy of the entire DB to Y. This copy should
include all data, properties, indexes, views, etc. Unfortunately, all
my attempts to automate this failed...
Server = MSDE2a
Original database is a database marked for replication so I can't make
an easy copy...
App = VB6
- SQL job: fails on certain tables due to large 'memo' fields (ntext >
10Mb).
- DAO: trying to copy the tables from dbX to dbY fails while
getting/setting properties. Even when getting the fields (for each
srcField in srcTable.Fields; set destField = srcField) gives errors...
- ADOX: Haven't been able to get all the properties and indexes across.
ADOX doesn't seem to have all methods available to accomplish that.
Current idea is to take a script from the DB, adjust the paths and
names and use that to generate the DB. Afterwards, loop through all
tables and copy the contents from dbX to dbY.
While I haven't looked into it, I might run into a problem because
probably not all tools are installed on the application PC and without
them so I can't run SQL scripts from the command line... Not sure if I
can distribute all tools (MSDE) to make that working.
Another thing might be that I need to find out what the relative path
is to the server in order to create the files at the correct locations.
They are different because the app is on PC1 and the SQLserver is on
PC2. So I need to update the script (that wouldn't be the big problem)
but I'm not sure on how this would be interpreted in the above
scenario...
Is this the correct way to copy a DB including indexes and properties
(like default values, etc.)? Or is there something easier?
Many thanks,
GB
Your eyes are weary from staring at the CRT. You feel sleepy. Notice
how restful it is to watch the cursor blink. Close your eyes. The
opinions stated above are yours. When I snap my fingers, you cannot
imagine why you ever felt otherwise. <snap>
Couldn't you simply backup the database on the source server and the copy
the backup file to a share on the destination server and do a restore?'
Jim
"Gijs Beukenoot" <Gijs.DOT.Beukenoot@.AT@.Wanadoo.DOT.nl> wrote in message
news:mn.ba4e7d52bd68e489.23024@.Wanadoo.DOT.nl...
> Hi all,
> I'm having a SQL Server database called X. For testing and training, I
> would like to make a copy of the entire DB to Y. This copy should include
> all data, properties, indexes, views, etc. Unfortunately, all my attempts
> to automate this failed...
> Server = MSDE2a
> Original database is a database marked for replication so I can't make an
> easy copy...
> App = VB6
> - SQL job: fails on certain tables due to large 'memo' fields (ntext >
> 10Mb).
> - DAO: trying to copy the tables from dbX to dbY fails while
> getting/setting properties. Even when getting the fields (for each
> srcField in srcTable.Fields; set destField = srcField) gives errors...
> - ADOX: Haven't been able to get all the properties and indexes across.
> ADOX doesn't seem to have all methods available to accomplish that.
> Current idea is to take a script from the DB, adjust the paths and names
> and use that to generate the DB. Afterwards, loop through all tables and
> copy the contents from dbX to dbY.
> While I haven't looked into it, I might run into a problem because
> probably not all tools are installed on the application PC and without
> them so I can't run SQL scripts from the command line... Not sure if I can
> distribute all tools (MSDE) to make that working.
> Another thing might be that I need to find out what the relative path is
> to the server in order to create the files at the correct locations. They
> are different because the app is on PC1 and the SQLserver is on PC2. So I
> need to update the script (that wouldn't be the big problem) but I'm not
> sure on how this would be interpreted in the above scenario...
> Is this the correct way to copy a DB including indexes and properties
> (like default values, etc.)? Or is there something easier?
> Many thanks,
> GB
> --
> Your eyes are weary from staring at the CRT. You feel sleepy. Notice how
> restful it is to watch the cursor blink. Close your eyes. The opinions
> stated above are yours. When I snap my fingers, you cannot imagine why you
> ever felt otherwise. <snap>
>
|||Jim Young drukte met precisie uit :
> Couldn't you simply backup the database on the source server and the copy the
> backup file to a share on the destination server and do a restore?'
> Jim
>
<<cut>>
Jim,
Both databases are on the same machine so that makes it a little easier
to do that. And, I've done that a few times (using Enterprise Manager
from another machine). The only problems I saw then is in the (odd)
naming of the databasefiles. I can't remember exactly, but I remember
the filenames where correct (dbY) but in some of the properties, the
internal name was still <path>\dbX. I'll admit, so far, this seems to
be the easiest way to accomplish all this. If I code it, the users
shouldn't be able to mess things up (like using EM to do this; it
really makes you read everything twice before you restore it the wrong
way...)
I'll guess I'll hop along that path then, thanks
GB
Your eyes are weary from staring at the CRT. You feel sleepy. Notice
how restful it is to watch the cursor blink. Close your eyes. The
opinions stated above are yours. When I snap my fingers, you cannot
imagine why you ever felt otherwise. <snap>