52

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.

Improve this question

10 Answers 10

Reset to default
96

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
Improve this answer
6
  • 2
    Calling NET USE with SQL Server current service user solves the problem. This answer is really useful.
    – JB.
    Commented Jun 17, 2014 at 9:59
  • 1
    This is perfect. It was the solution to our problem: full MSSQL access, but no file server access. Needed a copy. How? This is the solution.
    – David Betz
    Commented Aug 10, 2015 at 15:08
  • 1
    Will you need to run somehing like EXEC sp_configure 'xp_cmdshell', 0 afterwards to disable xp_cmdshell? (for security reason or whatnot).
    – Jim Aho
    Commented Mar 14, 2017 at 12:10
  • Pretty cool to spawn a windows command shell inside management studio. I think it's worth to mention that the command shell will have the same privilegies as the user account running SSMS.
    – Jim Aho
    Commented Mar 15, 2017 at 8:54
  • 1
    @JimAho I think, command shell started from MS SQL Server is a big security issue. But they implemented it, to cover lack of functionality or other architecture problems. Other SQL servers don't need to have admin access or to spawn command shell, to do usual tasks, like restore a backup...
    – TPAKTOPA
    Commented Mar 17, 2017 at 11:49
23

The database is often running as a service under an account with no network access. If this is the case, then you wouldn't be able to restore directly over the network. Either the backup needs to be copied to the local machine or the database service needs to run as a user with the proper network access.

Improve this answer
2
10

You cannot do this through the SSMS GUI, but you can do it using a script. RESTORE DATABASE from DISK='\unc\path\filename' If you need this process automated, the best way is to setup a SQL Server Job and run it as a user with access to the file location.

Improve this answer
2
  • 2
    This is not true. You can restore it over the network as long as you have mapped the network drive prior to opening the "Restore database..." dialog.
    – Jim Aho
    Commented Mar 15, 2017 at 8:56
  • 1
    This was not the case with SQL 2005 or SQL 2008 tools
    – baldy
    Commented Mar 20, 2017 at 8:22
8

You can use the SP xp_cmdshell to map the networkdrive for sql server, after that it will show up in the file browsing window.

EXEC xp_cmdshell 'NET USE Z: SERVERLOCATION PASSWORD /USER:DOMAIN\USERNAME'

more info here: DB Restore from Network Drive

Worked for me!

Improve this answer
7

Make sure that the user running your SQL services in "Services.msc" is an active directory "Domain User" this will fix the issue.

Improve this answer
0

I've had to do this a few times, and there are only two options that I know of. Copy the file locally to the SQL Server, or on the SQL server create a mapped network drive to the share that contains the backup file.

Improve this answer
0

Also, you need to make sure that the SQL Server Service is running as a user that has network access - and permissions to the share where the backup file lives. 'Local System' won't have permissions to access the network.

Improve this answer
0

As a side note, if you happen to be running SQL on a Virtual Machine it's often less hassle to just temporarily set up a new drive on the VM with enough space to copy your backup files to, do the restore from that new local copy, and then delete the temp drive.

This can be useful if stopping/starting the SQL service to change it's account is an issue.

Improve this answer
0

Create a shared drive on machine that has the backups, say server1 has the backups in folder "Backups". Grant full control to the account running the SQL Server. On the Server that you want to restore to launch SSMS go restore database and select "From Device". In the "Locate Backup file-"Server"" dialog box and remove anything in the "Selected Path" field and in the "File Name" field supply full path so "\server\backups\db.bak". At least it worked for me when migrating from 05 to 08. Not the preferred method because any network hiccup can cause an issue with the restore.

Improve this answer
0 
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

--This should be run on command prompt (cmd)

NET USE Z: \\172.100.1.100\Shared Password /USER:administrator /Persistent:no

then on SQL Server

EXEC xp_cmdshell 'NET USE Z: \\172.100.1.100\Shared Password /USER:administrator /Persistent:no'

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

RESTORE DATABASE DB FROM DISK = 'Z:\DB.BAK'
WITH REPLACE
Improve this answer
1

Not the answer you're looking for? Browse other questions tagged or ask your own question.