Backup error on network drive
-
Hi,
I am trying to backup database(full) on a network drive.Network drive is accessible from windows explorer.I created one user,backup_user(with Administrator permission) in Windows Account.The same user I added in SQL server-Login and made him sysadmin.
I created Maintenance Plan,SQL Job.When I run the job, I get the following error:
Failed-1073548784) Executing the query "EXECUTE master.dbo.xp_create_subdir N'S:\\HR_Server\\Data\\hrdb'
The system cannot find the path specified.'". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Command:EXECUTE master.dbo.xp_create_subdir N''S:\HR_Server\Data\hrdb''
The above path S:\.. exist on network drive.Though error includes path with "S:\\..", in actual I have given "S:\..." While creating job, I checked "Create sub directory option" too.
Thanks
PD
-
My best guess is permissions. I believe the Maintenance plan job is running under the SQL Server Agent service account.
Another issue is using a mapped drive. I have never attempted this with sql server, but since the scheduled job is running under the SQL Server Agent Service account the mapped drive probably does not exist. Change to a UNC path (\\servername\sharename).
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
I'm not 100% sure of what is wrong with your setup, although I bet there are people on here who could tell you.
What we do is to create the folder on the network drive and share it with everyone having full permission . We also have the SQL Server Agent running as a domain user (so we could probably restrict the folder permissions to this person, but don't).
This appears to work for us.
Cheers,
Phil
Regards,
Phil
-
Philip Barry (5/21/2008)
I'm not 100% sure of what is wrong with your setup, although I bet there are people on here who could tell you.What we do is to create the folder on the network drive and share it with everyone having full permission . We also have the SQL Server Agent running as a domain user (so we could probably restrict the folder permissions to this person, but don't).
This appears to work for us.
Cheers,
Phil
Phil,
Are you using mapped drives or UNC?
You should probably restrict the permissions as your current setup would allow for anyone who gets on your network the ability to steal any data in your enterprise.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Jack,
We use UNC paths.
In all reality the shares use a $ extension so they are not visible, so the person would have to know the path. Also we only backup external to the server usually ready for a restore to a testing environment and not as an everyday occurrence.
Phil
Regards,
Phil
-
Mapped drives will never work. UNC paths are the only way to do it. I have used a remote server exclusively for my backups without any issues. There are plenty of hits on this site both for and against remote backups and alternatives to using remote servers. I've never had a problem and do regularly restore them to a test environment to verify their integrity. I know MS does not recommend it but what works best for you is the appropriate approach. As Phillip stated, use $ so it's hidden and DO NOT give everyone rights to the share as Jack stated. Restrict it to the account running the SQL Agent and your admins (full rights if you're also automating the cleanup of the files). Your backup files, your job. If someone has deleted them and you need them, how do you explain that to the boss?
-- You can't be late until you show up.
-
Use Net share within SQL server
Create a share on network file system you wish to write the backup to.
Set permission for required domain\user
Run the following as a query:
exec xp_cmdshell 'net use driveLetter: \\server\share passoword/user:domain\user'
Create a backup device under server objects\backup devices using a the path created above (eg: m:\backup_Network.bak)
Redo the maintenance plan ensuring that the backup is written to the backup device created above.
Insert a start step in the job of the maintenance plan and include the following:
exec xp_cmdshell 'net use driveLetter: \\server\share password /user:domain\user'
Ensure the logic then leads to the maintenance plan step, and add a final step to the job and include the following:
exec xp_cmdshell 'net use driveletter: /delete'
-
I agree with Jack. You need to give permissions to the accounts that starts the SQL services. I am not sure, but I think that you have to give permissions to the account the SQL service and to the one that starts the SQL agent.
Please let us know if it helped.
Good luck!
-
I almost forgot. Remember to use an UNC path!
-
We had a related issue happen. We have a new network share to backup sql files to. We are using the destination format \\10.1.20.50\sqlbackups\Production\foldername. We can get all the backup scripts to backup to this folder using IP address, with the exception of one server instance. That is, we currently use one elevated login on all database instances and 6 backup but the 7th fails with the error.
Msg 22048, Level 16, State 1, Line 0
xp_create_subdir() returned error 161, 'The specified path is invalid.'
The server that fails is named crabnebula
If I RDP to crabnebula server and try to create folder using winsql sql serverr service account (which has same rights in sql as in other instances and is admin on this server as on the other servers)
EXECUTE master.dbo.xp_create_subdir '\\10.1.20.50\sqlbackups\NonProduction\CRABNEBULA\1'
the command fails
But I can logon to another server with the same login and same rights on sql and server, one of the 6 that work, and execute the command pointing to crabnebula's folder, and the folder is created: '\\10.1.20.50\sqlbackups\NonProduction\CRABNEBULA\1'
Any ideas???
-
tosscrosby (5/22/2008)
I know MS does not recommend it but what works best for you is the appropriate approach.Where did you read this out of interest?
-
Please post new questions in a new thread. Thank you.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Hi There,
I have a similar problem.
I am using SQL 2000 and I am backing up to a UNC path.
I am using this code below:
USE [master]DECLARE @DBNAME VARCHAR(20)
DECLARE @SQL VARCHAR(500)
DECLARE @Errors VARCHAR(300)
DECLARE @Message VARCHAR(100)
DECLARE @CommandLine VARCHAR(1000)
DECLARE @output VARCHAR(12)
DECLARE BackupCursor CURSOR FOR
SELECT[NAME]
FROMsysdatabases
WHERE[NAME] IN ('master',
'tempdb',
'model',
'msdb',
'pubs',
'Database01',
'Database02',
'Database03',
'Database04',
'Database05',
'Database06',
'Database07',
'Database08',
'Database09',
'Database10'
)
OPENBackupCursor
FETCH NEXT FROM BackupCursor INTO @DBNAME
WHILE@@FETCH_STATUS = 0
BEGIN
IF NOT EXISTS(SELECT [name] FROM sysdevices WHERE [name] = @DBNAME + ' Backup')
BEGIN
SET@SQL = 'EXEC sp_addumpdevice ''disk'', ''' + @DBNAME + ' Backup'', ''\\10.1.2.3\d$\dbbackups\dbserver2\' + @DBNAME + ' Backup.BAK'''
EXEC(@SQL)
END
ELSE
BEGIN
SELECT '''' + @DBNAME + ' Backup'' already exists on the server.'
END
SET@SQL = 'BACKUP DATABASE [' + @DBNAME + '] TO [' + @DBNAME + ' Backup] WITH INIT , NOUNLOAD , NAME = N''' + @DBNAME + ' backup'', NOSKIP , STATS = 10, NOFORMAT'
EXEC(@SQL)
FETCH NEXT FROM BackupCursor INTO @DBNAME
END
CLOSEBackupCursor
DEALLOCATE BackupCursor
The job fails but there is no error it just says:
Executed as user: Domain\dba. ...rcent backed up. [SQLSTATE 01000] (Message 3211) 30 percent backed up. [SQLSTATE 01000] (Message 3211) 40 percent backed up. [SQLSTATE 01000] (Message 3211) 50 percent backed up. [SQLSTATE 01000] (Message 3211) 60 percent backed up. [SQLSTATE 01000] (Message 3211) 70 percent backed up. [SQLSTATE 01000] (Message 3211) 80 percent backed up. [SQLSTATE 01000] (Message 3211) 90 percent backed up. [SQLSTATE 01000] (Message 3211) Processed 33944 pages for database 'Database01', file 'Database01_Data' on file 1. [SQLSTATE 01000] (Message 4035) 100 percent backed up. [SQLSTATE 01000] (Message 3211) Processed 1 pages for database ' Database01', file ' Database01_Log' on file 1. [SQLSTATE 01000] (Message 4035) BACKUP DATABASE successfully processed 33945 pages in 7.124 seconds (39.032 MB/sec). [SQLSTATE 01000] (Message 3014) 10 percent backed up. [SQLSTATE 01000] (Message 3211) 20 percent backed up. [SQLSTATE 01000] (Message 3211) 30 percent backed up. [SQLSTATE ... The step failed.
If I take the above code and execute it manually on the SQL 2000 instance it backs up all the databases successfully. All the security permissions are setup correctly.
Anyone have some advice on why it is failing in the job but if it get executed manually it completes successful and how to resolve the issue?
Thank you!
-
.
-
Please post new questions in a new thread. Thank you.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply