sql2005 database restore from another sql2005 backup file error.

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

thanks steve 

 

yes i runing sql express.

i can restore from myself backup file,

but can not restore from other backup file.

 

Answer 4

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

Answer 5

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.

Answer 6

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

 

Answer 7

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

Answer 8

Sorry i forgot to mention that i was talking about Backup of SQL Server Express 2005 database with OSQL in Command Prompt.

Answer 9

beware, running as the local system account should be less secure than NetworkService!

Answer 10

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.

 

 

Answer 11

If a have tapebackup and running the sqlserver service with network service, how i can do for give privilege to the tape?

Answer 12

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

Answer 13

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

 

Answer 14

Cheers Hugh - works a treat.

Jan.

Answer 15

what is ACL?

Answer 16

I had the same error and it had to do with the rights to the directory I was trying to restore the mdf & ldf files to.  I change the location to a directory I created ("c:\databases") and it reloaded fine.

Answer 17

zquad wrote:

what is ACL?

 

Windows ACL(Access Control List) permissions to files and folders remember to leave the everybody group alone because any application process that needs to access a file or folder is also a member of the everybody group.

 

Answer 18

I got the error: the opertaing system returned the error '32(error not found) while attempting 'restorecontainer...

while attempting to restore a 2005 backup from another server.

I resolved it by not restoring over the origional files, but putting them in another directory. It worked fine then, however the permissions on the original folder are fine.

 

 

Answer 19

Another reason you might get error 5 is if it tries to restore to a folder that doesn't exist on the system. Funny thing is though that on SQL 2005, it gives the following error: "The operating system returned the error '5(error not found)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\<database>.mdf'", even though it may be fx. "E:\databases\<database>.mdf" that can't be created. Took me a good 5 minutes to figure that one out!

Answer 20

Very useful.  I had the same error message when trying to backup on SQL Express 2005.  I changed my log on to Local System under the SQL configuration manager and my backup was then successful.  Thanks

 

Answer 21

I had a similar problem but that was because the .bak file was not properly copied. The error was resolved by copying over the file again.

Answer 22

This did it for me - bit of clarification for newbs like me - I kept trying to figure out how to do this in SQL managment studio - when it says Sql configuration manager... took me a minute to figure that out. They are 2 different programs.

Answer 23

chaus wrote:

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

Answer 24

 

Error "5" means "Access is denied". You need to check to make sure the service account (the account used to start sqlserver (sqlexpress in this case) has permission to the backup file.

 

 

Answer 25

That is not correct this is something I found out the hardway another poster speCtacular have covered the reason SQL Server cannot find the drive the backup was created because there are no Backup devices in SQL Server 2005. I was the Admin in my laptop I could not restore a .bak sent to me by email which was created in drive E there was no drive E in my box so I created four sets of code th do the restore. This link covers how to creat the devices.

 

http://wvmitchell.blogspot.com/search?q=backup+restore

 

Answer 26

Caddre wrote:

That is not correct this is something I found out the hardway another poster speCtacular have covered the reason SQL Server cannot find the drive the backup was created because there are no Backup devices in SQL Server 2005. I was the Admin in my laptop I could not restore a .bak sent to me by email which was created in drive E there was no drive E in my box so I created four sets of code th do the restore. This link covers how to creat the devices.   http://wvmitchell.blogspot.com/search?q=backup+restore

 

System error "5" means "Access is denied" whether or not it's correctly raised here by Sqlserver - that's another problem on its own.

 

As to whether or not you can restore a backup file, the following must be met:

1. you (and the service account) have full access to the backup file

2. you're restoring to the same or higher sql version than the backup file

3. you have full access to the destination folder where the database files are going to be created/replaced

4. you have to specify the correct destination for the database files if the target server does not contain the same path as the source server for the database

 

Either way, you should run "restore filelistonly" to verify if the target paths for the backup file pointing to a valid path on the target server.  Take a look at the "replace" option of the "restore database" in BOL for additional info.

 

 

 

 

 

Answer 27

(Either way, you should run "restore filelistonly" to verify if the target paths for the backup file pointing to a valid path on the target server.  Take a look at the "replace" option of the "restore database" in BOL for additional info.)

 

 

 

All this is nonsense because I used all the different restore info in the BOL SQL Server 2005 will not restore that database without Devices.  It took me a long time to fix this problem so please don't copy BOL text for me and everything you have said is not relevant the only post relevant is the poster in my previous post.

 

Answer 28

Hmmm...nonsense?!  Take a close look at the repro below for restoring a backup to a server that does not have the same backup device or storage (for that matter).

 

====source server===

select @@servername [servername]
go
exec xp_fixeddrives
go

create database caddre
on(name=cad,filename='s:\cad.mdf')
log on(name=cadlog,filename='s:\cad.ldf')
go

exec sp_addumpdevice 'disk','cad','s:\cad.bak'
go
backup database caddre to cad with init
go
exec sp_dropdevice 'cad'
go

drop database caddre
go

 

servername                                                                                                                      
---------------------------------------------
snr3vsql1\YUKON64_2

(1 row(s) affected)

 

drive MB free    
----- -----------
C     25768
S     705324
X     354796

(3 row(s) affected)

 

Processed 152 pages for database 'caddre', file 'cad' on file 1.
Processed 2 pages for database 'caddre', file 'cadlog' on file 1.
BACKUP DATABASE successfully processed 154 pages in 0.186 seconds (6.744 MB/sec).
Device dropped.

=====target server======

 

select @@servername [servername]
go
exec xp_fixeddrives
go
restore filelistonly from disk='c:\cad.bak'
go
restore database cad
from disk='c:\cad.bak'
with replace,
move 'cad' to 'c:\cad.mdf',
move 'cadlog' to 'c:\cad.ldf'
go
drop database cad
go

 

servername                                                                                                                      
-------------------------------------------------------------------
OJCLIENT1\YUKON32

(1 row(s) affected)

drive MB free    
----- -----------
C     181354
D     185691

(2 row(s) affected)

 

LogicalName                                                                                                                      PhysicalName                                                                                                                                                                                                                                                     Type FileGroupName                                                                                                                    Size                 MaxSize              FileId               CreateLSN                   DropLSN                     UniqueId                             ReadOnlyLSN                 ReadWriteLSN                BackupSizeInBytes    SourceBlockSize FileGroupId LogGroupGUID                         DifferentialBaseLSN         DifferentialBaseGUID                 IsReadOnly IsPresent
-------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---- -------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------------- -------------------- --------------------------- --------------------------- ------------------------------------ --------------------------- --------------------------- -------------------- --------------- ----------- ------------------------------------ --------------------------- ------------------------------------ ---------- ---------
cad                                                                                                                              s:\cad.mdf                                                                                                                                                                                                                                                       D    PRIMARY                                                                                                                          1245184              35184372080640       1                    0                           0                           B81E6AC5-4444-4275-84E6-94CB337C23B2 0                           0                           1245184              512             1           NULL                                 0                           00000000-0000-0000-0000-000000000000 0          1
cadlog                                                                                                                           s:\cad.ldf                                                                                                                                                                                                                                                       L    NULL                                                                                                                             1048576              2199023255552        2                    0                           0                           78150B64-4474-4CC3-B98C-97E6235A6C02 0                           0                           0                    512             0           NULL                                 0                           00000000-0000-0000-0000-000000000000 0          1

(2 row(s) affected)

 

Processed 152 pages for database 'cad', file 'cad' on file 1.
Processed 2 pages for database 'cad', file 'cadlog' on file 1.
RESTORE DATABASE successfully processed 154 pages in 0.041 seconds (30.595 MB/sec).

Answer 29

It is nonsense because databases are not created with Extended stored procedures and dumpdevices and since SQL Server 7.0 Backup devices are not needed to restore SQl Server .bak from anywhere in the World until SQL Server 2005 when the Master was changed.  

Answer 30

Caddre wrote:

It is nonsense because databases are not created with Extended stored procedures and dumpdevices and since SQL Server 7.0 Backup devices are not needed to restore SQl Server .bak from anywhere in the World until SQL Server 2005 when the Master was changed.

 

Talking of nonsense! What the heck are you talking about?!!

 

The OP is asking for help with restoring a sql2k5 database and encountering the error 5. Whether or not the error is accurately reported by Sqlserver, the OP still has to go through and make sure all permissions are aligned. You arrogantly accused me of posting nonsense and detoured it with something about backup device.

 

As shown in the earlier repro, you can easily restore a backup file that is created on a dump device even if there is NOT a backup device on the target server.  

 

Without being nasty, but please take a hold of yourself.

 

 

Answer 31

EDIT

 

(As shown in the earlier repro, you can easily restore a backup file that is created on a dump device even if there is NOT a backup device on the target server.)

 

So the question is when was the last time even DBA use Extended stored procedures and dump devices to create a database and backup?

 

go
exec xp_fixeddrives

Extended stored procedures these cannot be run in some company network my box was company issued.

go

exec sp_addumpdevice 'disk','cad','s:\cad.bak'

 

Developers and most DBAs knows these are not needed since SQL Server 7.0,when last I checked SQL Server was improving not going back.

go
backup database caddre to cad with init

 

Not needed in SQL Server 2000 why should I need it in SQL Server 2005

Answer 32

 

OMG, someone must have gone over the deep end!

 

exec xp_fixeddrives

 

This xp is being demo'd here to show you the current server's resource - i.e. to stress the point that the backup device is created on a volume/path of the source server that does not exist on the target server. Who said you must run these in your backup routine?!

 

exec sp_addumpdevice 'disk','cad','s:\cad.bak'

go
backup database caddre to cad with init

 

Again, this is simply to demonstrate that you can create a backup device on any volume and backup your database to a backup device. You need it or not is completely irrelevant.

 

Let us take a look at your previous statement:

 

Caddre wrote:

That is not correct this is something I found out the hardway another poster speCtacular have covered the reason SQL Server cannot find the drive the backup was created because there are no Backup devices in SQL Server 2005. I was the Admin in my laptop I could not restore a .bak sent to me by email which was created in drive E there was no drive E in my box so I created four sets of code th do the restore. This link covers how to creat the devices.   http://wvmitchell.blogspot.com/search?q=backup+restore

 

Isn't obvious that you can still use backup device on sql2k5?! To say there is no backup device on sql2k5 is complete wrong.

 

Isn't obvious that you can restore a backup file that is from a source database with a data path that is non-existent on the target server? And the backup file can also be created through a backup device.

 

Simply put, the data path of the source database matters little on the target if you have proper permission on the target and you know how to redirect the restore to a valid path on the target.

 

So, please answer the following:

 

1. What is the meaning of system error "5"?

 

2. Can you restore a database if you (and the service account) do not have full access to the backup file?

 

3. Can you restore a database from a higher version (e.g. 9.0) to a lower version (e.g. 8.0) sqlserver?

 

4. Can you restore a database to a target folder if you do not have full access to the destination folder where the database files are going to be created/replaced?

 

5. Can you restore a database if you do not specify the correct destination for the database files when the source database data path is different than the target destination?

 

In any of the above cases, what is the error message/number?

 

 

 

 

 

 

Answer 33

Thanks a lot, Hugh Abbott.

It will be better only if you could tell us what is the diference between those modes.

 

Ricardo Ildefonso

Londrina - Brazil.

Answer 34

Amazing worked for me . Thank you.

 

 

 

Answer 35

 

Thank you very much for this posting..I was experiencing the same issue and changing the logon identity to Local System account resolved the issue...

 

regards

 

Ike

Answer 36

Thanks for this useful post. I had the same problem and applied this solution. It worked for me. thanks a lot

Answer 37

That works, but more secure way would be to grant access to the backup file for the SQLSERVER user. Right click on the file, Properties, Security, Add and add the user that is used to login into SQL Server.

Answer 38

Thanx a lot works for me.

Answer 39

Hugh Abbott wrote:

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

 

That solved the same problem on Vista x64.  Could have been 3 things, but this was the first, that worked.