Data migration MSDE -> SQL Compact

Hi all

Currently we are using the MSDE as SQL Server and decide to switch to use the smaller and file based SQL Server 2005 Compact Edition.

Because of update issue we need to merge data from an already installed database into the new compact edition. Are there some tools we could use in our installation routine (InstallShield) or are there some recommendations how we could do this data merging?

Thanks @. all
Daniel

Hello Daniel,

I am going to begin investigating this issue myself very soon and I'll be more than happy to provide you with any info. I'm amazed that nobody has gotten to you, especially since you made this post well over a month ago. Have you received any information from others that might be relevant here? If so, I'd really like to hear your input.

Thanks,

Corey

|||

Please see the reply from Pragya on this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1300136&SiteID=1

"To make this migration task really easy, we are coming out of a data migration wizard soon!"

Data migration MSDE -> SQL Compact

Hi all

Currently we are using the MSDE as SQL Server and decide to switch to use the smaller and file based SQL Server 2005 Compact Edition.

Because of update issue we need to merge data from an already installed database into the new compact edition. Are there some tools we could use in our installation routine (InstallShield) or are there some recommendations how we could do this data merging?

Thanks @. all
Daniel

Hello Daniel,

I am going to begin investigating this issue myself very soon and I'll be more than happy to provide you with any info. I'm amazed that nobody has gotten to you, especially since you made this post well over a month ago. Have you received any information from others that might be relevant here? If so, I'd really like to hear your input.

Thanks,

Corey

|||

Please see the reply from Pragya on this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1300136&SiteID=1

"To make this migration task really easy, we are coming out of a data migration wizard soon!"

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

Data Link properties don't show any istance

Hi
Data Link Properties, open by test.udl file, when open don't show any
istance of MSDE sp3 installed.
By Visual Studio.NET 2003 Server Manager I can see the server and database.
If I insert manually name istance and try test connect it return me this
message:
Test connection failed because of an error initializing provider.
[DBNETLIB][ConnectionOpen(Connect()).]SQL Server does not exist or access
denied.
My system is:
Windows XP pro
Visual Studio.NET 2003
MSDE sp3
MDAC 2.8
Can you help me?
I answered your later post, regarding the same issue.
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Giuseppe Pezzella" <gpezzella@.yahoo.com> wrote in message
news:su31d.307127$5D1.13632538@.news4.tin.it...
> Hi
> Data Link Properties, open by test.udl file, when open don't show any
> istance of MSDE sp3 installed.
> By Visual Studio.NET 2003 Server Manager I can see the server and
database.
> If I insert manually name istance and try test connect it return me this
> message:
> Test connection failed because of an error initializing provider.
> [DBNETLIB][ConnectionOpen(Connect()).]SQL Server does not exist or access
> denied.
>
> My system is:
> Windows XP pro
> Visual Studio.NET 2003
> MSDE sp3
> MDAC 2.8
> Can you help me?
>

data from MSDE

How I can copy a database from MSDE to another computer with MSDE?
Thanks
Damiano
Damiano,
A good place to start is to have a look at the stored procedures called
sp_detach and sp_attach_db. After you detach the database, just
move the MDF and LDF files to the other computer and attach them
to the other server.
Dan
"YAMNE" <damiano_callegari@.libero.it> wrote in message
news:u7g#XHuWEHA.2176@.TK2MSFTNGP11.phx.gbl...

> How I can copy a database from MSDE to another computer with MSDE?
> Thanks
> Damiano
>
|||If you've committed all pending transactions before detaching then there's
no need to move the transaction log also. sp_attachdb will create a new
transaction log for you.
Jim
"Dan Deward" <ddeward@.msn.com> wrote in message
news:40dc85a9@.news.mcleodusa.net...
> Damiano,
> A good place to start is to have a look at the stored procedures called
> sp_detach and sp_attach_db. After you detach the database, just
> move the MDF and LDF files to the other computer and attach them
> to the other server.
> Dan
> "YAMNE" <damiano_callegari@.libero.it> wrote in message
> news:u7g#XHuWEHA.2176@.TK2MSFTNGP11.phx.gbl...
>
>
|||Hi,
Detach and Attach require some down time in actual server.
If you do a Backup in source server , copy the backup file to destination
and do a Restore - This require Zero down time.
Steps:-
OSQL -Usa -Ppassword -Sserver (enter)
1> Backup database <dbname> to disk='c:\dbname.bak' with init
2>go
copy the dbname.bak to destination server
Now restore the file in Destination
OSQL -Usa -Ppassword -Sserver (enter)
1> restore database <dbname> from disk='c:\dbname.bak'
2>go
Now it destination use the procedure sp_changeuserslogin (Refer books
online)
procedure to sync the logins after restore.
Thanks
Hari
MCDBA
"Jim Young" <thorium48@.hotmail.com> wrote in message
news:e9hlLivWEHA.1356@.TK2MSFTNGP09.phx.gbl...
> If you've committed all pending transactions before detaching then there's
> no need to move the transaction log also. sp_attachdb will create a new
> transaction log for you.
> Jim
> "Dan Deward" <ddeward@.msn.com> wrote in message
> news:40dc85a9@.news.mcleodusa.net...
>
>
|||Huh? The server does not have to be down to do an sp_detach--it can't be
down. Yes, you do "shutdown" the target database, but only that database is
affected. The sp_detach/file copy process can take a few seconds to a few
minutes.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uKCB%23yyWEHA.1908@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Hi,
> Detach and Attach require some down time in actual server.
> If you do a Backup in source server , copy the backup file to destination
> and do a Restore - This require Zero down time.
> Steps:-
> OSQL -Usa -Ppassword -Sserver (enter)
> 1> Backup database <dbname> to disk='c:\dbname.bak' with init
> 2>go
> copy the dbname.bak to destination server
> Now restore the file in Destination
> OSQL -Usa -Ppassword -Sserver (enter)
> 1> restore database <dbname> from disk='c:\dbname.bak'
> 2>go
> Now it destination use the procedure sp_changeuserslogin (Refer books
> online)
> procedure to sync the logins after restore.
> Thanks
> Hari
> MCDBA
>
>
> "Jim Young" <thorium48@.hotmail.com> wrote in message
> news:e9hlLivWEHA.1356@.TK2MSFTNGP09.phx.gbl...
there's[vbcol=seagreen]
called
>