Restoring a network back-up failing
-
I've already posted this in SQL 2008 General but didn't gut much of a response
Hello
I'm using SQL server 2008 R2 and have set-up my maintenance plans which appear to be running fine performing a full back-up overnight to a network locatrion (using UNC path) with 2 hourly transaction log back-ups to the same network location, I've checked that the back-up files are being generated on the correct network location and that e-mail notifications are working everything seemed fine, I've come to perform a restore and this is where I've hit my problem.
Using the restore wizard I select the database I want to restore then ok to perform the restore I get the following message.
"Restore failed for server 'InstanceName' (Microsoft.SqlServer.SmoExtended)
Additional Information:
System.Data.SqlClient.SqlError: Cannot open backup device
'{FBF49089-7555-9AFD-FC0E1B820512}-10'. Operating system error 2 (Failed to retrieve text for this error. Reason 15105). (Microsoft.SqlServer.Smo)"
Which suggests the restore action can't see the network location I've tried to restore from a previous day with the same problem but I noticed that the Full database backup is using 'User Name' 'NT Authority\System' which explains why it's having problems accessing a network resource, I've checked that the jobs are owned by accccounts that have access to the network resource and also that the SQLServer and SQLServer Agent services are running as domain accounts with access to the network resource which they all are.
I can not tell why the Full Backup is showing the user NTAuthority/System, I've copied a backup file locally and restored from the file fine so there is no issue with the backups themselves.
Any assistance greatfully recieved.
-
Maintenance Plan is executed under SQL agent account and some of the tasks can be EXECUTED AS plan owner's account (http://sqlblog.com/blogs/tibor_karaszi/archive/2009/09/19/sql-server-agent-jobs-and-user-contexts.aspx)
When you are trying to restore the database (either SSMS or T-SQL), SQL server's service account needs file system permissions. If sql server is running under system account, you can grant "DOMAIN\MachineName$" account access to UNC folder
-
Hi
the SQL server agent is using a domain account which has rights to the relevant network area I've confirmed access by mapping a drive from the server to this area using the service account and it is working fine, I'm reasonably certain this is working OK as the transaction logs are ok.
I've attached an image to show what I'm seeing when trying to do a restore, the transaction logs and database backup are created from the same maintenance plan (so presumably using the SQL Agent service login) yet you can see the username is different on the database backup to the account used on the transaction log backup also note that the database backup doesn't have a name
-
Can you run this and check the location of T-log and Full backup?
SELECT a.name,a.database_name,a.user_name,a.backup_start_date,a.backup_finish_date,a.type,cast((a.backup_size/(1024*1024)) as numeric(10,2)) backup_size,
cast((a.backup_size/(1024*1024)) as numeric(10,2))/
case when datediff(ss,a.backup_start_date,a.backup_finish_date)=0 then 1 else datediff(ss,a.backup_start_date,a.backup_finish_date) end [MB/Sec],
b.physical_device_name,b.device_type
FROM msdb.dbo.backupset a
join msdb.dbo.backupmediafamily b on a.media_set_id=b.media_set_id
ORDER BY a.backup_start_date desc
Blank name for full backup, when take a backup using t-sql and donot specify backup name, this information will be blank (check the above sql)
In the screen what you are observing is user who invokes the job. If scheduled job than Agent service account and if a user than you will see user account.
I am not sure in maintenance plan tasks runs under what credentials (service account (sql or agent), plan owner), but when a user runs a backup using t-sql, SQL server service account must have permission to file system it doesn't matter that user has permission or not. User just need permission to perform a backup (i.e. Server Role : sysadmin,dbcreator, DB role : db_backupoperator, dbo_owner)
Same permission is required to Sql service account when you are trying to resotre the a database from file system.
If you are using mapped drive then, keep in mind that after Win 2003 onwards mapped drived are isolated to a user session. i.e. if a user creates a mapped drive will not be visible to service account.
So in my mind, either you need to change sql service account to a domain account which has necessary permissions to the file system and mapped drive OR give machine account permission to file system and create a mapped drive in system account's session
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply