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.
10 Answers
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.
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.
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.
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.
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
Related