SQL Server restore database with move example

A common task for a DBA is to restore a database to a new location. The script for the same is given below

USE [master]
GO
RESTORE DATABASE [AdventureWorks] FROM  DISK = N'E:\Ahmad\AdventureWorks2014.bak' 
WITH  FILE = 1,  
MOVE N'AdventureWorks2014_Data' 
TO N'E:\Ahmad\DATA\AdventureWorks_Data.mdf',  
MOVE N'AdventureWorks2014_Log' TO N'E:\Ahmad\Log\AdventureWorks_Log.ldf',
NOUNLOAD,  STATS = 10

The above command restores the Adventureworks2014 database to AdventureWorks database. The primary data file is created at e:\ahmad\data and log file is created at e:\ahmad\log folder.

In order to get the original database file location run below command

Restore filelistonly from DISK = N'E:\Ahmad\AdventureWorks2014.bak'

sql server restore database with move

Happy learning!!!

 

Like us on FaceBook  |  Join the fastest growing SQL Server group on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Leave a Reply

Your email address will not be published. Required fields are marked *