Re: Sql Server Login

From: Andrea Montanari (andrea.sqlDMO_at_virgilio.it)
Date: 01/04/05 

Date: Tue, 4 Jan 2005 18:10:58 +0100

hi again,
"Andrea Montanari" <andrea.sqlDMO@virgilio.it> ha scritto nel messaggio
news:33vvkpF43u1qrU1@individual.net
>>
>> Which is better for creating the database on one computer and then
>> deploying it at the customer site.
>>
>> Are there any documents which explain how to copy a database from
>> development site to production site?
>>

as regard this last part, I personally am aware of 3 ways to do it.. and ech
of them has it's drawbacks...
1) you can detach your dev database, copy it on the user's machine, and
reattach it .. using sp_detach_db to detach it and sp_attach_db for
reattach, see BOL for further info....
2) you can backup your dev database and restore in on the user's machine...
see backup database and restore database in BOL for further info....

these 2 methods are quite easy to perform but suffer from some drawbacks..
- they can raise orphan users issue (please see
http://www.sqlservercentral.com/columnists/nboyle/fixingbrokenlogins.asp for
further info and details on how to solve this)
- they do not make use of user's model database... every database inherits
settings, dbobjects, sometimes permissions, from the current model
database... if you deploy your database schema these way you simply export
YOUR settings and preferences..
- they inherits your settings regarding collation and sort order.... this is
no more a problem with SQL Server 200 becouse it supports different
collations, but was a major issue for SQL Server 7.0 deployment..

3) you can script out your DDL database schema with tools like Enterprise
Manager or other (free) scripting tools...
this method is the more elastic one, even if it require more deal...
I currently deploy my dbs this way...
I deploy sql DDL script to re-create the schema, sql permission scripts to
generate privileges and users, sql DML "Insert Into" script to pre-load some
application's system-tables and/or txt files to BCP in data...
I do use a companion application which parses a private defintion files that
includes settings as long as a list of files to be "executed" some way... I
do use ADO and/or SQL-DMO, but oSql.exe can be an alternative, even if it
does not set the OS error level status on Win9x boxes... I chose not to use
oSql for this reason...
even if more complicated, this method allows me to deploy database upgrades
with no pain, providing the sql DDL script to morphe databases to current
release.. several tools can help you for this
please have a look at http://tinyurl.com/64jjy for a little more detailed
explanation about the method...

recently Microsoft released an excellent article base on scripting you can
read at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/scriptingacustomdatabaseinstallation.asp
and this is by far the best article I ever read about.. 

-- 
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm        http://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

Relevant Pages

  • Re: Enterprise Manager Newbie Question
    ... a SQL backup is not a simple copy of the database files. ... Is it possible to write a script that one could run from a workstation and ...
    (microsoft.public.sqlserver.tools)
  • Re: SQL Security
    ... except I'm having problems making it work in a script. ... ;Set properties of DB objects and open connection to database ... > from Books Online (within the SQL Server program group): ... > communicate with SQL Server. ...
    (microsoft.public.sqlserver.server)
  • Re: Vista hosting XPe tools/db
    ... Are you running this script on the machine that actually has the SQL server ... machine hosting the database. ... i don't know if you can install a second instance of SQL ...
    (microsoft.public.windowsxp.embedded)
  • Re: FORCE DB Corruption
    ... This script from Sharon Dooley should do the trick - be careful with it ... I haven't tried it on SQL 2000, ... /* this script works on a database that is a copy of pubs. ... How can I force a database to become corrupt to a point> that DBCC CHECKDB will report errors? ...
    (microsoft.public.sqlserver.server)
  • [NEWS] IBM Informix Web DataBlade Local Root by Design
    ... The following security advisory is sent to the securiteam mailing list, and can be found at the SecuriTeam web site: http://www.securiteam.com ... that ease development of "intelligent", interactive, Web-enabled database ... person who has access to change the Perl script. ...
    (Securiteam)
Loading