Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

RESTORE DATABASE with MOVE TO option

Posted on 2009-06-30
6
Medium Priority
?
821 Views
Last Modified: 2012-05-07
I have a problem with restoring databases from backups.  I'll try to explain.

The situation is that I will get a BAK file (full backup) performed programatically with the following T-SQL statement
BACKUP DATABASE [dbNameGoesHere] TO DISK = 'somepath\BackupFile.bak'
The file name (BackupFile.bak) is invariant however the name of the database is not necessarily known to me nor is the original file path of the backup or database.

I want to be able to restore to a specific location on my system. eg.
RESTORE DATABASE [foo] FROM DISK = 'x:\BackupFile.BAK' MOVE xxx_Data TO 'D:\foo\mydata.mdf', MOVE xxx_Log To 'D:\foo\mydata_log.ldf'

The problem is that I don't know what the logical names for the data and the log are inside the .bak file.
How do I find that information OR is there a way around without requiring the logical names?

In a nutshell I would like the T-SQL statement to achieve what I want.
0
Comment
Question by:AndyAinscow
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24748354
run this to get the logical file info

restore filelistonly FROM DISK = 'x:\BackupFile.BAK'
0
 
LVL 44

Author Comment

by:AndyAinscow
ID: 24748439
OK, but then what ?  
Do you mean this will do the job?

restore filelistonly FROM DISK = 'x:\BackupFile.BAK' ;  RESTORE DATABASE [foo] FROM DISK = 'x:\BackupFile.BAK' MOVE xxx_Data TO 'D:\foo\mydata.mdf', MOVE xxx_Log To 'D:\foo\mydata_log.ldf'
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24748525
once you run the filelist only, you will get the contents of the backup file including the logical file name
 then you can use that logicall name to modify your 'WITH MOVE ' statement
0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 24748537
How to restore a database with a new name (Transact-SQL)
To restore a database with a new name

Optionally, execute the RESTORE FILELISTONLY statement to determine the number and names of the files in the database backup.


Execute the RESTORE DATABASE statement to restore the database backup, specifying:
The new name for the database.


The backup device from where the database backup will be restored.


The NORECOVERY clause if you have transaction log backups to apply after the file backups are restored. Otherwise, specify the RECOVERY clause.
The transaction log backups, if applied, must cover the time when the files were backed up.

The MOVE clause for each file to restore to a new location if the file names already exist. For example, creating a copy of an existing database on the same server for testing purposes may be necessary. In this case, the database files for the original database already exist, and so different file names need to be specified when the database copy is created during the restore operation.
Examples
This example creates a new database called MyNwind2_Test. MyNwind2_Test is a copy of the existing MyNwind2 database that comprises two files: MyNwind2_data and MyNwind2_log. Because the MyNwind2 database already exists, the files in the backup need to be moved during the restore operation. The RESTORE FILELISTONLY statement is used to determine the number and names of the files in the database being restored.

USE master
GO
-- First determine the number and names of the files in the backup.
-- MyNwind_2 is the name of the backup device.
RESTORE FILELISTONLY
   FROM MyNwind_2
-- Restore the files for MyNwind2_Test.
RESTORE DATABASE MyNwind2_Test
   FROM MyNwind_2
   WITH RECOVERY,
   MOVE 'MyNwind2_data' TO 'D:\MyData\MyNwind2_Test_data.mdf',
   MOVE 'MyNwind2_log' TO 'D:\MyData\MyNwind2_Test_log.ldf'
GO
0
 
LVL 44

Author Comment

by:AndyAinscow
ID: 24748749
Thanks.
I'll give that a try tomorrow (late evening here now) and get back to you with the results.
0
 
LVL 44

Author Comment

by:AndyAinscow
ID: 24760857
I've not been able to get the thing to run with that as one SQL statement.  :-(

However I have used the FILELISTONLY part to build a recordset and in code get the contents of the first field (logical name) in the returned set.  I can then use this to build the T-SQL command without the FILELISTONLY part.  That does work, maybe not quite as efficiently but it works.  Mission accomplished.

Thanks for your help.
0

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question