File copy approach security question

[bburnett71]

I need to copy some files as a part of a SQL backup job.
I've been looking all over for Microsoft's recommended approach and don't see an indication as to what is the safest:
1. enabling xmp_cmdshell
2. enabling Ole Automation Procedures
3. enabling clr

 

[TheBugSlayer]

What version of SQL Server are you using? In 2008, the FileSream datatype will allow you to save files (or they location rather) in the database; the files will be saved in the file system but you will be able to back them up through SQL Server.

Check the following link for more on the feature:

http://msdn.microsoft.com/en-us/library/cc949109.aspx.

Good luck.

 

[bburnett71]

Thanks for the tip, that won't work in this case. It's a 2005 server and I am wanting to make a copy of a backup in the filesystem after the backup completes.

 

[DrSql]

Its not a good practice to leave xp_cmdshell enabled. I would suggest add a step as pre and post copy to enable and disbale the xp_Cmdshell.
Good luck

Dr.Sql
Good Luck.