MSDE & database moving.

**Andrea Montanari** · Jul 20 '05, 03:31 AM

Re: MSDE & database moving.

hi,
"bryja_klaudius z[at]poczta[dot]fm" <dla@zmyly.pl > ha scritto nel messaggio
news:c67ou4$i2p $1@atlantis.new s.tpi.pl...[color=blue]
> 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
>[/color]
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/default.asp?url=/library/en-us/tsqlref/ts_sp_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/default.asp?url=/library/en-us/tsqlref/ts_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

**bryja_klaudiusz[at]poczta[dot]fm** · Jul 20 '05, 03:32 AM

Re: MSDE & database moving.

Hi,

Thanks. It helped.
[color=blue]
>hi,
>"bryja_klaudiu sz[at]poczta[dot]fm" <dla@zmyly.pl > ha scritto nel messaggio
>news:c67ou4$i2 p$1@atlantis.ne ws.tpi.pl...
>
>[color=green]
>>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
>>
>>
>>[/color]
>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
>
>[/color]

MSDE & database moving.

MSDE & database moving.

Comment

Comment