KenpoDBA (1/5/2011)
I can't think of any real honest business case where taking backups outside of native SQL is necessary. I hate it when people choose to show the coolness of powershell by showing the most bloated method for backing up DBs on the planet. What DBA in their right mind would choose to take backups and put them in the windows scheduler? You don't get history and it's harder to tell when the job is running and get stats on it.If you have to run backups in powershell (again, WHY?), then at least call the script from an agent job so you can at least get some history and other job stats.
This is a case of using powershell just because you want to use it, and not because it's the best tool for the job.
Kenpo,
I agree, but I can present a case where it might make sense.
Assume for just a minute that you are a consultant DBA and the request is for you to ensure that a backup can be taken by any unskilled schmo (read as computer operator).
We could create an Agent Job that could be executed from SSMS by said operator, but let's say their skill set is not at a level where they can retain how this is completed. However, they are smart enough to double click on an icon on their desktop or the Server desktop that will complete the operation for them, passing all of the parameters, making sure that the previous backup is eliminated, someone is e-mailed that the backup is complete, and providing redundancy of the created file with little or no thought.
That's kind of where I am at. I have run into a number of folks that have the title of DBA foist upon them when they have no business looking at a RDBMS. In order for me to ensure that there is some level of recoverability the KISS principal applies. "If you need a backup outside of the schedule, just click here."
SQL Server Native backups are simple to implement and manage and this is by design. However, the unskilled, simpleminded user can still wreak havoc by completing the process incorrectly. :w00t:
Regards, Irish