September 23, 2009 at 3:39 am
Good morning,
The problem I am having appears to be a problem others have encountered, and I have worked through their resolutions (i.e. here: http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/021c7aa5-4a8a-4bbb-8ff0-fe6b03920aae"> http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/021c7aa5-4a8a-4bbb-8ff0-fe6b03920aae ) except I'm finding that it only effecting our Production server and not a Development server where the plan works just fine.
To explain: We have a Windows 2008 server running a SQL2005 (SP2) server instance and I am attempting to backup our main database to a file server as it has more space. Because the maintenance plans in SSMS only show local drives to the server I am executing the following script in a 'Execute T-SQL Statement Task' within the plan:
DECLARE @BackupPath VARCHAR(100)
SET @BackupPath = '\\ServerName\DB_Backups\DBName\DBNameFull_' + CONVERT(nvarchar,GETDATE(),112) + '_' + LEFT(REPLACE(CONVERT(nvarchar,GETDATE(),108),':',''),4) + '.BAK'
BACKUP DATABASE [Nucleus] TO DISK = @BackupPath WITH NOFORMAT, NOINIT, NAME = N'DBName-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
(I have obviously replaced the servernames as I don't want to give any secure information out :))
The SQL Server Agent has now been changed to a domain account and this domain admin account has write/modify permissions on the destination server folder. However, the following error is still being raised when SQL agent is trying to execute this backup command:
" failed with the following error: "Cannot open backup device '\\\\ServerName\\DB_Backups\\DBName\\DBNameTrans_20090923_1000.TRN'. Operating system error 5(error not found).
I know that this error is essentially an 'access is denied' error for the SQL Server Agent service but
I am at a loss as to why this is happening now as this same maintenance plan works a dream on the development server to the SAME folder using the same Domain admin account! The SQL Server service was previously a local system account but has now been changed to the same domain admin account as the SQL agent service , however, it has not been reset; is this something to do with the error being raised?
If anyone has any ideas i'm all ears as at the moment I'm running out of ideas!
Thanks,
Dan
September 23, 2009 at 3:59 am
try making the destination a share and backing up to the share name.
---------------------------------------------------------------------
September 23, 2009 at 4:11 am
I'm sorry I forgot to add that. The destination folder IS a share and the share permissions on the folder have been set to 'Full control' and the SQL Service domain account that is running the SQL job is also included on the 'Security' tab for the folder...
September 23, 2009 at 4:12 am
the other option is to impersonate the domain account and check that you can actually read and write to the share on the other server.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 23, 2009 at 4:14 am
Yes, this is what myself and the network admin have already tried! We can navigate to the folder no problem and create a test text file in the folder as the domain account in question...I'm wondering if it is something else within the server or the plan itself? Thank you for the suggestion 🙂
September 23, 2009 at 4:19 am
Danb7183 (9/23/2009)
Yes, this is what myself and the network admin have already tried! We can navigate to the folder no problem and create a test text file in the folder as the domain account in question...I'm wondering if it is something else within the server or the plan itself? Thank you for the suggestion 🙂
When you impersonated, i was referring to running the command within a query or using xp_cmdshell to access the share. Using execute as login. is that what you did.
keep your existing query and path but do it using a restore headeronly or restoreverify if you can.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 23, 2009 at 4:32 am
Ah ok, I see what you mean now.
How am I to run the command within the query using the domain account? Also, using xp_cmdshell will I still be able to use the text manipulation that I previously had in the SQL query?
As i'm attempting backups is restore headonly just for restoring and not backing up?
Thanks again.
September 23, 2009 at 4:51 am
Danb7183 (9/23/2009)
Ah ok, I see what you mean now.How am I to run the command within the query using the domain account? Also, using xp_cmdshell will I still be able to use the text manipulation that I previously had in the SQL query?
As i'm attempting backups is restore headonly just for restoring and not backing up?
Thanks again.
execute as login = 'domain\login'
-- your query here
revert - reverts back to your login
You are testing to see if the login has access to the share going through a query under the context of that domain account. you could run your entire query in this way and see if it works
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 23, 2009 at 5:26 am
on the SQL Server can you either logon as that domain account and access the share?
Or you logon and try to map the network drive, using the domain account in question to connect.
EDIT: Acutally this is the same thing as Silverfox showed in the previous post, this is just on the Window's side.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
September 23, 2009 at 5:42 am
Thanks Silverfox.
I've attempted your suggestion but it still returning the same:
Msg 3201, Level 16, State 1, Procedure spTestingBackup, Line 11
Cannot open backup device '\\ServerName\DB_Backups\DBName\DBNameFull_20090923_1233.BAK'. Operating system error 53(error not found).
Msg 3013, Level 16, State 1, Procedure spTestingBackup, Line 11
BACKUP DATABASE is terminating abnormally.
My code is as follows:
ALTER PROCEDURE spTestingBackup
AS
EXECUTE AS LOGIN = 'ADMIN\$SQL'
DECLARE @BackupPath VARCHAR(100)
SET @BackupPath = '\\ServerName\DB_Backups\DBName\DBNameFull_' + CONVERT(nvarchar,GETDATE(),112) + '_' + LEFT(REPLACE(CONVERT(nvarchar,GETDATE(),108),':',''),4) + '.BAK'
BACKUP DATABASE [Nucleus] TO DISK = @BackupPath WITH NOFORMAT, NOINIT, NAME = N'DBName-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
(again I have substituted different server names/DBnames)
What I still don't understand is how exactly the same SQL on a different server is able to write backup files directly to the same shared folder I am attempting on Production....
September 23, 2009 at 5:58 am
Danb7183 (9/23/2009)
Thanks Silverfox.I've attempted your suggestion but it still returning the same:
Msg 3201, Level 16, State 1, Procedure spTestingBackup, Line 11
Cannot open backup device '\\ServerName\DB_Backups\DBName\DBNameFull_20090923_1233.BAK'. Operating system error 53(error not found).
Msg 3013, Level 16, State 1, Procedure spTestingBackup, Line 11
BACKUP DATABASE is terminating abnormally.
is that a typo 'cos thats a different error, 53 is 'network path not found'
---------------------------------------------------------------------
September 23, 2009 at 6:46 am
Ah, well spotted. It is. Its because I ran the procedure afterwards with the 'Servername/DBName' path (which clearly doesn't exist)
The error is in fact:
Msg 3201, Level 16, State 1, Procedure spTestingBackup, Line 11
Cannot open backup device ''. Operating system error 5(error not found).
Msg 3013, Level 16, State 1, Procedure spTestingBackup, Line 11
BACKUP DATABASE is terminating abnormally.
September 23, 2009 at 7:55 am
I'm still not sure how to resolve this error, other than copying the files to a local drive on the Production server and then copying these files to the fileserver (which I'd rather avoid as I should be able to backup directly to the fileserver)
Any ideas?
Thanks,
Dan
September 23, 2009 at 8:18 am
this phrase in your original post:
The SQL Server service was previously a local system account but has now been changed to the same domain admin account as the SQL agent service , however, it has not been reset; is this something to do with the error being raised?
does this mean you have changed the service account but not restarted SQL? If this is so restart SQL as it will not take effect till then.
ensure this is done via configuration manger as well.
---------------------------------------------------------------------
September 23, 2009 at 8:28 am
I have changed the SQL Server Agent account's login and restarted this service. I have changed the SQL Server Service but have not been able to restart this service yet, is the SQL Server Agent service dependent on the account that is used for the database engine service account? I thought that one could be changed mutually exclusively of the other? Is this not so?
Thanks!
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply