SQL with Manoj

I was restoring Databases as a routine monthly job. Suddenly in between I came across an error which didn’t let me restore a Database. I restored other Databases and left this one to do at the end. Finally I picked it up again and saw that some new kind of error I was facing. The error message says to use “RESTORE FILELISTONLY”, I check the syntax on MSDN and found that this Database had two log files (LDF) earlier, but now configured for only one. And as I was using the same old script to Restore it, I was getting this error.

Let’s try to reproduce it in a standalone box:

–> I took a backup of AdventureWorks2012 Database on my machine.

I created the RESTORE script and added another line of log file at line 8 below:

USE [master]
GO

RESTORE DATABASE [AdventureWorks2012] 
FROM DISK = N'E:\Softwares\MS_bits\AdventureWorks2012.bak' WITH  FILE = 1,  
MOVE N'AdventureWorks2012_Data' TO N'E:\MSSQL11\DATA\AdventureWorks2012_Data.mdf', 
MOVE N'AdventureWorks2012_Log' TO N'E:\MSSQL11\Log\AdventureWorks2012_Log.ldf', 
MOVE N'AdventureWorks2012_Log2' TO N'E:\MSSQL11\Log\AdventureWorks2012_Log2.ldf', 
NOUNLOAD,  REPLACE,  NOUNLOAD,  STATS = 5
GO

On executing I got an error as expected, shown below:

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

As suggested in the Error Message, I used the “RESTORE FILELISTONLY” command to check what all files this Database is using:

RESTORE FILELISTONLY from disk=N'E:\Softwares\MS_bits\AdventureWorks2012.bak'

And I could see only two, one MDF & one LDF, as shown below:

LogicalName		PhysicalName					Type	FileGroupName
AdventureWorks2012_Data	E:\MSSQL11\DATA\AdventureWorks2012_Data.mdf	D	PRIMARY
AdventureWorks2012_Log	E:\MSSQL11\Log\AdventureWorks2012_log.ldf	L	NULL

So, I removed the extra LDF log file option from the RESTORE script, as shown below:

RESTORE DATABASE [AdventureWorks2012x] 
FROM  DISK = N'E:\Softwares\MS_bits\AdventureWorks2012.bak' WITH  FILE = 1,  
MOVE N'AdventureWorks2012_Data' TO N'E:\MSSQL11\DATA\AdventureWorks2012_Data.mdf', 
MOVE N'AdventureWorks2012_Log' TO N'E:\MSSQL11\Log\AdventureWorks2012_Log.ldf', 
--MOVE N'AdventureWorks2012_Log2' TO N'E:\MSSQL11\Log\AdventureWorks2012_Log2.ldf', 
NOUNLOAD,  REPLACE,  NOUNLOAD,  STATS = 5
GO

The above statement executed fine and the Database restored successfully.

So, I learnt a new thing here about the “RESTORE FILELISTONLY” command. This command tell us what all Database files (MDF, NDF, LDF) a Backup file contains without actually Restoring the Database.