I'm about to refactor inherited scripts deployed on multiple servers that backup many other servers. They are a mixture of cmd, vbs and sql. I'm pondering what benefits would bring doing it with Python since I find it friendlier to script? Mainly i'm thinking that with PS I can use something that could be considered native like Invoke-Sqlcmd or sqlps. Is it just a matter of taste or might I stumble upon something nasty and unforeseen by using python?
closed as primarily opinion-based by Kin, Shawn Melton, Tom V, Philᵀᴹ, James Anderson Nov 9 '16 at 9:16
Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.
I am not familiar with Python, but for your current requirement (i.e. backup). I'd strongly recommend you to use PowerShell for two reasons:
MS in its SQLPS module (since SQL Server 2012), has a native cmdlet called
You may run this cmdlet on one central server to do the backup against multiple sql instances at once because this
Backup-SQLDatabasecan accept multiple sql instances, like the following example
Backup-SQLDatabase -Database master -Server "my_server1", "my_server2" ...
This 2nd point may help you to centralize all those backup scripts to one administration server (dedicated for DBA use) instead of sparsely stored on each sql server instance.
Also with MS providing
Backup-SQLDatabase, you can be assured that it will be supported for all future new features. (For example, using this cmdlet to support Azure SQL Database etc.)
I would say it's more than a matter of taste; it's a matter of who owns the scripts; if there are corporate practices for what is acceptable they need to be followed. These days there are a lot of options for scripting, but limiting the technology breadth is important; if you finish your refactoring, then leave, will they have to hire a python expert to carry them on while everything else is in PowerShell?
That said, as long as peers agree, and the output is documented and maintainable, you could make a case for either.
SQL Server SMO is another option. I've had very good success doing backups & restores with C# using it. It looks like you can access this library from Python if you are using Windows.
This site has an example backup syntax.