SQL-Server: Error - Logical file is not part of database. Use RESTORE FILELISTONLY to list the logical file names

Question

I made a script to restore .bak or backup files. It works for some databases, but not for one. How do I make it work for any type of .bak file ? This is in sql server 2008. The error message is -

Msg 3234, Level 16, State 1, Line 1
Logical file 'Northwind_Data' is not part of database 'Northwind'. 
Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Script -

IF DB_ID('Northwind') IS NULL
BEGIN
RESTORE DATABASE [Northwind]
FILE = N'Northwind_Data'
FROM 
DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SS2008\MSSQL\Backup\Northwind.bak'
WITH  FILE = 1,
MOVE N'Northwind_Data'
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SS2008\MSSQL\DATA\Northwind.mdf',
MOVE N'Northwind_Log'
 TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SS2008\MSSQL\DATA\Northwind_0.LDF',
NOUNLOAD,  STATS = 10
END

Here was suggested to use free Database Restore software: http://stackoverflow.com/questions/1360529/how-do-you-backup-and-restore-a-database-as-a-copy-on-the-same-server/ — T.Todua, Jul 23 '14 at 14:01

score 102 · Answer 1 · edited Jul 16 '14 at 12:14

102

Please run below sql and check logical names

RESTORE FILELISTONLY 
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SS2008\MSSQL\Backup\Northwind.bak'

And then replace logical name shown by RESTORE FILELISTONLY in script below

--If database already exists do not restore
IF DB_ID('Northwind') IS NULL 
BEGIN
  RESTORE DATABASE [Northwind]
  FILE = N'Northwind_Data'
  FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SS2008\MSSQL\Backup\Northwind.bak'
  WITH 
    FILE = 1, NOUNLOAD, STATS = 10,
    MOVE N'YOUR logical name of data file as shown by RESTORE FILELISTONLY command'
    TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SS2008\MSSQL\DATA\Northwind.mdf',
    MOVE N'YOUR logical name of Log file as shown by RESTORE FILELISTONLY command'
    TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SS2008\MSSQL\DATA\Northwind_0.LDF'
END

edited Jul 16 '14 at 12:14

Junior Mayhé

16,144
26
115
161

answered Mar 07 '14 at 09:22

Saurabh Sinha

1,363
1
9
11

I get an error - Msg 102, Level 15, State 1, Line 8 Incorrect syntax near '*'. – Steam Mar 09 '14 at 18:51
1

Blasto : you have to take output of first query "logical name " and place in second query. ** was mention as there you have to replace logical name. So fom above query **N'logical name of data file'** should be replace with 'output of 1st query'. If you share your 1st query output, i will tell yo what to replace where. You can also search "restore database with move" to get lot of helpful self explanatory links – Saurabh Sinha Mar 10 '14 at 17:03
3

If you need to change logical name after restoring database, run `ALTER DATABASE [Northwind] MODIFY FILE (NAME=N'Northwind_Data', NEWNAME=N'YourDatabaseName_Data'); ALTER DATABASE [Northwind] MODIFY FILE (NAME=N'Northwind_log', NEWNAME=N'YourDatabaseName_log');` – Junior Mayhé Jul 16 '14 at 12:17
This solution works for me. One thing to notice you need to put "logic file name" in MOVE, not "physical file name". – user908645 Dec 05 '17 at 22:27
How do you do that in code? I have a bunch of tables, and they named the stupid logical names randomly. As such, I need to do a select * from (restore filelistonly...) and do the first one for the data and the 2nd one for the log. but I can't get the output of restore... – Traderhut Games Jul 17 '18 at 21:10
This answer does not help me learn or understand the problem. More explanation would be helpful. – mopo922 Feb 04 '20 at 21:35

score 12 · Answer 2 · answered Aug 26 '20 at 18:07

I experienced this issue when trying to restore a database on MS SQL Server 2012.

Here's is my script for restoring the database:

USE master;
GO

ALTER DATABASE my_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

    
RESTORE DATABASE my_db
    FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\my_db_backup.bak'
    WITH REPLACE,
    MOVE 'my_db' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\my_db.mdf',
    MOVE 'my_db_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\my_db_log.ldf'
GO
    
ALTER DATABASE my_db SET MULTI_USER;
GO

And I was encountering the error:

Msg 3234, Level 16, State 1, Line 1
Logical file 'my_db' is not part of database 'my_db'. 
Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Here's how I fixed it:

The issue was that I was not referencing the logical files properly.

I had to run the RESTORE FILELISTONLY command below on the backup file:

RESTORE FILELISTONLY 
    FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\my_db_backup.bak'

This displayed the LogicalName and the corresponding PhysicalName of the Data and Log files for the database respectively:

LogicalName      PhysicalName               
com.my_db        C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\com.my_db.mdf
com.my_db_log    C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\com.my_db_log.ldf

All I had to do was to simply replace the LogicalName and the corresponding PhysicalName of the Data and Log files for the database respectively in the script:

USE master;
GO

ALTER DATABASE my_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

    
RESTORE DATABASE my_db
    FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\my_db_backup.bak'
    WITH REPLACE,
    MOVE 'com.my_db' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\com.my_db.mdf',
    MOVE 'com.my_db_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\com.my_db_log.ldf'
GO
    
ALTER DATABASE my_db SET MULTI_USER;
GO

And the Database Restore task ran successfully:

This post helped me in figuring it out: SQL Error – Logical file ‘XYZ_Log2’ is not part of database ‘XYZ’. Use RESTORE FILELISTONLY to list the logical file names.

That's all.

I hope this helps

You deserve the cred here. This was amazing. well laid out and worked like a charm. Thanks! — Jason P Sallinger, Jun 23 '23 at 15:06

score 3 · Answer 3 · edited May 19 '16 at 19:16

3

Check the database properties and make sure the logical name is the same as the file name. Use Alter Database command to modify those:

ALTER DATABASE [Northwind] MODIFY FILE (NAME=N'Northwind_Data', NEWNAME=N'Northwind')
GO
ALTER DATABASE [Northwind] MODIFY FILE (NAME=N'Northwind_Data_log', NEWNAME=N'Northwind_log')
GO

edited May 19 '16 at 19:16

answered May 19 '16 at 18:49

AssociateDBA

31
1

score 1 · Answer 4 · answered Sep 21 '22 at 15:41

This question is probably too old but I hope this answer can help someone. I got into a situation today where I had to create a backup from a windows environment and import it into a docker container on macOS.

Once you have the .bak file, you just need to do following 2 steps and your backup should be imported just fine.

Get LogicalName from the .bak file:

RESTORE FILELISTONLY 
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SS2008\MSSQL\Backup\YourDB.bak'

Then you just need to run following command. (Make sure to replace LogicalName (in single quote) after Move keyword)

RESTORE DATABASE medjobs_devCurrent FROM DISK = N'/var/opt/mssql/data/YourDB.bak'
WITH MOVE 'YourDB' TO '/var/opt/mssql/data/YourDB.mdf'
, MOVE 'YourDB_log' TO '/var/opt/mssql/data/YourDB_log.ldf'

Credit for step 1 goes to @Saurabh Sinha.

score 0 · Answer 5 · answered Aug 23 '19 at 18:55

I was using Powershell to do this and I had the same error. What got me was that I was using "$db_log.mdf" and the underscore is a valid character for variable definitions so it was really looking for $db_log and not concatenating.

So my code looked like this:

  $db = "MyNewDb"
  $restoreSuffix = "_V1"
  $newDbName = $db
  $dataFileOrigin = $db
  $logFileOrigin = $db+"_log"
  $dataFileLocation = "$dataAndLogFileDestination\$db$restoreSuffix.mdf"
  $logFileLocation = "$dataAndLogFileDestination\$db$restoreSuffix"+"_log.ldf"

  echo "Renaming and Relocating files to ($newDbName)"

  $RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("$dataFileOrigin", "$dataFileLocation")
  $RelocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("$logFileOrigin", "$logFileLocation")
  Restore-SqlDatabase -ServerInstance "." -Database "$newDbName" -BackupFile "$backupLocation$db.bak" -RelocateFile @($RelocateData,$RelocateLog)

score 0 · Answer 6 · answered May 18 '20 at 11:30

I was trying to restore the database from a backup on a UNC path. There were 2 problems:

The database started with numbers: 123DbName so this had to be wrapped with [] like [123DbName]
I was writing the full UNC path of server on which I wanted to move: \\server\e$\data | \\server\f$\log, once I removed the server part and kept only e and f everything worked.

score -2 · Answer 7 · edited May 23 '17 at 12:26

-2

What you're asking for is not trivial, and has several potential pitfalls (do you want to overwrite the database if it exists?, what if the database is in use when you try to overwrite? do you want to put the physical files in the same directory all the time? , etc.)..

Luckily it's been asked before, look here, I haven't tested it but it looks sound.

edited May 23 '17 at 12:26

Community

1
1

answered Mar 08 '14 at 01:55

SqlACID

4,024
20
28

1

Thanks acid. I am looking for a simple script to restore dbs without worrying about overwrite, in use etc. This is a simple exercise for myself. I wonder why MS does not provide this functionality. – Steam Mar 08 '14 at 06:31

SQL-Server: Error - Logical file is not part of database. Use RESTORE FILELISTONLY to list the logical file names

7 Answers7