Technical FAQs

Ask a Question

How to convert backup (.bak) database files to .mdf and .ldf files

Issue
During migration and upgrade of databases, ideally, we should have both the backup database file (with .bak extension) as well as originals (.mdf and .ldf files). If we only have backups but not the originals, we can extract .mdf and .ldf files using a SQL query in SQL Server Management Studio.
 
Product Line
SQL Server 2005 (Standard and Express) 
SQL Server 2008
SQL Server 2008 R2 (Standard and Express) 
SQL Server 2012
SQL Server 2014
 
Environment
SQL Server Management Studio
 
Cause
It may be necessary to have the .mdf and .ldf files when restoring a database.
 
Resolution

*Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio. Databases should be backed up prior to performing this procedure.* 

Please note the two directory paths used  below in the sql query is only for the purpose of demonstration. While using the query, one has to change the source and destination directories, as appropriate to their needs.
 
Let’s consider a case where a user has the backup file (.bak) of the ION_Network database in 'C:\Program files (x86)\Schneider Electric\Power Monitoring Expert\config\cfg\DBBackups\Network'
The user wants to extract the .mdf and .ldf files from the backup file into this location 'C:\ Program files (x86)\Schneider Electric\Power Monitoring Expert\Database\Network'
In SQL Server Management Studio open a new query window and execute the following query (paste the following query first, change the file path section in the second line according to what it should be in this specific case, highlight the code snippet and hit ‘! Execute’ button).
 
RESTORE FILELISTONLY
FROM DISK = 'C:\Program files (x86)\Schneider Electric\Power Monitoring Expert\config\cfg\DBBackups\Network \ION_Network.bak'
GO
 
After the successful running of the above query, in the ‘Results’ window that will appear below please note the ‘Logical Name’ of the .mdf and .ldf files. Use the logical names in the yellow highlighted space below and execute the entire block at the same time.
 
RESTORE FILELISTONLY
FROM DISK = 'C:\Program files (x86)\Schneider Electric\Power Monitoring Expert\config\cfg\DBBackups\Network \ION_Network.bak'
GO
RESTORE DATABASE ION_Network
FROM DISK = 'C:\Program files (x86)\Schneider Electric\Power Monitoring Expert\config\cfg\DBBackups\Network \ION_Network.bak'
WITH MOVE 'ION_Network_Data' TO 'C:\ Program files (x86)\Schneider Electric\Power Monitoring Expert\Database\Network \ION_Network.mdf',
MOVE 'ION_Network_Log' TO 'C:\ Program files (x86)\Schneider Electric\Power Monitoring Expert\Database\Network \ION_Network.ldf'
Was this helpful?
What can we do to improve the information ?