Restore database backup over the network

Ryan picture Ryan · Aug 19, 2008 · Viewed 77.1k times · Source

How do you restore a database backup using SQL Server 2005 over the network? I recall doing this before but there was something odd about the way you had to do it.

Answer

TPAKTOPA picture TPAKTOPA · Dec 9, 2013

You have few options to use a network file as a backup source

  1. Map network drive/path, hosting file, under SAME user as MS-SQL Server.
  2. Use xp_cmdshell extended stored procedure to map network drive from inside of MS SQL (such way, command shell will have the same privilegies as the user account running SSMS)
-- allow changes to advanced options 
EXEC sp_configure 'show advanced options', 1
GO
-- Update currently configured values for advanced options.
RECONFIGURE
GO
-- To enable xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
GO
-- Update currently configured values for advanced options.
RECONFIGURE
GO
EXEC xp_cmdshell 'NET USE Z: \\Srv\Path password1 /USER:Domain\UserName'

Afterwards drive Z: will be visible in Server Managment studio, or just

RESTORE DATABASE DataBaseNameHere FROM DISK = 'Z:\BackNameHere.BAK'
GO