I have created a SP which is used for Backup/Restore. Now the problem is I need to create a table which is used for collecting the log information,
I.E When Procedure is executed, the execution of Procedure should be in the table which should contain following fields
Who(Which user used the Procedure), What they executed(backup/Restore) & Path (Where backup is taken or Restored etc).
Any thoughts on this ???
I'm probably not fully understanding what you're asking, but it sounds like you just want an insert into your logging table (create a table for this log info if you don't have it already). You probably already have a parameter on your proc whether it's for Backup or Restore. Is the path a parameter of the procedure as well? (probably). You can get the user from suser_sname().
INSERT dbo.YourLoggingTable (UserId, ExecutionType, Filename, ExecutionStart) VALUES (suser_sname(), @ExecType, @Filename, GETDATE());