Detach and Attach for Backup and Deployment?

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?
Thank you, Markus
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/colu...okenlogins.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.shtmhttp://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
|||Hello Andrea,
thank you very much for your detailed answer!
I think Nr. 3 is more work if you want to provide data
with your database. I's easy to attach a filled database.
Markus