March 12, 2009 at 10:57 am
This is the TSQL script that I am trying to run using SQLCMD in a .bat file.
RESTORE DATABASE Test2
FROM DISK='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\materials.bak'
WITH MOVE 'Materials' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Test2_Data.mdf',
MOVE 'Materials_Log' TO'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Test2_log.ldf'
This is the .Bat file
sqlcmd -S .\SQLEXPRESS -E -i restoreScript.sql -b -o out.log
This is the error I receive
Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\materials.bak'. Operating system error 5(Access is denied.).
Can you do what I am trying to do? I am trying to make this simple for a developer to execute on there local machine.
March 12, 2009 at 11:53 am
Assuming that the permissions are set for whoever is running the bat file to access the .bak file and to restore the database then I would say that yes, it is fine. The error that you are getting it typical with either a wrong path, filename or permissions. So, I would validate that those are correct and then see if you can run it with your permissions.
HTH.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
March 12, 2009 at 12:04 pm
FYI ... The permissions SQL Server uses to restore are that of the service account the MSSQLServer service is running under.
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 12, 2009 at 12:10 pm
I figured it out. I am running multiple instances of SQL. It was a permission issue
March 12, 2009 at 12:21 pm
JKSQL - Glad you got it working.
Mohit - Can you explain what you mean by that?
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
March 12, 2009 at 12:50 pm
Mohit (3/12/2009)
FYI ... The permissions SQL Server uses to restore are that of the service account the MSSQLServer service is running under.Mohit.
Dat? I was trying to explain the permissions SQL Server uses when accessing files for backup/restore function. SQL Server will use the Service Account that is running the SQL Server (MSSQLServer) service.
You can find the Service account information from SQL Server Configuration Manager -> SQL Server 2005 Services -> SQL Server (MSSQLServer).
Check the Log On As column; what ever account is there it must have access to the path where backup is being written/read from :).
err yaa thats what I meant .. I was just being lazy me bad :Whistling:
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 12, 2009 at 12:56 pm
No problem. I think his situation is a bit different as the current user was going to be executing the bat file which is why I asked the question. Thanks!
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
March 12, 2009 at 1:12 pm
It was strange. I am currently running two versions of SQL on my local box. I am running a developer edition and that currently is set to run as local system. I basically had no issue restoring files in any directory and seeing the backup file anywhere on local disk. the .\express is running under nt autorityetworkservice. That is the one where I had to have the backup on the C:\ (root) or the MSQL.3 instance in order for the permissions to work. The way I figured this out was by trying to restore using the gui. I thought that was a bit strange till you went into detail about the service account. When I initiate the .bat I do not think it is restoring with my permissions since I am an admin on my box. the service account I think has something to do with my issue.
Either way now I know how to get around this if a developer has an issue.
Very educational thanks.
March 12, 2009 at 1:23 pm
He said that because of ...
Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\materials.bak'. Operating system error 5(Access is denied.).
And it makes sense in my little mind because if he has multiple instances installed on computer not every instance is going to have access to this folder. By default only the instances main group gets added to MSSQL.x\MSSQL\Backup folder.
If I look at my SQL Server 2005 install that has more then once instance on it... you can see the permissions on it..
I checked two folders:
MSSQL.1\MSSQL\Backup [Default Instance]
MSSQL.2\MSSQL\Backup [Named Instance]
Only SQLServer2005MSSQLUser$SQLServ$MSSQLServer has access to MSSQL.1\MSSQL\Backup
Only SQLServer2005MSSQLUser$SQLServ$MSSQLServer$NamedInstance has access to MSSQL.2\MSSQL\Backup
so thats why I posted that little quick sentence heh .. Sorry for confusion :crazy:.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply