sql2005 database restore from another sql2005 backup file error.
hi
i try to restore a bak file from another sql2005 server to my sql2005 server, but it show the error message as below :
TITLE: Microsoft SQL Server Management Studio Express
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
ADDITIONAL INFORMATION:
Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\backup.bak'. Operating system error 5(error not found).
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3201)
For help, click:http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=3201&LinkId=20476
BUTTONS:
OK
pls some one can help me ?
thanks
chaus
[1490 byte] By [
chaus] at [2007-12-23]
Error (5) is "access denied". I'm not sure why that mapping to text didn't work.
Check that the service account for the target server has access to the backup file.
For example, if you are running sql as LocalSystem, it may not be able to access files to which you have access.
Hope that helps.
I tried to repro the error, and on my system, the error 5 is mapped to text properly.
Are you aware of anything funky with your system installation?
On my system, the error looks like this:
Server: Msg 3201, Level 16, State 2, Line 1
Cannot open backup device '\\testmachine\s$\xferdb.bak'. Operating system error 5(Access is denied.).
Are you running sql express? Running a user instance? I'll have to try it there too.
Thanks.
thanks steve
yes i runing sql express.
i can restore from myself backup file,
but can not restore from other backup file.
I'm having a similar problem, I think. Have just installed Sql Express at home. Trying to restore a database into a new (non existent) database (from a bak file). The bak file contains a valid backup of a database on a Sql Server 2000 installation at work.
Error is :
System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\TCM2000_Data.MDF'. (Microsoft.SqlServer.Express.Smo)
Does Sql Express support restoring of Sql Server 2000 databases?
Tony
Yes, sql2005 supports restoring from sql2000 and sql7 backups.
The problem you are having is due to the sqlservr.exe process getting "access denied" on the backup file.
You need to ACL the file to allow access to the account running sql server.
Ok ... restored happily now. I'm afraid I didn't properly understand the advice given in previous responses, but I think I do now ... some facts which may help other wannabe database dudes ...
1. ACL was fine in the sense that everybody on my machine should have had full access to the file I was restoring from. ie individual accounts plus Everyone all had all rights.
2. I had just done a fresh install of Sql Express, accepting all defaults (as far as I remember).
3. After your advice and a re-read of other comments, I checked details of the MSSQL$SQLEXPRESS service. The logon tab had "This account" option selected, with this user specified:
NT AUTHORITY\NetworkService
Who is this guy and why is he running my service?
3. When I changed to Local System account and restarted the service, my restore worked.
Thanks for your help
Tony
Hi Folks,
Im a timepass SQL Server developer.
One way to ged rid of the below error is to change the account from Network Service to Local Account System.....
"Msg 3201, Level 16, State 1, Server , Line 1 Cannot open backup device 'c:\backup\master.bak'. Operating system error 5(Access is denied.). Msg 3013, Level 16, State 1, Server , Line 1 BACKUP DATABASE is terminating abnormally."
But i just wanna know if there is any other way to get rid of the error mentioned above without changing the account to Local?
Expecting a quick response.....
Thanks in Advance,
Shaun
Sorry i forgot to mention that i was talking about Backup of SQL Server Express 2005 database with
OSQL in Command Prompt.
beware, running as the local system account should be less secure than NetworkService!
If you logon into sqlserver with proper privilege, you will be backing up your database in the context of the service account that is used for sqlserver service. By default, sqlexpress uses NetworkService as the startup account. So, you will need to grant NetworkService read/write/list/delete permission to the backup folder.
oj at 2007-8-30 >
If a have tapebackup and running the sqlserver service with network service, how i can do for give privilege to the tape?
A couple of newbie questions to this:
1. If I am having the package run under a domain account "OurDomain\MyAcct", and that account has full permission to the network share, why do I still get an "Access Denied" message?
2. I tried to add "NT Authority \ Network Service" to the users in the Security tab for the network share, but was told that user does not exist on our domain... How do I "grant NetworkService read/write/list/delete permission"?
Thanks in advance!
Jeff
Just wanted to say that I had the same problem - I could not restore from a .bak file.
error 5(error not found).
RESTORE HEADERONLY
Using SQL Configuration Manager I changed my Logon to 'Local System' and this worked a treat.
So take heart - it does work!!!!
My steps in detail.
1. open up SQL Server Configuration Manager (I am using SQL Server 2005)
2.right click on SQL Sever Express (I assume it is the same for the full version of SQL Server)
3.Choose Properties
4. in the Logon tab - click the built in account radio button and choose 'Local system' from the drop down.
This worked for me
good luck all
Cheers Hugh - works a treat.
Jan.
