August 31, 2017 at 5:25 am
I am trying to copy a database from one server instance to another server instance. Both instances are viewable in my SSMS session. I tried the wizard with no luck so I was hoping to do it with T-Sql. To avoid overwriting the production AAA_DB database I disconnected the source instance. When I run the below T-Sql I get a lot of errors. Any ideas?:
RESTORE DATABASE AAA_DB
FROM DISK='\\MyInstance1\Databases\Backups\AAA_DB\AAA_DB_backup_201611140023.bak'
WITH REPLACE
Msg 5133, Level 16, State 1, Line 2
Directory lookup for the file "D:\Databases\Data\AAA.mdf" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 2
File 'BCC' cannot be restored to 'D:\Databases\Data\AAA.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Line 2
Directory lookup for the file "D:\Databases\Data\AAA.ldf" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 2
File 'BCC_log' cannot be restored to 'D:\Databases\Data\AAA_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 2
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
August 31, 2017 at 5:39 am
Do you have a directory D:\Databases\Data on the server your restoring to?
If not you will need to create that directory or use MOVE to specify the file paths to put the MDF and LDF files to.
August 31, 2017 at 5:41 am
Sounds like you have a different drive structure on each instance. Have a read here to see whether it explains how to resolve it.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 31, 2017 at 5:46 am
I'm reviewing the drive structure. I do get an error that WITH MOVE is not a restore option.
August 31, 2017 at 5:52 am
briancampbellmcad - Thursday, August 31, 2017 5:46 AMI'm reviewing the drive structure. I do get an error that WITH MOVE is not a restore option.
Please post the T-SQL you used and the full text of the error.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 31, 2017 at 5:54 am
briancampbellmcad - Thursday, August 31, 2017 5:46 AMI'm reviewing the drive structure. I do get an error that WITH MOVE is not a restore option.
You need to move each file.
RESTORE DATABASE Sample_DB
FROM DISK = 'C:\Backups\Sample_DB.bak'
WITH
MOVE 'Sample_DB' TO 'C:\DATABASES\Sample_DB.mdf',
MOVE 'Sample_DB_LOG' TO 'C:\DATABASES\Sample_DB_log.ldf',
REPLACE
;
September 1, 2017 at 5:17 am
to get the detail of each file use the following query against the backup file(s)
restore filelistonly from disk = 'drive:\some\path\backupfile.bak'
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 1, 2017 at 8:07 am
I ran this command with no errors:
restore filelistonly from disk = 'C:\DATABASES\ACS_DB_backup_201709010016.bak'
Which gave me a physical path of D:\Databases\Data\ACS_DB_Data.MDF and D:\Databases\Data\ACS_DB_Log.LDF
But this fails:
RESTORE DATABASE Sample_DB
FROM DISK = 'C:\DATABASES\ACS_DB_backup_201709010016.bak'
WITH
MOVE 'ACS_DB' TO 'D:\Databases\Data\ACS_DB_Data.MDF',
MOVE 'ACS_DB_LOG' TO 'D:\Databases\Data\ACS_DB_Log.LDF',
REPLACE
;
With this error:
Msg 3234, Level 16, State 2, Line 1
Logical file 'ACS_DB' is not part of database 'Sample_DB'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
September 1, 2017 at 9:12 am
briancampbellmcad - Friday, September 1, 2017 8:07 AMI ran this command with no errors:
restore filelistonly from disk = 'C:\DATABASES\ACS_DB_backup_201709010016.bak'Which gave me a physical path of D:\Databases\Data\ACS_DB_Data.MDF and D:\Databases\Data\ACS_DB_Log.LDF
But this fails:
RESTORE DATABASE Sample_DB
FROM DISK = 'C:\DATABASES\ACS_DB_backup_201709010016.bak'
WITH
MOVE 'ACS_DB' TO 'D:\Databases\Data\ACS_DB_Data.MDF',
MOVE 'ACS_DB_LOG' TO 'D:\Databases\Data\ACS_DB_Log.LDF',
REPLACE
;With this error:
Msg 3234, Level 16, State 2, Line 1
Logical file 'ACS_DB' is not part of database 'Sample_DB'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Do the logical names in the MOVE statements actually match what's in the results of the RESTORE FILELISTONLY command? You're moving the logical names to the new physical location on your new system.
It may also be that you should be using your database name instead of Sample_DB on the Restore Database line.
September 1, 2017 at 9:32 am
Tom_Hogan - Friday, September 1, 2017 9:12 AMbriancampbellmcad - Friday, September 1, 2017 8:07 AMI ran this command with no errors:
restore filelistonly from disk = 'C:\DATABASES\ACS_DB_backup_201709010016.bak'Which gave me a physical path of D:\Databases\Data\ACS_DB_Data.MDF and D:\Databases\Data\ACS_DB_Log.LDF
But this fails:
RESTORE DATABASE Sample_DB
FROM DISK = 'C:\DATABASES\ACS_DB_backup_201709010016.bak'
WITH
MOVE 'ACS_DB' TO 'D:\Databases\Data\ACS_DB_Data.MDF',
MOVE 'ACS_DB_LOG' TO 'D:\Databases\Data\ACS_DB_Log.LDF',
REPLACE
;With this error:
Msg 3234, Level 16, State 2, Line 1
Logical file 'ACS_DB' is not part of database 'Sample_DB'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.Do the logical names in the MOVE statements actually match what's in the results of the RESTORE FILELISTONLY command? You're moving the logical names to the new physical location on your new system.
It may also be that you should be using your database name instead of Sample_DB on the Restore Database line.
I corrected the 'Sample_DB' error. When I run the RESTORE FILELISTONLY command here's what comes back:
Logical: ACS_DB_Data Physical: D:\Databases\Data\ACS_DB_Data.MDF
Logical: ACS_DB_Log Physical: D:\Databases\Data\ACS_DB_Log.LDF
September 1, 2017 at 10:42 am
briancampbellmcad - Friday, September 1, 2017 9:32 AMTom_Hogan - Friday, September 1, 2017 9:12 AMbriancampbellmcad - Friday, September 1, 2017 8:07 AMI ran this command with no errors:
restore filelistonly from disk = 'C:\DATABASES\ACS_DB_backup_201709010016.bak'Which gave me a physical path of D:\Databases\Data\ACS_DB_Data.MDF and D:\Databases\Data\ACS_DB_Log.LDF
But this fails:
RESTORE DATABASE Sample_DB
FROM DISK = 'C:\DATABASES\ACS_DB_backup_201709010016.bak'
WITH
MOVE 'ACS_DB' TO 'D:\Databases\Data\ACS_DB_Data.MDF',
MOVE 'ACS_DB_LOG' TO 'D:\Databases\Data\ACS_DB_Log.LDF',
REPLACE
;With this error:
Msg 3234, Level 16, State 2, Line 1
Logical file 'ACS_DB' is not part of database 'Sample_DB'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.Do the logical names in the MOVE statements actually match what's in the results of the RESTORE FILELISTONLY command? You're moving the logical names to the new physical location on your new system.
It may also be that you should be using your database name instead of Sample_DB on the Restore Database line.
I corrected the 'Sample_DB' error. When I run the RESTORE FILELISTONLY command here's what comes back:
Logical: ACS_DB_Data Physical: D:\Databases\Data\ACS_DB_Data.MDF
Logical: ACS_DB_Log Physical: D:\Databases\Data\ACS_DB_Log.LDF
So you have now solved the puzzle yourself. Check & fix your earlier code based on the revised logical data file name.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 5, 2017 at 6:56 am
Phil Parkin - Friday, September 1, 2017 10:42 AMbriancampbellmcad - Friday, September 1, 2017 9:32 AMTom_Hogan - Friday, September 1, 2017 9:12 AMbriancampbellmcad - Friday, September 1, 2017 8:07 AMI ran this command with no errors:
restore filelistonly from disk = 'C:\DATABASES\ACS_DB_backup_201709010016.bak'Which gave me a physical path of D:\Databases\Data\ACS_DB_Data.MDF and D:\Databases\Data\ACS_DB_Log.LDF
But this fails:
RESTORE DATABASE Sample_DB
FROM DISK = 'C:\DATABASES\ACS_DB_backup_201709010016.bak'
WITH
MOVE 'ACS_DB' TO 'D:\Databases\Data\ACS_DB_Data.MDF',
MOVE 'ACS_DB_LOG' TO 'D:\Databases\Data\ACS_DB_Log.LDF',
REPLACE
;With this error:
Msg 3234, Level 16, State 2, Line 1
Logical file 'ACS_DB' is not part of database 'Sample_DB'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.Do the logical names in the MOVE statements actually match what's in the results of the RESTORE FILELISTONLY command? You're moving the logical names to the new physical location on your new system.
It may also be that you should be using your database name instead of Sample_DB on the Restore Database line.
I corrected the 'Sample_DB' error. When I run the RESTORE FILELISTONLY command here's what comes back:
Logical: ACS_DB_Data Physical: D:\Databases\Data\ACS_DB_Data.MDF
Logical: ACS_DB_Log Physical: D:\Databases\Data\ACS_DB_Log.LDFSo you have now solved the puzzle yourself. Check & fix your earlier code based on the revised logical data file name.
The error persists... I changed the obvious logical name of the DB, but still the error:
--restore filelistonly from disk = 'C:\DATABASES\ACS_DB_backup_201709010016.bak'
RESTORE DATABASE ACS_DB
FROM DISK = 'C:\DATABASES\ACS_DB_backup_201709010016.bak'
WITH
MOVE 'ACS_DB' TO 'D:\Databases\Data\ACS_DB_Data.MDF',
MOVE 'ACS_DB_LOG' TO 'D:\Databases\Data\ACS_DB_Log.LDF',
REPLACE
;
--Msg 3234, Level 16, State 2, Line 3
--Logical file 'ACS_DB' is not part of database 'ACS_DB'. Use RESTORE FILELISTONLY to list the logical file names.
--Msg 3013, Level 16, State 1, Line 3
--RESTORE DATABASE is terminating abnormally.
September 5, 2017 at 7:34 am
briancampbellmcad - Tuesday, September 5, 2017 6:56 AMThe error persists... I changed the obvious logical name of the DB, but still the error:
--restore filelistonly from disk = 'C:\DATABASES\ACS_DB_backup_201709010016.bak'
RESTORE DATABASE ACS_DB
FROM DISK = 'C:\DATABASES\ACS_DB_backup_201709010016.bak'
WITH
MOVE 'ACS_DB' TO 'D:\Databases\Data\ACS_DB_Data.MDF',
MOVE 'ACS_DB_LOG' TO 'D:\Databases\Data\ACS_DB_Log.LDF',
REPLACE
;--Msg 3234, Level 16, State 2, Line 3
--Logical file 'ACS_DB' is not part of database 'ACS_DB'. Use RESTORE FILELISTONLY to list the logical file names.
--Msg 3013, Level 16, State 1, Line 3
--RESTORE DATABASE is terminating abnormally.
That's the same command you ran before. You are using the logical name ACS_DB for the data file when the logical name is ACS_DB_DATA
Sue
September 5, 2017 at 7:49 am
Sue_H - Tuesday, September 5, 2017 7:34 AMbriancampbellmcad - Tuesday, September 5, 2017 6:56 AMThe error persists... I changed the obvious logical name of the DB, but still the error:
--restore filelistonly from disk = 'C:\DATABASES\ACS_DB_backup_201709010016.bak'RESTORE DATABASE ACS_DB
FROM DISK = 'C:\DATABASES\ACS_DB_backup_201709010016.bak'
WITH
MOVE 'ACS_DB' TO 'D:\Databases\Data\ACS_DB_Data.MDF',
MOVE 'ACS_DB_LOG' TO 'D:\Databases\Data\ACS_DB_Log.LDF',
REPLACE
;--Msg 3234, Level 16, State 2, Line 3
--Logical file 'ACS_DB' is not part of database 'ACS_DB'. Use RESTORE FILELISTONLY to list the logical file names.
--Msg 3013, Level 16, State 1, Line 3
--RESTORE DATABASE is terminating abnormally.[/code]
That's the same command you ran before. You are using the logical name ACS_DB for the data file when the logical name is ACS_DB_DATA
Sue
OK Ran the below with a new set of errors:
RESTORE DATABASE ACS_DB
FROM DISK = 'C:\DATABASES\ACS_DB_backup_201709010016.bak'
WITH
MOVE 'ACS_DB_Data' TO 'D:\Databases\Data\ACS_DB_Data.MDF',
MOVE 'ACS_DB_LOG' TO 'D:\Databases\Data\ACS_DB_Log.LDF',
REPLACE
;
Msg 5133, Level 16, State 1, Line 3
Directory lookup for the file "D:\Databases\Data\ACS_DB_Data.MDF" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 3
File 'ACS_DB_Data' cannot be restored to 'D:\Databases\Data\ACS_DB_Data.MDF'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Line 3
Directory lookup for the file "D:\Databases\Data\ACS_DB_Log.LDF" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 3
File 'ACS_DB_Log' cannot be restored to 'D:\Databases\Data\ACS_DB_Log.LDF'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 3
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.
September 5, 2017 at 7:55 am
briancampbellmcad - Tuesday, September 5, 2017 7:49 AMOK Ran the below with a new set of errors:RESTORE DATABASE ACS_DB
FROM DISK = 'C:\DATABASES\ACS_DB_backup_201709010016.bak'
WITH
MOVE 'ACS_DB_Data' TO 'D:\Databases\Data\ACS_DB_Data.MDF',
MOVE 'ACS_DB_LOG' TO 'D:\Databases\Data\ACS_DB_Log.LDF',
REPLACE
;
Msg 5133, Level 16, State 1, Line 3
Directory lookup for the file "D:\Databases\Data\ACS_DB_Data.MDF" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 3
File 'ACS_DB_Data' cannot be restored to 'D:\Databases\Data\ACS_DB_Data.MDF'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Line 3
Directory lookup for the file "D:\Databases\Data\ACS_DB_Log.LDF" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 3
File 'ACS_DB_Log' cannot be restored to 'D:\Databases\Data\ACS_DB_Log.LDF'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 3
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.
The path you use for moving the files to on the restore must exist. The errors "Use WITH MOVE to identify a valid location for the file" and "The system cannot find the path specified" indicate that the path:
D:\Databases\Data\
does not exist.
Sue
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply