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

Leave a Reply

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