How to restore on the network server using backup taken on another server

Question

Post reply

How to restore on the network server using backup taken on another server

Paresh MD

SSC Rookie

Points: 41
More actions
October 19, 2010 at 5:58 pm

#241887

Hi All,
I have following scenario. I am using SQL server 2005.
I am connecting from a client to server using management studio. I have taken the backup of a database on that server, lets call it a 'ServerA'. So, I have a backup file on 'ServerA'.
I want to connect to another server. Lets call it 'ServerB'. I connected to 'ServerB'. I want to restore the backup taken on 'ServerA' to the 'ServerB'.
I wrote following script.
RESTORE DATABASE testrestore
FROM DISK='\\ServerA\Backup\DBbackup.bak'
withMOVE 'DB_Data' TO N'\\ServerB\Files\Data_Primary.mdf',
MOVE 'DB_Log' TO N'\\ServerB\Files\Log.ldf',
norewind,
nounload,
replace
but it does not work. Any solution to this problem will be appreciated.
Thanks in advance.

Viewing 8 posts - 1 through 7 (of 7 total)

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

homebrew01 SSC Guru Points: 55549 More actions · Answer 1

homebrew01

SSC Guru

Points: 55549

October 19, 2010 at 6:08 pm

#1239427

What error do you get ?

Paresh MD SSC Rookie Points: 41 More actions · Answer 2

I am getting following error:

The file "\\ServerB\Files\Data_Primary.mdf" is on a network path that is not supported for database files.

'DB_Data' can not be restored to '\\ServerB\Files\Data_Primary.mdf'. Use WITH MOVE to identify a valid location for the file.

Is there other way of doing this thing?

au500 Newbie Points: 6 More actions · Answer 3

Replace these network path with local path like 'c:\data\serverB.mdf'

Paresh MD SSC Rookie Points: 41 More actions · Answer 4

Thanks, but that is not my requirement. My requirement is to restore on the network location.

Rob Schripsema SSCertifiable Points: 7469 More actions · Answer 5

Does that network location actually exist? (i.e., are the folders already present?) Can it be reached successfully from the machine on which SQL is running? Does the account you and/or SQL are running under have write permission on that network folder?

Edit: what am I thinking?!? You cannot restore a database to a network drive. From BOL:

We recommend that you use a Storage Area Network (SAN), iSCSI-based network, or locally attached disk for the storage of your SQL Server database files, because this configuration optimizes SQL Server performance and reliability. By default, using network database files (stored on a networked server or network-attached storage) is not enabled for SQL Server. However, you can create a database that has network-based database files by using trace flag 1807.

So, what you're wanting to do simply won't work (by default, anyway).

Rob Schripsema
Propack, Inc.

Jeffrey Williams SSC Guru Points: 90314 More actions · Answer 6

Are you restoring the database to an instance of SQL Server on 'ServerB'? Or, are you trying to locate the database on ServerB and have it available from the instance on ServerA?

If the latter - that is not recommended.

If the former, then you need to use the WITH MOVE option to identify the local path on ServerB where the data and log files will reside. You cannot use a UNC path in the restore command.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Paresh MD SSC Rookie Points: 41 More actions · Answer 7

Thanks a lot Mr. Williams and Mr. Schripsema,

I will get the local path on the server to restore the files and restore the database accordingly.

Once again thank you for your suggestions.