Restore Backups automatically using SQL Server

The general context is that, I try to restore a list of backups automatically using the script below. I put all the backups in the same folder, after that I put the names of the backups in a table to retrieve them easily. Finally, I put a cursor that points each time to a backup and restores it. Now when executing script, I get this error:

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

When I restore each backups independently, the operation ends successfully But when I try to restore the list I got error.

 DECLARE @name VARCHAR(50) -- database name 
 DECLARE @path VARCHAR(256) -- path for backup files 
 DECLARE @fileName VARCHAR(256) -- filename for backup 

 -- specify database backup directory
 SET @path = 'C:Program FilesMicrosoft SQL ServerMSSQL13.MSSQLSERVERMSSQLBackup'


 DECLARE @backuppath NVARCHAR(256) -- path for backup files 
 DECLARE @datapath VARCHAR(256) -- path for data files 
 DECLARE @logpath VARCHAR(256) -- path for log files 
 DECLARE @backupfileName VARCHAR(256) -- filename for backup 
 DECLARE @datafileName VARCHAR(256) -- filename for database
 DECLARE @logfileName VARCHAR(256) -- filename for logfile
 DECLARE @logName VARCHAR(256) -- filename for logfile
 DECLARE @dataName VARCHAR(256)

 -- specify database backup directory
 SET @backuppath = 'C:Program FilesMicrosoft SQL ServerMSSQL13.MSSQLSERVERMSSQLBackup'

 SET @datapath = 'C:Program FilesMicrosoft SQL ServerMSSQL13.MSSQLSERVERMSSQLDATA'
 SET @logpath = 'C:Program FilesMicrosoft SQL      ServerMSSQL13.MSSQLSERVERMSSQLDATA'

 print 'backup path is ' + @backuppath
 print 'data path is ' + @datapath
 print 'log path is ' + @logpath

 /*Table to hold each backup file name in*/
 CREATE TABLE #List(fname varchar(200),depth int, file_ int)
 INSERT #List
 EXECUTE master.dbo.xp_dirtree @backuppath, 1, 1
 SELECT * FROM #List

 DECLARE files CURSOR FOR
 SELECT fname FROM #List

 OPEN files
 FETCH NEXT FROM files INTO @name   

 WHILE @@FETCH_STATUS = 0  
 BEGIN  
   DECLARE @cleanname AS VARCHAR(255)
   SET @cleanname  = REPLACE(@name, '.BAK', '')
   PRINT @cleanname

   SET @backupfileName = @backuppath + @name
   SET @datafileName = @datapath + @cleanname   + '.MDF'
   SET @logfileName = @logpath + @cleanname   + '_log.LDF'
   SET @logName = @cleanname + '_log'
   SET @dataName = @cleanname + '_DATA'

   print 'backup file is ' + @backupfileName
   print 'data file is ' + @datafileName
   print 'log file is ' + @logfileName

   USE [master]
   RESTORE DATABASE @cleanname
   FROM  DISK = @backupfileName 
   WITH  FILE = 1, 
   MOVE @dataName TO @datafileName, 
   MOVE @logName TO  @logfileName, 
   NOUNLOAD,  STATS = 5
   FETCH NEXT FROM files INTO @name  
 END  

 CLOSE files  
 DEALLOCATE files
 DROP TABLE #List
 GO

I expect in the output that the backups will be restored and I could see them in the database bar, but the actual output is error that I mentioned above.

Answer

SQL Server databases consist of a number of “Logical Files“. When you restore a database you need to say where these files should be stored. You are doing that, but your code makes assumptions about the names of these logical files. You are assuming they are FileName_Data and FileName_Log. The error message is telling you that is wrong in this case. If you can run the following query, look in the LogicalName field to see the actual values for this .BAK file.

RESTORE FILELISTONLY FROM DISK = N'C:MyBackupsOP38MLG_db_201903040000.bak'

Over the years, this has happened to me many times. And it’s always a surprise to find out the actual logical file names.

Advertisement

Answer