Msg 3234 on RESTORE DATABASE

I'm trying unsuccessfully to back up a database from a flash drive. See the sequence and error message below. I'm running SQL Server Express 2014 on Windows 8.1.

use master; go backup database Staging to disk = 'e:\temp\StagingDisk.bak' with init; go

use [master];
go

restore filelistonly
from disk = N'e:\temp\StagingDisk.bak'
go

alter database Staging
set single_user with
rollback immediate

restore database Staging
  from disk = N'e:\temp\StagingDisk.bak'
  WITH REPLACE,
  move N'StagingDisk.mdf'to N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\Staging.mdf',
  move N'StagingDisk.ldf' to N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\Staging.ldf'

alter database Staging
set multi_user;
go

The backup is successful but here's the error I get on the restore:

2 row(s) affected)
Msg 3234, Level 16, State 2, Line 14
Logical file 'StagingDisk' is not part of database 'Staging'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 14
RESTORE DATABASE is terminating abnormally.


June 25th, 2015 7:01pm

looks like you made new thread for this

copying my answer from the other thread

what is output of the restore filelistonly. post this output, and we should be able to give you the exact syntax 

per error message, it is because 'stagingdisk' does not exist but you specified that in the restore command.. how did get that name??

in the output of the restore filelistonly - there should be a column for logicalname and it is what you should use in the restore command

my hunch is that it is 'staging' 

try this

restore database Staging
  from disk = N'e:\temp\StagingDisk.bak'
  WITH REPLACE,
  move N'Staging'to N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\Staging.mdf',
  move N'Staging_log' to N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\Staging.ldf'


Free Windows Admin Tool Kit Click here and download it now
June 25th, 2015 7:12pm

Hi 

Your restore statement should include the logical filename after the MOVE

restore database Staging
  from disk = N'e:\temp\StagingDisk.bak'
  WITH REPLACE,
  move N'LOGICAL_FILE_NAME_DATA_FILE'to N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\Staging.mdf',
  move N'LOGICAL_FILE_NAME_LOG_FILE' to N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\Staging.ldf'

You can get the logical file name by restoring the backup with FILELISTONLY(first column)

restore filelistonly 
from disk = 'e:\temp\StagingDisk.bak'
Hope that helps

  • Proposed as answer by Olaf HelperMVP 44 minutes ago
  • Unproposed as answer by sfTim 25 minutes ago
June 25th, 2015 7:14pm

Hi Stan: I created an empty 'Staging' database on the target machine so I could eliminate the issue in the thread title. So I started a new thread. I have this straightened finally(!). Here's what worked for me:

Step 1:
Get the logical and physical names of the data and log files. Run this query:

use master;
go

restore filelistonly
from disk = N'e:\temp\StagingDisk.bak'
go

Here's the important part of the results set:

LogicalName	PhysicalName	Type	FileGroupName
Staging	        C:\Program Files (x86)\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\Staging.mdf	        D	PRIMARY
Staging_log	C:\Program Files (x86)\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\Staging_log.ldf	L	NULL

Step 2:
Use these logical and physical names in the restore query as noted below:

USE [master];
GO

alter database Staging
set single_user with
rollback immediate

restore database Staging
from disk = N'e:\temp\StagingDisk.bak'
with replace,
--Note: 'MOVE' <logical name> 'TO' <physical name>
move N'Staging'to N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\Staging.mdf',
move N'Staging_log' to N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\Staging_log.ldf'

alter database Staging
set multi_user;
go

One weird thing. Notice that the physical name path includes "Program Files (x86)". This was run on a brand new laptop running Windows 8.1 and the 64-bit version of SQL Server 2014 Express. Seems to me that the path should be "Program Files". 

Free Windows Admin Tool Kit Click here and download it now
June 25th, 2015 8:39pm

right click on the servername in the object explorer - go to -- properties -- on the left side pane - you should General -- in there --  check platform -- what does it say (64 or 32)

also, in there on the left pane - you should see - database settings- what does the default database locations have?? 

June 25th, 2015 10:40pm

The target machine is platform=NT x64 with the database paths "Program Files". However, the source machine that produced the .bak on the flash drive is an x86 machine with paths "Program Files (x86)".
Free Windows Admin Tool Kit Click here and download it now
June 25th, 2015 11:49pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics