hi,
"bryja_klaudius z[at]poczta[dot]fm" <dl*@zmyly.pl > ha scritto nel messaggio
news:c6******** **@atlantis.new s.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:\...\physica l_position_Data file.Mdf'
, @filenameN = 'c:\...\physica l_position_Logf ile.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_Datafi le_name' TO 'c:\..\newDataF ilePosition.Mdf '
MOVE 'logical_Logfil e_name' TO 'c:\..\newLogFi lePosition.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
