Restore across a network problems

Question

Restore across a network problems

Wedge78

Ten Centuries

Points: 1102
More actions
July 26, 2007 at 4:50 am

#118004

Hi
I work on 2 domains A and B. We use mixed mode for SQL Server authentication. I have a dev server on domain B but my backups reside on a NAS box on A.
I have mapped a drive to the NAS box N: from the DEV box. I then run the following:
RESTORE
FILELISTONLY FROM DISK = 'N:\Chris_test.bak'
I get the following error:
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'N:\Chris_test.bak'. Operating system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.

How do I access that file on A from B? Is it possible to restore accross domains with some king of connections string maybe?

thanks
Chris

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

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

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

Hi Chris,

generally I prefer to use UNC names when accessing network resources. I know that in SQL 2000 mapped drives often caused problems, not sure if 2005 reacts the same.

Other than that it sounds like a permission issue on the network share. Make sure the account you use to connect to SQL Server has read permission on the backup share.

Markus

[font="Verdana"]Markus Bohse[/font]

Wedge78 Ten Centuries Points: 1102 More actions · Answer 2

Im connecting to a server using a sql server account. Can this account be given access to a folder on another domain?

Anders Pedersen SSChampion Points: 11410 More actions · Answer 3

Are you running Windows 2003? If so the mapped drive will not work from within SQL. You will have to use a UNC name.

Wedge78 Ten Centuries Points: 1102 More actions · Answer 4

Half resolved

This works except that I cant get it to work using extended stored procedure of sqlbackup.

EXEC master.dbo.xp_cmdshell 'net use N: \\LON-NAS01\Backups\SQLFullBackups /USER:username password'

master..sqlbackup N'-SQL "RESTORE FILELISTONLY FROM DISK = ''n:\FULL_DEV_Chris_test_20070718_154741.sqb''"'

EXEC master.dbo.xp_cmdshell 'net use n: /del'

Anyone know how to get it to function across the network

Jpotucek SSCoach Points: 18583 More actions · Answer 5

How are you doing your Backups? are you backing up to a 2nd Server or Backing up locally and then copying the Backup Files?

To backup\restore across the Network, I use the UNC patch

\\DestinationServer\DestinationFolder\sqlbackups.bak

using a mapped drive won't work in SQL

Jason Hall Hall of Fame Points: 3906 More actions · Answer 6

The account that you connect to SQL Server with is irrelevant. The account that needs access to the network resource is the account that is running SQL Server.

You can check this account using either SQL Server Configuration Manager or by going to control panel -> administration tools -> services and finding the SQL Server service.

Let me know how this goes for you.

Steve Jones - SSC Editor SSC Guru Points: 736210 More actions · Answer 7

This is dicey as SQL Server is very intolerant of network delays. File copies are more tolerant. I'd recommend backup/restore from the local disk.

Anders Pedersen SSChampion Points: 11410 More actions · Answer 8

Mapped drives work in SQL, just doesn't work in Windows 2003.

File copy gets impractical though with large files... I've had more luck restoring directly from a remote drive than copying. Maybe I've just been lucky to work with good networks!