Re: Detach and Attach for Backup and Deployment?

From: Andrea Montanari (andrea.sqlDMO_at_virgilio.it)
Date: 06/20/04

  • Next message: Jibey: "MSDE causing problems." 
    Date: Sun, 20 Jun 2004 16:48:58 +0200

    hi Markus,
    "Markus S" <anonymous@discussions.microsoft.com> ha scritto nel messaggio
    news:1e96401c456cb$6637cfa0$a601280a@phx.gbl...
    > Hello,
    >
    > is it ok, to use Detach and Attach for Backup and
    > Deployment? To deploy a database by copy the .mdf and .ldf
    > file and simply run the sp_attach_db stored procedure on
    > the target server. Or are there reasons why a Attach might
    > fail? When does it fail? What are the requirements that it
    > does run successfull?

    detach + attach is a viable solution...
    database deplyment is usually done in 3 ways...
    1) backup your distribution database and restore it on user's server..
    2) detach your distribution database and re-attach it on user's server..
    both these methods can be accomplished via oSql.exe, the command line user
    inteface MSDE is shipped with and are relative easy to implement..
    they share the same caveat:
    - orphaned users troubles can be raised if you do not properly clean the
    registered users for that database befor backing it up or detach it...
    orphaned users are users that are non more in sync with the target server
    becouse of the relationship between sysusers.sid and syslogins.sid produces
    a NULL value... this can be fixed via sp_change_users_login system stored
    procedure
    314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
    http://support.microsoft.com/?id=314546
    224071 INF: Moving SQL Server Databases to a New Location with Detach/Attach
    http://support.microsoft.com/?id=224071
    221465 INF: Using the WITH MOVE Option with the RESTORE Statement
    http://support.microsoft.com/?id=221465
    240872 HOW TO: Resolve Permission Issues When You Move a Database Between
    http://support.microsoft.com/?id=240872
    246133 INF: How To Transfer Logins and Passwords Between SQL Servers
    http://support.microsoft.com/?id=246133
    168001 PRB: User Logon and/or Permission Errors After Restoring Dump
    http://support.microsoft.com/?id=168001
    Fixing broken logins
    http://www.sqlservercentral.com/columnists/nboyle/fixingbrokenlogins.asp

    all these articles can help you understanding how perform this and
    troubleshouting logins problems...

    - another caveat is that the distribution database inherits all it's
    settings from your development model database, including collation/sort
    order, database settings, special user object present in model database such
    as users, dbobjects and so on...
    this can not be an issue anymore, becouse SQL Server 2000 allows multiple
    collation/sort order settings per instance, but regarding SQL Server
    7.0/MSDE1.0, this was a big one...

    3) scripting out all DDL statements to sql files in order to re-create the
    databases on target server..
    this require more work, becouse you have to distribute all sql files to
    regen the database using external tools such as oSql.exe or additional
    applications of yours via ADO/ODBC/Ado.Net/SQL-DMO...
    I do prefer this method even if it involves more work, becouse it's
    granularity and flexibility...
    a companion application of mine is deserved to database creation reading a
    proprietary file which lists all files and relative actions to be performed,
    such as executing actions stored in separeted files like T-SQL CREATE
    [object] statements, loading base data via T-SQL INSERT INTO statements,
    eventually performing BULK INSERT operations, T-SQL statements regarding
    privileges and/or executing direct T-SQL statements included in the
    definition file like UPDATE.... SET...
    this comes in handy when an update to the database schema have to be
    shipped... only the DDL sql files to modify the database are shipped and
    executed .... and the app is the same.. only different command line
    switches/paramenters are passed
    you pay the price of a little more complexity, but you have full control
    over the database creation on the target server...
    hth 

    -- 
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm        http://italy.mvps.org
DbaMgr2k ver 0.8.0  -  DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--------- remove DMO to reply
  • Next message: Jibey: "MSDE causing problems."

    Relevant Pages