I am implementing Database backup technique. Due to budget constraints i will be using SQL express as the DB. So i would like to a take periodic DB backup(say once in a month).Since SQL jobs are not provided with express edition, i think i am left with 2 options 1. SMO 2. Stored proc Can you suggest me when should i use SMO and when should i use Stored proc. Do you suggest any other mechanism? P.S: i completely understand that there is a limited space in DB with express edition. Also it doesnt have the advanced performance improvement techniques!
As long as you're using some mechanism to issue the command BACKUP DATABASE, how it gets called doesn't really matter. You can create a stored procedure that runs the backup command. That will work just fine called from your code or from a scheduling application. You can use SMO through the Backup class. It's just a different way to issue the same command. The code is different, but the actions within SQL Server are the same. You can also use PowerShell to issue a backup to the database. They're all the same command. Just different resources. If you want to schedule and set it up for manual actions, I'd suggest creating a procedure within the database. This can then be called in different ways, but using the same code.