Prev: BAK file include stored procedure
Next: Row Level Versioning using Read Committed Isolation
From: SQL on 2 Jul 2010 19:28
Can someone please give me all the steps and SQL code for copying 2005
databases using detach/attach? I need this for both using a)
Management Studio b) TSQL approaches. Thank you.
From: Erland Sommarskog on 3 Jul 2010 04:16
SQL (texassqldba(a)gmail.com) writes:
> Can someone please give me all the steps and SQL code for copying 2005
> databases using detach/attach? I need this for both using a)
> Management Studio b) TSQL approaches. Thank you.

Personally I prefer using BACKUP/RESTORE, since in this case the
source database does not have to be taken offline. And there is only
one file to copy, not two.

1. On the source server, run "sp_helpdb yourdb". Make note of the names
in the leftmost columns in the second result set.
2. BACKUP DATABASE yourdb TO DISK = 'somepath.bak' WITH COPY_ONLY
3. Copy the backup file to the target server. (Not needed if you can
use a network path which is visible from both servers.)
4. On the target server, determine where you want the database to be
restored. Say that you want it in the same place as otherdb, then
run "sp_helpdb otherdb" and make note of the file paths in the third
column.
5. RESTORE DATABASE yourdb FROM DISK = 'somepath.bak'
WITH MOVE 'name1' TO PATH 'path1',
MOVE 'name2' TO PATH 'path2'.
REPLACE

name1 and name2 are the names you found at point 1. path1 and path2 are
derived from the paths you found in point 4, but you need to change the
file names of course.

There may be server-level objects you need to migrate; particularly
logins. Windows users are less of a problem. If DOMAIN\User exists
on both servers, there is no extra precautions you need to make.
But if you use SQL Server authentication, SQL logins will lose their
mapping on the target server. This can be addressed with the command

ALTER USER name SET LOGIN = name



--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: John Bell on 3 Jul 2010 05:57
On Fri, 2 Jul 2010 16:28:20 -0700 (PDT), SQL <texassqldba(a)gmail.com>
wrote:

>Can someone please give me all the steps and SQL code for copying 2005
>databases using detach/attach? I need this for both using a)
>Management Studio b) TSQL approaches. Thank you.

Your first port of call should be Books Online where these procedures
and their usage is documented. For instance you haven't said if your
database is replicated and therefore just posting a script to detach
and attach a database is not going to cover what you actually need.

I would follow Erlands approach as you don't have to worry about the
database having multiple data and log files, but if you want to persue
using sp_detach_db look at:
http://msdn.microsoft.com/en-us/library/ms190794.aspx

and

http://msdn.microsoft.com/en-us/library/ms188031.aspx

John
 | 
Pages: 1
Prev: BAK file include stored procedure
Next: Row Level Versioning using Read Committed Isolation