469,934 Members | 1,928 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,934 developers. It's quick & easy.

MSDE & database moving.

Hi,

How to copy database diagram (all data) to other server using only MSDE
server and VS.Net Proffesional? I have no Enterprice manager. I have to
move database (create on my machine) to my customer server. Is some
possibilities?
--
*Pozdrawiam,*
Klaudiusz Bryja

Jul 20 '05 #1
2 8447
hi,
"bryja_klaudiusz[at]poczta[dot]fm" <dl*@zmyly.pl> ha scritto nel messaggio
news:c6**********@atlantis.news.tpi.pl...
Hi,

How to copy database diagram (all data) to other server using only MSDE
server and VS.Net Proffesional? I have no Enterprice manager. I have to
move database (create on my machine) to my customer server. Is some
possibilities?
--
*Pozdrawiam,*
Klaudiusz Bryja

you actually have 3 possibilities..
1)
you can detach your database(s), copy all the physical files you database is
made of to you user's server and re-attach the database using the system
stored procedure
EXEC sp_detach_db 'database_name' -- for detaching
and
EXEC sp_attach_db @dbname = 'dbname'
, @filename1 = 'c:\...\physical_position_Datafile.Mdf'
, @filenameN = 'c:\...\physical_position_Logfile.Ldf'

ah... you have to re-attach the database to your server to, after copying
the files to a distribution media...
up to 16 data and log files can be specified, but please have a look at
http://msdn.microsoft.com/library/de...ae-az_52oy.asp
for sp_attch_db synopsis and syntax...

2)
you can (full) backup you database(s) and restore it to your user's server
using
RESTORE DATABASE database_name
FROM DISK = 'c:\...\backup.bck'
WITH MOVE 'logical_Datafile_name' TO 'c:\..\newDataFilePosition.Mdf'
MOVE 'logical_Logfile_name' TO 'c:\..\newLogFilePosition.Ldf'

the WITH MOVE option grants you the possibility to specify alternate
physical positions on user's server, other than your original locations on
your dev server
please have a look at
http://msdn.microsoft.com/library/de...ra-rz_25rm.asp
for Transact SQL backup syntax.

both these 2 methods are easy to implement, they can be performed on user's
server using ADO/AdoNet commands as long as via oSql.exe or similar tools...
they suffer a common scenario... you will restoring your database, based on
your model database, with your server settings regarding collation/sort
order, database options (autogrowth, autoshrink, size, recovery model and so
on), and more, they miss the ability to inherits objects/users existing in
user's model database.. another issue is you can propagate "orphaned users"
troubles if you not correctly purge your distribution database before
distributing it (more about "orphaned users" at
http://www.sqlservercentral.com/colu...okenlogins.asp)

3)
another way is to script out your database structure using tools like
Enterprise Manager or ObjectScripter, provided for free by MVP OJ at
http://www.rac4sql.net/objectscriptr_main.asp...
once the structure is exported to file, you can easily script out table
contents creating INSERT INTO sql scripts to be run in order to reload
pre-poluated tables, using the well known procedure provided by MVP Narayana
Vyas Kondreddi at http://vyaskn.tripod.com/code.htm#inserts (there are
other variations of that available on the net)
personally I do prefer this method, which grant me greater and more granular
control, where I can provide T-SQL DDL scripts, INSERT INTO scripts as long
as BCP possibility for larger tables/views.. this comes in handy for
structure version sync to, becouse the tool you provide for database
distribution can be prepared for upgrading database structure too.. and all
user's server and database settings are respected...
this method grants greater flexibility at the cost of greater complexity...
hth
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--------- remove DMO to reply

Jul 20 '05 #2
Hi,

Thanks. It helped.
hi,
"bryja_klaudiusz[at]poczta[dot]fm" <dl*@zmyly.pl> ha scritto nel messaggio
news:c6**********@atlantis.news.tpi.pl...

Hi,

How to copy database diagram (all data) to other server using only MSDE
server and VS.Net Proffesional? I have no Enterprice manager. I have to
move database (create on my machine) to my customer server. Is some
possibilities?
--
*Pozdrawiam,*
Klaudiusz Bryja

you actually have 3 possibilities..
1)
you can detach your database(s), copy all the physical files you database is
made of to you user's server and re-attach the database using the system
stored procedure
EXEC sp_detach_db 'database_name' -- for detaching
and
EXEC sp_attach_db @dbname = 'dbname'
, @filename1 = 'c:\...\physical_position_Datafile.Mdf'
, @filenameN = 'c:\...\physical_position_Logfile.Ldf'

ah... you have to re-attach the database to your server to, after copying
the files to a distribution media...
up to 16 data and log files can be specified, but please have a look at
http://msdn.microsoft.com/library/de...ae-az_52oy.asp
for sp_attch_db synopsis and syntax...

2)
you can (full) backup you database(s) and restore it to your user's server
using
RESTORE DATABASE database_name
FROM DISK = 'c:\...\backup.bck'
WITH MOVE 'logical_Datafile_name' TO 'c:\..\newDataFilePosition.Mdf'
MOVE 'logical_Logfile_name' TO 'c:\..\newLogFilePosition.Ldf'

the WITH MOVE option grants you the possibility to specify alternate
physical positions on user's server, other than your original locations on
your dev server
please have a look at
http://msdn.microsoft.com/library/de...ra-rz_25rm.asp
for Transact SQL backup syntax.

both these 2 methods are easy to implement, they can be performed on user's
server using ADO/AdoNet commands as long as via oSql.exe or similar tools...
they suffer a common scenario... you will restoring your database, based on
your model database, with your server settings regarding collation/sort
order, database options (autogrowth, autoshrink, size, recovery model and so
on), and more, they miss the ability to inherits objects/users existing in
user's model database.. another issue is you can propagate "orphaned users"
troubles if you not correctly purge your distribution database before
distributing it (more about "orphaned users" at
http://www.sqlservercentral.com/colu...okenlogins.asp)

3)
another way is to script out your database structure using tools like
Enterprise Manager or ObjectScripter, provided for free by MVP OJ at
http://www.rac4sql.net/objectscriptr_main.asp...
once the structure is exported to file, you can easily script out table
contents creating INSERT INTO sql scripts to be run in order to reload
pre-poluated tables, using the well known procedure provided by MVP Narayana
Vyas Kondreddi at http://vyaskn.tripod.com/code.htm#inserts (there are
other variations of that available on the net)
personally I do prefer this method, which grant me greater and more granular
control, where I can provide T-SQL DDL scripts, INSERT INTO scripts as long
as BCP possibility for larger tables/views.. this comes in handy for
structure version sync to, becouse the tool you provide for database
distribution can be prepared for upgrading database structure too.. and all
user's server and database settings are respected...
this method grants greater flexibility at the cost of greater complexity...
hth


Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by bryja_klaudiusz[at]poczta[dot]fm | last post: by
3 posts views Thread by *no spam* | last post: by
2 posts views Thread by Rosy Moss | last post: by
2 posts views Thread by benamar | last post: by
2 posts views Thread by hfk0 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.