copying a database from one server instance to another server instance

Question

Post reply

copying a database from one server instance to another server instance

DaveBriCam

SSCarpal Tunnel

Points: 4298
More actions
August 31, 2017 at 5:25 am

#332452

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.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

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.

Phil Parkin SSC Guru Points: 246978 More actions · Answer 2

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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 3

I'm reviewing the drive structure. I do get an error that WITH MOVE is not a restore option.

Phil Parkin SSC Guru Points: 246978 More actions · Answer 4

briancampbellmcad - Thursday, August 31, 2017 5:46 AM
I'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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Tom_Hogan Hall of Fame Points: 3903 More actions · Answer 5

briancampbellmcad - Thursday, August 31, 2017 5:46 AM
I'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 ;

Perry Whittle SSC Guru Points: 234013 More actions · Answer 6

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" 😉

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 7

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.

Tom_Hogan Hall of Fame Points: 3903 More actions · Answer 8

briancampbellmcad - Friday, September 1, 2017 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.

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.

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 9

Tom_Hogan - Friday, September 1, 2017 9:12 AM
briancampbellmcad - Friday, September 1, 2017 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.
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

Phil Parkin SSC Guru Points: 246978 More actions · Answer 10

briancampbellmcad - Friday, September 1, 2017 9:32 AM
Tom_Hogan - Friday, September 1, 2017 9:12 AM
briancampbellmcad - Friday, September 1, 2017 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.
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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 11

Phil Parkin - Friday, September 1, 2017 10:42 AM
briancampbellmcad - Friday, September 1, 2017 9:32 AM
Tom_Hogan - Friday, September 1, 2017 9:12 AM
briancampbellmcad - Friday, September 1, 2017 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.
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 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.

Sue_H SSC Guru Points: 90860 More actions · Answer 12

briancampbellmcad - Tuesday, September 5, 2017 6:56 AM
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.

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

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 13

Sue_H - Tuesday, September 5, 2017 7:34 AM
briancampbellmcad - Tuesday, September 5, 2017 6:56 AM
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.
[/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.

Sue_H SSC Guru Points: 90860 More actions · Answer 14

briancampbellmcad - Tuesday, September 5, 2017 7:49 AM
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.

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