Restore Database From Network Drive
-
Hello All,
I have two servers "Server1" and "Server2" and both servers have SQL server 2000 and running on same domain user account. Now I would like to restore a databae on server1 where the back-up file is on server2 using below commands.
RESTORE DATABASE (New Database Name) FROM DISK = '\\Server2\D$\subfolder\PUBS.BAK' WITH
MOVE 'PUBS' TO 'D:\DATA\PUBS_DATA.MDF',
MOVE 'PUBS_LOG' TO 'D:\DATA\PUBS_LOG.LDF'
when I run the above script on Query Anazlyzer then the server is throughing an error that
Server: Msg 3201, Level 16, State 2, Line 1
Cannot open backup device '\\Server2\D$\VERIS\PUBS.BAK'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
If anyone of you come across this problem please forward the solution. I really appreciate your time and effort.
Email: bhushan_kalla@yahoo.com
Thanks,
Bhushan kalla
-
D$ is an admin only share so it won't work unles the user account in the Local Adminstrators group?
Better solution is to share out the subfolder on server2. Then your restore command would be,
RESTORE DATABASE (New Database Name) FROM DISK = '\\Server2\subfolder\PUBS.BAK' WITH MOVE 'PUBS' TO 'D:\DATA\PUBS_DATA.MDF', MOVE 'PUBS_LOG' TO 'D:\DATA\PUBS_LOG.LDF'
--------------------
Colt 45 - the original point and click interface
-
Hi Phil.
Thank you very much for your reply. My account user ID is a member of admin group but still it is not working. Probably I have to change SQL Server service account with my domain user account which has admin privileges. I read an article about this and it was mentioned that SQL SERVER SERVICE should run only domain user account to use network drives in restoration of database. However if you any find solutions about this please reply me. I appreciate your help and patience.
Thanks,
Bhushan Kalla
-
Yes you can have SQL Server running under a plain user account, but to get proper functionality (SQL Agent, SQL Mail, registry access, etc...) you need to assign a few user permissions. I find it easier to have the basic domain user account and then put that user in the Local Administrators group on the SQL Server machine.
As I mentioned in my earlier post, setup the folder on server2 as a share and then grant the user account that SQL Server is running under permissions to that share.
--------------------
Colt 45 - the original point and click interface
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply