I have a script that uses xp_cmdshell to copy over a database backup file. I'd like to be able to have a stored procedure (email notification of error) called if either "The network path was not found" or "the system cannot find the file specified'. These were both error messages returned by sql when testing around with bogus locations.
Here's the sql:
xp_cmdshell 'COPY \\server1\backup$\PA880PRD_Full_LiteSpeed.BAK\\server2\backup$\PA880PRD_Full_LiteSpeed.BAK'