SQL Server Performance Forum – Threads Archive

Restore database with Move option

I have a database backup from live server and I want to restore to development server. In Live
DBName:ABC
Logical Name=ABC_Data1
DataFile=c:abcdataABC_abc.mdf
Logincal Name=ABC_Data2
Datafile=c:abcdataABC_abc.ndf
Logical Name=ABC_Log1
Logfile=d:abllogABC_abc.ldf In Devlopment
DBName:ABC
Logical Name=Data1
DataFile=e:abcdataabc.mdf
Logical Name=Log1
Logfile=f:abllogabc.ldf Here Logical name and path are different.
I tried Restoring with move and replace option, i am able to do it but I have to change the logical name so can I restore the database without changing the Logical Name.
Rajeev Kumar Srivastava
–ALWAYS BE POSITIVE!–

---

Try this RESTORE DATABASE testing
FROM DISK = ‘Crogram FilesMicrosoft SQL ServerMSSQLBACKUPABC.BAK’
WITH MOVE ‘ABC_abc’ TO ‘Crogram FilesMicrosoft SQL ServerMSSQLdataabc.MDF’,
MOVE ‘ABC_abc’ TO ‘Crogram FilesMicrosoft SQL ServerMSSQLdataabc.MDF’ Madhivanan Failing to plan is Planning to fail

---

The above one will work if we add Replace clause, Even it work if we change the path.
But in DEVLOPMENT I want to have following structure after restore.
DBName:ABC
Logical Name=Data1
DataFile=e:abcdataabc.mdf
Logical Name=Log1
Logfile=f:abllogabc.ldf
Rajeev Kumar Srivastava
–ALWAYS BE POSITIVE!–

---

— Yes when you add REPLACE; you can move physical files to different locations and change names too. RESTORE DATABASE ABC
FROM DISK = ‘Crogram FilesMicrosoft SQL ServerMSSQLBACKUPABC.BAK’
WITH RECOVERY, REPLACE,
MOVE ‘ABC_DATA’ TO ‘Crogram FilesMicrosoft SQL ServerMSSQLdataabc_data.MDF’,
MOVE ‘ABC_LOG’ TO ‘Crogram FilesMicrosoft SQL ServerMSSQLdataabc_log.LDF’
If you need to change logical name of file, you will have to run alter database command: – ALTER DATABASE ABC
MODIFY FILE (NAME=ABC_DATA, NEWNAME=Data1)
Go
ALTER DATABASE ABC
MODIFY FILE (NAME=ABC_LOG, NEWNAME=Log1) See SQL BOL under Alter database for more information Deepak Kumar –An eye for an eye and everyone shall be blind

---

]]>