Restoring Database Files to a Different Location

On 17 Sep, 2015

MS SQL DBA

By : Charith Silva

No Comments

Assume you have database called Sales_DB in your SQL Server. You planning to migrate this databases to another SQL Server and you want to place the database files (.mdf and .ldf) in different locations to those recorded in the backup from the original server.

Below script will help you to restore Sales_DB database to different location. Note that the MOVE option requires the specification of the logical file name, rather than the original physical file path.

RESTORE DATABASE Sales_DB 

FROM DISK = 'F:\Backups\ Sales_DB.bak'

WITH MOVE ' Sales_DB _Data' TO 'S:\ Sales_DB.mdf',

MOVE ' Sales_DB _Log' TO 'L:\ Sales_DB.ldf';

RESTORE DATABASE Sales_DB

FROM DISK = 'F:\Backups\ Sales_DB.bak'

WITH MOVE ' Sales_DB _Data' TO 'S:\ Sales_DB.mdf',

MOVE ' Sales_DB _Log' TO 'L:\ Sales_DB.ldf';

Previous Post Next Post

About The Author

Charith Silva

Charith Silva is a Microsoft certified SQL Server developer and database administrator who was graduated at Buckinghamshire New university in the UK. His career was started in 1998, primarily into Web application development, and later diversified into database development. He has got a vast experience in SQL Server database development, Database administration and Business Intelligence development. He believes that sharing the knowledge is key to the success.

Number of Posts : 87

All Posts by : Charith Silva