List Logical Names from SQL Server bak file
By Pete Freitag
When restoring a SQL Server database from a bak file you may need to know what the Logical Names of the storage files are in order to restore them. Here's how you can do it from a SQL statement:
RESTORE FILELISTONLY FROM DISK='/path/to/example-db.bak'
The LogicalName in this case might be example and example_log, it doesn't always correspond to the file name of your bak file.
If you are doing this inside a SQL Server docker container (as I needed to), you might be able to run the SQL statement like this:
/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "$MSSQL_SA_PASSWORD" -Q "RESTORE FILELISTONLY FROM DISK='/path/to/example-db.bak'"
Once I know the LogicalName's I can restore, by running SQL like this:
RESTORE DATABASE example FROM DISK='/data/example-db.bak' WITH MOVE 'example' TO '/var/opt/mssql/data/example.mdf', MOVE 'example_log' TO '/var/opt/mssql/data/example.ldf'
List Logical Names from SQL Server bak file was first published on January 13, 2020.