SQL Server - Creating Folder and Backup Database in Network.

Asked By Madhu on 23-Mar-11 02:39 AM
Hi,
  I am trying to write a script in SQL Server 2008 Express Edition for creating a folder in a network drive and then Backup database in that folder. I have tried using the following


DECLARE @DBFolderName sysname
DECLARE @DirPath nvarchar(500)
DECLARE @DirTree TABLE (subdirectory nvarchar(255), depth INT)

-- 2 - Initialize variables
SET @DBFolderName = 'XXX'
SET @DirPath = N'\\ipl20\D\Abcd\' + @DBFolderName

-- 3 - @DataPath values
INSERT INTO @DirTree(subdirectory, depth)
EXEC master.sys.xp_dirtree @DirPath

-- 4 - Create the @DataPath directory
IF NOT EXISTS (SELECT 1 FROM @DirTree WHERE subdirectory = @DBFolderName)
EXEC master.dbo.xp_create_subdir @DirPath

BACKUP DATABASE XXX
TO DISK = @DirPath
WITH INIT,SKIP


While trying to create the above folder a following error appears

xp_create_subdir() returned error 161, 'The specified path is invalid.'

Please do suggest me how to do the same.


Anoop S replied to Madhu on 23-Mar-11 03:19 AM
Check whether 1st statment creating folder or not, if not try this script to create folder  using  SQL Server sys.xp_create_subdir extended stored procedure.
EXEC master.sys.xp_create_subdir 'C:\SQLDatabases\Test\'
Madhu replied to Anoop S on 23-Mar-11 06:01 AM

I am able to create a directory using the above command in a local drive but in network I am unable to do so. Returning the same result xp_create_subdir() returned error 161, 'The specified path is invalid.'


The Built-in-account is set to Network Services in SQL Server.