When i am trying to take backup on network devices of sql,then below error is coming
Msg 3201, Level 16, State 1, Line 3
Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\Backup\192.168.1.117\d$\BackupFull_MedicalStore1_Backup_2021_06_08_T_12_25_22.bak'. Operating system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 3
BACKUP DATABASE is terminating abnormally.
Below is my procedure
alter procedure sp_generate_full_backup
as
begin
DECLARE @Date VARCHAR(30)
DECLARE @FileName VARCHAr(max)
DECLARE @DBName VARCHAR(150)
DECLARE @BkpPath VARCHAR(max)
DECLARE @backupCommmand nvarchar(max)
declare @DBcount int
declare @i int = 0
create table #UserDatabases(Name varchar(500))
insert into #UserDatabases select name from sys.databases where database_id>4
set @DBcount=(select count(1) from #UserDatabases)
While (@DBcount>@i)
Begin
set @DBName = (select top 1 name from #UserDatabases)
set @Date = replace(Convert(VARCHAR(10),Getdate(),23),'-','_') + '_T_' + replace(Convert(VARCHAR(10),Getdate(),108),':','_')
set @FileName = 'Full_' + @DBName + '_' + 'Backup' + '_' +@Date +'.bak'
set @BkpPath = '\\192.168.1.117\d$\Backup'
set @FileName = @BkpPath + @FileName
set @backupCommmand='Backup database [' +@DBName +'] to Disk= ''' +@FileName +''' WITH NOFORMAT, NOINIT ,SKIP, NOREWIND, NOUNLOAD, STATS = 10'
--Print @backupCommmand
EXEC sys.sp_executesql @backupCommmand
delete from #UserDatabases where name=@DBName
Set @i=@i+1
end
end
