Data migration in MSDE

Hi
I have a set of distributors who use MSDE as their
database and have VB as the front end. I am planning to
have a second version to my application.
During this upgrade new tables are being added, old
tables modified and some tables modified.
What would be the best approach for this migration.
Rgds
Kudla
hi Kudla,
"Kudla" <johnvellaip@.yahoo.com> ha scritto nel messaggio
news:135201c4a13d$18f54260$a401280a@.phx.gbl
> Hi
> I have a set of distributors who use MSDE as their
> database and have VB as the front end. I am planning to
> have a second version to my application.
> During this upgrade new tables are being added, old
> tables modified and some tables modified.
> What would be the best approach for this migration.
in order to install both a database from scratch, or updating existing ones,
I do personally use another companion (still VB6, at the moment) application
provided with our main apps..
this other application, via SQLDMO (but you can use whatever access method
of your choice like ADO, Ado.Net, ..) will create/update the database(s)
from a reserved defintion file described like:
[Database]
Name=xxx
DataName=logical_name|physical_name
DataNameN=logical_name|physical_name ; for additional .Ndf files
LogTo=logical_name|physical_name
LogToN=logical_name|physical_name ; for additional .Ldf files
other key to specify additional settings
each database is described in it's structure by another reserved file which
list all actions, .sql DDL files, BCP to be executed, like
<BOF>
SQL=\Tables\MasterTables.sql
'which is a T-SQL DDL file including CREATE TABLE statements
SQL=\Tables\otherTB.sql
SQL=\Views\Views.sql
SQL=\SP\StoredProcedure.sql
.....
SQL=\general\CreateLogins.sql
SQL=\general\Grant.sql
SQL=\general\Deny.sql
.....
SQL=\Populate\TableXXX.sql
'which is a T-SQL INSERT INTO file to load tables
....
BCPIN=\PopulateBCP\tableYYY.txt|table_name|
'which performs a BCP IN bulk load
....
EXEC=INSERT INTO .....
EXEC=UPDATE ...
'which are T-SQL statements to be directly performed
....
<EOF>
personally I based this app on SQLDMO... the app will parse the file, read
references to the described files (\Tables\MasterTables.sql) and execute
it's contained statements or perform the appropriate task based on the
action prefix..
this app takes command line parameters in order to upgrade existing schema
too, to subsequent schema changes stored in version folders like
\00000001\
\00000002\
\0000000n\
the migration path will then generate the new objects executing all listed
and required DDL scripts and perform migration via standard INSERT
statements, or first provide BCP out with successive BCP in, or generate
INSERT INTO DML scripts for specific tables to be later executed...
Red-Gate now provides a new product that should be able to perform the same
task... have a look at http://www.red-gate.com/sql/sql_packager.htm
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply