sp_attach_db with file relocation?

Question

Hi all,

I'm on serverA where I've DatabaseX. I detach databaseX and copy databasex.mdf and database_log.ldf into serverB but on ServerB I want to put theese two file in different location regarding the ones they have on serverA, the situation is

ServerA

c:\db\databasex.mdf

c:\db\databasex_log.ldf

serverB

d:\db_data\databasex.mdf

e:\db_log\databasex.ldf

How can I attach databaseX into serverB with moving its original file locations?

Answer 1

check this

EXEC sp_attach_db @dbname = N'databasex', 
   @filename1 = N'd:\db_data\databasex.mdf', 
   @filename2 = N'e:\db_log\databasex.ldf'

if you are using SQL Server 2008 then right click on SQL Server ->facets and change the data file path

---

Ramesh Babu Vavilla MCTS,MSBI

Answer 2

Use CREATE DATABASE FOR ATTACH syntax as describted in SQL Server BOL

CREATE DATABASE MyAdventureWorks 
    ON (FILENAME = 'D:\MySQLServer\AdventureWorks_Data.mdf'), 
    (FILENAME = 'E:\MySQLServer\AdventureWorks_Log.ldf') 
    FOR ATTACH; 

Answer 3

When you detach, copy and reattach database files, you put the files where you want them on the target and attach them from their current location.  The MOVE file syntax is for restoring database files, because it attempts to restore the files to the original location.

Answer 4

its simple you can use the Create Database with attach options-

for the simple steps follow the below steps-

Move a Database Using Detach and Attach (Transact-SQL)
-http://msdn.microsoft.com/en-us/library/ms187858.aspx
(here in the page just change which versions you need i,e other versions it gives you to select ->SQL server 2005,2008,2008R2)

Attach Database in SQL Server-
http://www.mytechmantra.com/LearnSQLServer/Attach-Database-in-SQL-Server.html

Ensure once attached then you change the DB_owner as old one.. the good practise will be do the Backup and restore activity..

while restoring you can use the Move option to move to different file locations-

for ex-

D. Restoring a database and move files

The following example restores a full database and transaction log and moves the restored database into the C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data directory.

RESTORE DATABASE AdventureWorks2012
   FROM AdventureWorksBackups
   WITH NORECOVERY, 
      MOVE 'AdventureWorks2012_Data' TO 
'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\NewAdvWorks.mdf', 
      MOVE 'AdventureWorks2012_Log' 
TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\NewAdvWorks.ldf';
RESTORE LOG AdventureWorks2012
   FROM AdventureWorksBackups
   WITH RECOVERY;

ref ->http://msdn.microsoft.com/en-us/library/ms186858.aspx

---

Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and un-mark them if they provide no help.