Restore Database to another server

Question

Restore Database to another server

Chad Koenig

SSC Enthusiast

Points: 120
More actions
March 26, 2007 at 3:30 pm

#117979

I have a database on one server that I'm looking to restore on another server. I'm using the following T-SQL:
RESTORE DATABASE NRCSPersonnel FROM disk='E:\SQLdata\NRCSPersonnel\NRCSPersonnelBackup.BAK'
WITH MOVE 'NRCSPersonnelDat' to 'E:\SQLdata\NRCSPersonnel\DataTabs.ndf',
MOVE 'NRCSPersonnelNdx' to 'E:\SQLdata\NRCSPersonnel\Indexes.ndf',
MOVE 'NRCSPersonnelSys' to 'E:\SQLdata\NRCSPersonnel\SysTabs.mdf',
MOVE 'NRCSPersonnelText' to 'E:\SQLdata\NRCSPersonnel\Text.ndf',
MOVE 'NRCSPersonnelLog' to 'E:\SQLdata\NRCSPersonnel\TransLog.ldf',
REPLACE
I get the following messages.
Server: Msg 3156, Level 16, State 1, Line 1
File 'NRCSPersonnelSys' cannot be restored to 'E:\SQLdata\NRCSPersonnel\SysTabs.mdf'. Use WITH MOVE to identify a valid location for the file.
Server: Msg 3156, Level 16, State 1, Line 1
File 'NRCSPersonnelDat' cannot be restored to 'E:\SQLdata\NRCSPersonnel\DataTabs.ndf'. Use WITH MOVE to identify a valid location for the file.
Server: Msg 3156, Level 16, State 1, Line 1
File 'NRCSPersonnelNdx' cannot be restored to 'E:\SQLdata\NRCSPersonnel\Indexes.ndf'. Use WITH MOVE to identify a valid location for the file.
Server: Msg 3156, Level 16, State 1, Line 1
File 'NRCSPersonnelText' cannot be restored to 'E:\SQLdata\NRCSPersonnel\Text.ndf'. Use WITH MOVE to identify a valid location for the file.
Server: Msg 3156, Level 16, State 1, Line 1
File 'NRCSPersonnelLog' cannot be restored to 'E:\SQLdata\NRCSPersonnel\TransLog.ldf'. Use WITH MOVE to identify a valid location for the file.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
My domain account has full permission to access this folder. I also tried the copy database wizard and got a login failed error. My account is also a member of the System Administrators role so I'm not sure what the problem could be. I would appreciate any advice. Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

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

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 1

Hello Chad,

Can you try by interchanging the MOVE statements. First, give the .mdf statement and then .ndf statements. I think that it is trying to find the Primary filegroup and then add a pointer to it in order to reference the secondary filegroups.

This is my assumption only. The overall restore statement looks fine.

Hope this helps.

Thanks

Lucky

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Is there enough space on the drive?

Is the drive (or folder) read only or encrypted?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 3

I cannot see from your script that it is used for restoring a database from one server to another. Did you try to use mapping drives?

Chad Koenig SSC Enthusiast Points: 120 More actions · Answer 4

I had to put this task down for awhile, but now I'm back at it. I'm trying to execute the script in Query Analyzer while being connected to the destination server (the backup file resides on this server). Also space isn't an issue and these folders aren't encrypted or read only.