RESTORE DATABASE Server: Msg 3234, Level 16, State 2, Line 2

I am trying to RESTORE a SQL2003 database and receiving the following error:

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

I referenced this solution http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/knowledgeBaseEntry.jsp?qid=21493697

But I'm not able to get past the first step to achieve the next which is to rename the files.


Here is my statement:

RESTORE DATABASE maxdev
   FROM DISK='C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\maxprod_bak'

   WITH MOVE 'maxprod' TO 'D:\maxdevdatabase\maxprod.mdf',
   MOVE 'maxprod_log' TO 'D:\maxdevlogs\maxprod_log.ldf'
GO

I can NOT use the same named database because it already exists in the enterprise.  Generally speaking what I'm trying to accomplish is to have a development version of my production database "maxprod".

Thanks.
psueocAsked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
restore database maxdev
 from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\maxprod_bak'
with
 move 'maxdev_Data' to 'D:\maxdevdatabase\maxdev_Data.mdf',
 move 'maxdev_log' to 'D:\maxdevdatabase\maxdev_Log.ldf'


---you can see the Logical file names from the above restore filelistonly command
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
it seems like the name "MaxPod" is not in the list , just run    

RESTORE FILELISTONLY MaxDev

this will list the logical files, which you need to put in your restore command

RESTORE DATABASE maxdev
   FROM DISK='C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\maxprod_bak'

   WITH MOVE 'PutTheLogicalFilenameHere' TO 'D:\maxdevdatabase\maxprod.mdf',  ---------------------- 1
   MOVE 'maxprod_log' TO 'D:\maxdevlogs\maxprod_log.ldf'
GO
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Do a restore filelistonly first, as stated. When restoring, I suggest changing the target file names to (not only the directory). This means:

restore filelistonly from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\maxprod_bak';
restore database maxdev
 from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\maxprod_bak'
with
 move 'maxprod' to 'D:\maxdevdatabase\maxdev.mdf',
 move 'maxprod_log' to 'D:\maxdevdatabase\maxdev.ldf'




0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
I guess it's not logical file name 'maxprod' but 'maxprod_data' ...
0
 
psueocAuthor Commented:
I'm receiving the following error:


(2 row(s) affected)

Server: Msg 3234, Level 16, State 2, Line 2
Logical file 'maxprod' is not part of database 'maxdev'. Use RESTORE FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
as aneeshattingal described, the logical file name might not be correct. Check that (see above)
0
 
psueocAuthor Commented:
Tried:
RESTORE FILELISTONLY maxdev

Receive error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'FILELISTONLY'.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
try this

RESTORE FILELISTONLY
FROM DISK='C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\maxprod_bak'
0
 
psueocAuthor Commented:
Executed:
RESTORE FILELISTONLY
FROM DISK='C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\maxprod_bak'

Resulted:
maxdev_Data      D:\maxproddatabase\maxprod.mdf      D      PRIMARY      47382528      35184372080640
maxdev_Log      D:\maxprodlogs\maxprod_log.ldf      L      NULL      704905216      35184372080640

Executed:
restore filelistonly from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\maxprod_bak';
restore database maxdev
 from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\maxprod_bak'
with
 move 'maxprod' to 'D:\maxdevdatabase\maxdev_Data.mdf',
 move 'maxprod_log' to 'D:\maxdevdatabase\maxdev_Log.ldf'

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

I must be missing something here?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
restore database maxdev
 from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\maxprod_bak'
with
 move 'maxdev_Data' to 'D:\maxdevdatabase\maxdev_Data.mdf',
 move 'maxprod_log' to 'D:\maxdevdatabase\maxdev_Log.ldf'
0
 
psueocAuthor Commented:
Executed:
restore database maxdev
 from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\maxprod_bak'
with
 move 'maxdev_Data' to 'D:\maxdevdatabase\maxdev_Data.mdf',
 move 'maxprod_log' to 'D:\maxdevdatabase\maxdev_Log.ldf'

Results:
Server: Msg 3234, Level 16, State 2, Line 1
Logical file 'maxprod_log' is not part of database 'maxdev'. Use RESTORE FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
0
 
psueocAuthor Commented:
aneeshattingal:

Your solution was almost correct.
The statement needed to include the "WITH REPLACE" option as shown below:

restore database maxdev
 from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\maxprod_bak'
with replace,
 move 'maxdev_Data' to 'D:\maxdevdatabase\maxdev_Data.mdf',
 move 'maxdev_log' to 'D:\maxdevdatabase\maxdev_Log.ldf'


I sincerely appreciate all of your prompt responses.

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
:)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.