Posts
33
Comments
133
Trackbacks
0
July 2014 Entries
Create or Restore SQL Database with backup from different location
There comes a scenario when you want to restore database with another database backup but would like its data and transaction files to be located at different location.

Original Database - OldDB (database whose backup is with you)
New Database - NewDB (new database to be created by restoring backup)

Step 1- get the logical file name for OldDB backup using the following sql

RESTORE FILELISTONLY FROM DISK = 'E:\SQLBackups\OlDBBackup.bak'

This will give you logical file name of the Data and transaction log files of the backup.

Step 2- Use below sql to create new database using backup from different location

RESTORE DATABASE NewDB FROM DISK = 'E:\SQLBackups\OlDBBackup.bak'
WITH MOVE 'OldDB Backup Data file Logical name' TO 'E:\Dev2008\Data\NewDB_Data.mdf',
MOVE 'OldDB Backup Log file Logical name' TO 'E:\Dev2008\Logs\NewDB_Log.ldf'

Posted On Monday, July 7, 2014 4:35 PM | Comments (3)