Sql-server – How to restore SQL Server database to a custom location, if the backup file was renamed

backuprestoresql server

Suppose I have an SQL Server database called bubu and the respective backup file is bubu.bak.

If I want to restore it with the backing store in a custom location, I use the following T-SQL statement:

RESTORE DATABASE "bubu2" FROM DISK = N'C:\bubu.bak' WITH REPLACE, STATS = 10, 
    MOVE N'bubu' TO N'C:\custom\bubu.mdf', MOVE N'bubu_log' TO N'C:\custom\bubu.LDF'

And so I have a new database bubu2, which happens to be the backup copy of bubu. The important thing here is that I assume that the backup contains the logical files bubu and bubu_log, because the backup file is named bubu.bak.

Now, suppose someone renames bubu.bak to mumu.bak. Doing so, breaks my logic, since now I will try this T-SQL statement:

RESTORE DATABASE "mumu2" FROM DISK = N'C:\mumu.bak' WITH REPLACE, STATS = 10, 
    MOVE N'mumu' TO N'C:\custom\mumu.mdf', MOVE N'mumu_log' TO N'C:\custom\mumu.LDF'

And of course, I fail, because mumu.bak does not contain the logical files mumu and mumu_log – renaming the backup file does not rename the logical files contained in the backup (of course).

But then, how do I do what I need if the backup file was renamed?

Best Answer

You can use the filelistonly option to retrieve the files names in a backup:

restore filelistonly from disk='c:\mumu.bak'
Related Question