April 19, 2008 at 8:45 pm
Hi All,
All of our database servers are doing backups and saving the .bak files out
on a network share. With SQL 2005, how can I point to that share and restore
the database in SQL 2005 environment?
When I do 'restore database --> From Device --> File; when I click the 'ADD;
button, I only see my local drives on the computer. How can I map to my
network share and point to my restore file on the network?
I need to do a restore for a database with the latest backup which is there on a network drive.
--
April 19, 2008 at 9:19 pm
Browse there manually using Windows Explorer and then cut&paste the path and backup name into the bottom text field of the dialog box that's showing you your folders. You cannot map a network drive in your windows session and have SQL Server use it as its running under a service account. I haven't tried but you could set SQL Server to log on using a specific user account and have that account have a mapped drive persistently mapped? Using a full UNC of the form \\computername\share\folders\file.bak is always going to be more reliable than a mapped drive letter.
April 19, 2008 at 9:29 pm
thanks, but is this possible to do that, i dont know exactly where to put the copied pathname you are telling, in which dialog box, More over when i specify the network path name, its unable to access even if i am keeping the network drive on local system through map drive.
April 21, 2008 at 4:08 am
Remember the network share the backup is being restored from has to be accessible to the user that is running the SQL server service - it is not enough to log on to the server console yourself and find the file
James Horsley
Workflow Consulting Limited
April 24, 2008 at 7:16 am
When you do 'restore database --> From Device --> File; click the 'ADD' Button-->
Get the FULL UNC path as "\\ComputerName\Shared folder\Backup\abc.Bak" by mapping in the
explore window.
Copy the path & paste this path under add button-->File Name. It will work.
April 24, 2008 at 1:46 pm
i am not restoring it, i am trying to give the backup path, anyways thanks for your reply, let me know how can you add a drive
In my case, the drive is there in the local system, but the problem is when i am giving a backup, its only showing 2 drives out of 4 i have. its on a cluster server
April 24, 2008 at 2:22 pm
I think I see your issue, you have a local drive that you want to back up to, yet, your SQL Server database is on a Cluster that does not see that local drive (probably because it is connected to the San box).
This situation will require you to use UNC naming as there is no guarantee that the local server will be the active server in the cluster. This basically mean that you will have to backup over the network even though this is a local drive because SQL Server does not see the local drive since it is not part of the cluster.
The next issue you will face is backing up over the network. Not a very good way to accomplish backups as SQL server is not very tolerate for small network hiccups that always occur.
The best way to do this is to backup to the cluster and then utilize xp..cmd shell to move the file using UNC to the drive on the local machine to your target backup drive.
Remember, Clusters are very different from stand alone servers. The best way is to think of the cluster as it's own server as it is very independent of the nodes hosting it.
Hope this helps
Marvin Dillard
Senior Consultant
Claraview Inc
June 4, 2008 at 10:23 am
I have the similar Issue.
SQL 2005 Cluster with a san box attached. After the Cluster was configured we added a drive on the san ( G.) Now we want to create a maintenance plan so the back up copy to the G drive rather than the local D or E Drive. I can't see the G drive in the context of Management Studio when creating the maintenance plan.
I can only accomplish the task using a share. G=\\sql\backups
Is there a way to get the cluster to see the G drive as a local drive?
July 17, 2008 at 11:01 am
It may help to check your cluster configuration. Use the cluster admin to add the G:\ drive as a dependent SQL resource.
August 1, 2008 at 12:25 am
I am trying to restore a database on SQL Server 2005, using the backup placed on a different server ( in same domain). I have shared the folder and tried the same with sharing the complete drive too.
The account, I am using, has been granted full permisisons... but still when I type the complete path ( in file name test box), the message says..the database engine cannot find the specified path or the account does not have access to it. Should the server(with shared folder) have SQL Server installed on it..???
Also what should be the computer name here - "\\ComputerName\Shared folder\Backup\abc.Bak" I used the local computer name (in case of mapped drive) and the other server name in case of shared folder.
Pls advise.
Thanks !!!
August 1, 2008 at 12:45 am
ansz5 (8/1/2008)
I am trying to restore a database on SQL Server 2005, using the backup placed on a different server ( in same domain). I have shared the folder and tried the same with sharing the complete drive too.The account, I am using, has been granted full permisisons... but still when I type the complete path ( in file name test box), the message says..the database engine cannot find the specified path or the account does not have access to it. Should the server(with shared folder) have SQL Server installed on it..???
Also what should be the computer name here - "\\ComputerName\Shared folder\Backup\abc.Bak" I used the local computer name (in case of mapped drive) and the other server name in case of shared folder.
Pls advise.
Thanks !!!
Does the account running SQL Server have access to the share? If the account is not a domain account, then you cannot get access to the shared folder.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 6, 2008 at 11:53 pm
Try restoring the database using the following query:
restore database "dbname"
from disk = 'Servername\sharedlocation\db.bak'
servername - This is not the local server name, it is the servername where the backup files are located.
or else to do it through GUI:
Know the exact shared location, you can type the servername(\\servername) in the "run" and that would autoamtically display all the shared locations on the server to which your account has access. From there navigate to the correct location and use it in the restore path.
The other server need not have SQL installed on it. Its just storing the backup files.
August 7, 2008 at 12:09 am
Hi !
Thank you every one for all you replies and suggetsions,
The problem had been resolved , I could not post it here on the forum.
I used the command net use on command prompt to find out exact name (UNC) name of the shared drives and used this complete name in the File name text box of locate backup window , while restoring the backup.
It worked.
Thanks again !
Best Regards,
ansz5
August 8, 2008 at 11:15 am
I have similar issue as the last user. I try to restore a db from a db backup file on another server. I use the full UNC name as \\sourcecomputer\shre drive\testdb.bak in the file name. Got the same erorr. I wonder if someone can confirm if itworks for them. My account I use has full admin priv on both servers.
August 11, 2008 at 1:36 am
May I know , if you are using a shared folder or you have a network drive mapped on your server ?
If its a network drive mapped, then pls use 'net use' command on command prompt to fetch the complete name for the drive.
I faced the same problem but when I fetched the name this way, it worked.
Please refer to my previous reply for steps.
Hope it helps.
🙂
Rgds,
ansz5
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply