February 19, 2008 at 5:04 pm
I have a backup copy of a SQL200 database on a network drive. I am unable to restore it on a newly created SQL2005 database as I don't see any option to go to a network drive in the "LOCATE BACK UP FILE" screen, using the EM Studio Restore option in SQL2005.
After reading some posts here I tried the following using the Query Analyzer:
RESTORE DATABASE [MY DATABASE NAME(SQL 2005)]
FROM DISK = '\\SERVERNAME\SHARED FOLDER\MSS\DATA\db_200706040056.BAK'
WITH MOVE 'LOGICAL NAME OF NEW DB' TO 'LOCAL DRIVE:\MSSQL\Data\filename.mdf',
MOVE 'LOGICAL NAME OF NEW DB LOG' TO 'LOCAL DRIVE:\MSSQL\Data\filename_log.ldf',
REPLACEgo
I get the following error:
Msg 3201, Level 16, State 2, Line 3
Cannot open backup device '\\\\SERVERNAME\SHARED FOLDER\MSS\DATA\db_200706040056.BAK'.
Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.
I do have access to the shared folder as I can read and write to it and am logged in to the local machine as the administrator with full privileges. I also copied a small txt file to the network location to make sure I can read and write to that location.
ANY HELP USING THE RESTORE COMMAND FOR SQL 2005 PLEASE!!
Thanks
February 19, 2008 at 6:03 pm
It won't matter who you are logged in as - when SQL Server runs a backup/restore command, it uses the credentials of the account the SQL Server service is started with. Make sure said account has read rights to the folder in question. If you are using localsystem or localservice, you will need to grant read access to Everyone. If you are using network service, you can restrict to to the machine account rather than everyone.
Tidy up the permissions after you have finished as well.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
February 19, 2008 at 7:34 pm
Thanks but it did not work. I logged in as myself and I see the service was started by me (left hand corner of object explorer shows logged in as me).
I think what you mean by tidy up permissions, is make sure I have grants to altering databases, altering resources etc., for server properties. I made sure my login (windows authentication) has all the permissions too.
Any other insight.
February 19, 2008 at 7:55 pm
I already assumed you had rights within SQL Server to do the restore.
No, I mean open Computer Management on the SQL server you are trying to restore to, navigate to Services, find SQL Server & check what the "Log On As" account is set to (or use SQL Server Configuration Manager, it's all good). If that account doesn't have at least read rights to the network share the backup file is sitting on (check share AND NTFS permissions), then you'll be SOL.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
February 19, 2008 at 8:59 pm
The services are all set to "Log On As" - 'Local System Account". The NTFS permissions look good too.
Any other insights.
Thanks
February 19, 2008 at 10:38 pm
Local System is an account that has administrator rights on the local machine and NO rights to any other machine.
If you want SQL to be able to backup and restore from a network drive, the SQL must be started with a domain account that has rights on the remote machine.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 20, 2008 at 10:53 am
I am logged in as a domain account user i.e., domainname\user on the Sql Server machine. I then use my windows authentication to login to sql query analyzer and then run the RESTORE command. So I am not sure how else to login as their is only one way to login to the server.
February 20, 2008 at 1:15 pm
As has been said, it doesn't matter who YOU log in as, it matters who the SQL Server SERVICE starts up with.
Since it is starting up with the LocalSystem account, you have two options:
1. Grant the Everyone group READ access to the shared folder AND in the NTFS ACL. This is not especially secure, so if it is not a regular occurrence, revoke those permissions when you are done.
2. Change the login that the SQL Server service is using (i.e. a domain user, dedicated service account. Not an account local to the SQL Server. Not your domain user account). Do NOT just go into computer management and do it. Use SQL Server Configuration Manager. Read BOL for implications/advice. Then make sure the account you have used has READ access to the shared folder AND in the NTFS ACL.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
February 21, 2008 at 2:25 am
Try Give privileges to Net Work Service Account on that Folder From Where You are location Backup and On that Folder where you are moving Db Files
Hope this will help 🙂
August 2, 2010 at 7:24 pm
Hi
I got the similar problem yesterday and checking the solution here and there. Below is the steps I followed to restore the database
1. Map the Drive Letter to this location from where you are going to restore the file
2. Stop MSSQL service from Services on relevant server
3. Run command prompt under your account by right clicking on the Command Prompt and select user name and password to this domain
4. Run MSSQL in command prompt mode as follow - this line is from SQL Server(MSSQLSERVER) PROPERTIES --> path to executable
"C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Binn\sqlservr.exe" -sMSSQLSERVER
5. Once above service is running - Restart Ent. Manager and do the restore - you can give the Mapped Drive letter for path and give the name for file.
6. Verify the .mdf and .ldf file location for this database from Options and click OK - data base will be restored.
Many Thanks
Deepak
August 2, 2010 at 11:18 pm
Please note: 2 year old thread.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply