Backing Up SQL Server Databases is Easier in PowerShell than T-SQL

  • Aaron Nelson

    SSCommitted

    Points: 1836

    Comments posted to this topic are about the item Backing Up SQL Server Databases is Easier in PowerShell than T-SQL

    @SQLvariantI have a PowerShell script[/url] for you.

  • er.mayankshukla

    SSCarpal Tunnel

    Points: 4190

    Aaron Nelson - SQLvariant - Thursday, January 12, 2017 12:07 AM

    Comments posted to this topic are about the item Backing Up SQL Server Databases is Easier in PowerShell than T-SQL

    Nice article and it clearly shows power and features of powershell which are usually ignored by many.
    Even I had written a powershell script that uses sql queries internally but allows users to perform operations like backup, Restore from device, restore from db, create a new db from db.

    http://www.sqlservercentral.com/scripts/Backup/151723/

  • Aaron Nelson

    SSCommitted

    Points: 1836

    Hey folks, looks like the link to AndrΓ©'s video got left off.  You can find his video here:
    https://youtu.be/4GaMnamvEJk

    @SQLvariantI have a PowerShell script[/url] for you.

  • ThomasRushton

    SSC-Insane

    Points: 22591

    Loving the practical example for Out-Gridview -PassThru.  Never seen that demoed before (so have never used it), and am now wondering why not?  Great post, Aaron, thanks.

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • Thom A

    SSC Guru

    Points: 98212

    Out-Gridview -PassThru is very interesting. Definitely a consideration for some of my scripts πŸ™‚ Even I don't use the back up solution, I've definitely learned something today.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Andy Warren

    SSC Guru

    Points: 119655

    Aaron,

    First - see, I did read it! Thought it was well done, I like the tiny step approach of adding on and I think its a good example because its something any DBA can follow and a great place to start a newbie to Posh.

  • skreebydba

    SSC-Addicted

    Points: 495

    Aaron,
    Thanks for the post.  The Out-GridView -PassThru is the best thing I have seen in a while.  I am presenting at my user group tonight and will demo that before I get started.  
    Frank

  • KenpoDBA

    SSCrazy Eights

    Points: 9801

    While this article does a good job of showing the PS cmdlet for backup, I think it obscures the complexity of backup.  This method works ok when you treat all the DBs on the server the same, but I've been in large shops for yrs backing up servers with thousands of DBs going to many locations at once.  They've also got different requirements for striping, tuning settings, retention, cert backups and password mgmt, etc.  There's much more to backing up SQL than simply producing a loop with a simple statement.
    And even if all that other stuff went away, I think a PS solution disregards the complexity of deployment.  With PS you'd have to deploy a script to each server because the PS agent task is a piece of crap.  So now you've got to put a folder out in the same location on every box, so you've got to find a location that all boxes have in common where someone won't delete your scripts.  A lot of shops won't let you deploy anything at all to C: so you've got to find a mutual drive, which may be harder than it sounds.  And there are differences between versions of PS that are going to throw things off as well as some boxes may not even have PS on every server.
    Most of the stuff I've mentioned is easier and better managed in tsql because those settings would likely live there, and your code is less likely to be deleted out of SQL itself.
    So while I accept your premise that under this limited simple circumstance, PS is easier, SQL backup as an enterprise process is very complex and has lots of exceptions, settings, and processes involved with it.  And it needs to be managed at an enterprise level in a way that's easy for the DBA team to report on and make changes to.  And having settings anywhere but in SQL will make that very hard when you're dealing with thousands of boxes and even tens of thousands of DBs.

    There I tried to keep that as short as I could.

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

  • Aaron Nelson

    SSCommitted

    Points: 1836

    KenpoDBA - Thursday, January 12, 2017 2:09 PM

    While this article does a good job of showing the PS cmdlet for backup, I think it obscures the complexity of backup.  This method works ok when you treat all the DBs on the server the same, but I've been in large shops for yrs backing up servers with thousands of DBs going to many locations at once.  They've also got different requirements for striping, tuning settings, retention, cert backups and password mgmt, etc.  There's much more to backing up SQL than simply producing a loop with a simple statement.
    And even if all that other stuff went away, I think a PS solution disregards the complexity of deployment.  With PS you'd have to deploy a script to each server because the PS agent task is a piece of crap.  So now you've got to put a folder out in the same location on every box, so you've got to find a location that all boxes have in common where someone won't delete your scripts.  A lot of shops won't let you deploy anything at all to C: so you've got to find a mutual drive, which may be harder than it sounds.  And there are differences between versions of PS that are going to throw things off as well as some boxes may not even have PS on every server.
    Most of the stuff I've mentioned is easier and better managed in tsql because those settings would likely live there, and your code is less likely to be deleted out of SQL itself.
    So while I accept your premise that under this limited simple circumstance, PS is easier, SQL backup as an enterprise process is very complex and has lots of exceptions, settings, and processes involved with it.  And it needs to be managed at an enterprise level in a way that's easy for the DBA team to report on and make changes to.  And having settings anywhere but in SQL will make that very hard when you're dealing with thousands of boxes and even tens of thousands of DBs.

    There I tried to keep that as short as I could.

    I just wanted to show people something that might simplify a potentially time consuming ad-hoc task for them, and get more folks interested in the topic.

    I find this to be a valuable skill to be able to call on. 
    πŸ™‚

    @SQLvariantI have a PowerShell script[/url] for you.

  • KenpoDBA

    SSCrazy Eights

    Points: 9801

    Aaron Nelson - SQLvariant - Thursday, January 12, 2017 2:23 PM

    KenpoDBA - Thursday, January 12, 2017 2:09 PM

    While this article does a good job of showing the PS cmdlet for backup, I think it obscures the complexity of backup.  This method works ok when you treat all the DBs on the server the same, but I've been in large shops for yrs backing up servers with thousands of DBs going to many locations at once.  They've also got different requirements for striping, tuning settings, retention, cert backups and password mgmt, etc.  There's much more to backing up SQL than simply producing a loop with a simple statement.
    And even if all that other stuff went away, I think a PS solution disregards the complexity of deployment.  With PS you'd have to deploy a script to each server because the PS agent task is a piece of crap.  So now you've got to put a folder out in the same location on every box, so you've got to find a location that all boxes have in common where someone won't delete your scripts.  A lot of shops won't let you deploy anything at all to C: so you've got to find a mutual drive, which may be harder than it sounds.  And there are differences between versions of PS that are going to throw things off as well as some boxes may not even have PS on every server.
    Most of the stuff I've mentioned is easier and better managed in tsql because those settings would likely live there, and your code is less likely to be deleted out of SQL itself.
    So while I accept your premise that under this limited simple circumstance, PS is easier, SQL backup as an enterprise process is very complex and has lots of exceptions, settings, and processes involved with it.  And it needs to be managed at an enterprise level in a way that's easy for the DBA team to report on and make changes to.  And having settings anywhere but in SQL will make that very hard when you're dealing with thousands of boxes and even tens of thousands of DBs.

    There I tried to keep that as short as I could.

    I just wanted to show people something that might simplify a potentially time consuming ad-hoc task for them, and get more folks interested in the topic.

    I find this to be a valuable skill to be able to call on. 
    πŸ™‚

    Sure, and it's a good piece for that... it really is.  I just didn't want anyone thinking that PS is a better solution for backup than tsql as an enterprise solution.  And when you get into it, that question isn't as easy to answer either cause both PS and tsql have their strengths when looking at a big backup routine.  Can you tell backup is a hot button topic of mine?

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

  • Aaron Nelson

    SSCommitted

    Points: 1836

    KenpoDBA - Thursday, January 12, 2017 2:31 PM

    Aaron Nelson - SQLvariant - Thursday, January 12, 2017 2:23 PM

    KenpoDBA - Thursday, January 12, 2017 2:09 PM

    While this article does a good job of showing the PS cmdlet for backup, I think it obscures the complexity of backup.  This method works ok when you treat all the DBs on the server the same, but I've been in large shops for yrs backing up servers with thousands of DBs going to many locations at once.  They've also got different requirements for striping, tuning settings, retention, cert backups and password mgmt, etc.  There's much more to backing up SQL than simply producing a loop with a simple statement.
    And even if all that other stuff went away, I think a PS solution disregards the complexity of deployment.  With PS you'd have to deploy a script to each server because the PS agent task is a piece of crap.  So now you've got to put a folder out in the same location on every box, so you've got to find a location that all boxes have in common where someone won't delete your scripts.  A lot of shops won't let you deploy anything at all to C: so you've got to find a mutual drive, which may be harder than it sounds.  And there are differences between versions of PS that are going to throw things off as well as some boxes may not even have PS on every server.
    Most of the stuff I've mentioned is easier and better managed in tsql because those settings would likely live there, and your code is less likely to be deleted out of SQL itself.
    So while I accept your premise that under this limited simple circumstance, PS is easier, SQL backup as an enterprise process is very complex and has lots of exceptions, settings, and processes involved with it.  And it needs to be managed at an enterprise level in a way that's easy for the DBA team to report on and make changes to.  And having settings anywhere but in SQL will make that very hard when you're dealing with thousands of boxes and even tens of thousands of DBs.

    There I tried to keep that as short as I could.

    I just wanted to show people something that might simplify a potentially time consuming ad-hoc task for them, and get more folks interested in the topic.

    I find this to be a valuable skill to be able to call on. 
    πŸ™‚

    Sure, and it's a good piece for that... it really is.  I just didn't want anyone thinking that PS is a better solution for backup than tsql as an enterprise solution.  And when you get into it, that question isn't as easy to answer either cause both PS and tsql have their strengths when looking at a big backup routine.  Can you tell backup is a hot button topic of mine?

    As a vendor for a product that revolves around backup and restore, I'd expect nothing less! Can you recommend a freely available article or tutorial for advanced backup techniques in PowerShell? If people are reading the forum, I believe I've accomplished my goal of getting them excited and interested, and now we can take it further.

    @SQLvariantI have a PowerShell script[/url] for you.

  • KenpoDBA

    SSCrazy Eights

    Points: 9801

    Aaron Nelson - SQLvariant - Thursday, January 12, 2017 4:14 PM

    KenpoDBA - Thursday, January 12, 2017 2:31 PM

    Aaron Nelson - SQLvariant - Thursday, January 12, 2017 2:23 PM

    KenpoDBA - Thursday, January 12, 2017 2:09 PM

    While this article does a good job of showing the PS cmdlet for backup, I think it obscures the complexity of backup.  This method works ok when you treat all the DBs on the server the same, but I've been in large shops for yrs backing up servers with thousands of DBs going to many locations at once.  They've also got different requirements for striping, tuning settings, retention, cert backups and password mgmt, etc.  There's much more to backing up SQL than simply producing a loop with a simple statement.
    And even if all that other stuff went away, I think a PS solution disregards the complexity of deployment.  With PS you'd have to deploy a script to each server because the PS agent task is a piece of crap.  So now you've got to put a folder out in the same location on every box, so you've got to find a location that all boxes have in common where someone won't delete your scripts.  A lot of shops won't let you deploy anything at all to C: so you've got to find a mutual drive, which may be harder than it sounds.  And there are differences between versions of PS that are going to throw things off as well as some boxes may not even have PS on every server.
    Most of the stuff I've mentioned is easier and better managed in tsql because those settings would likely live there, and your code is less likely to be deleted out of SQL itself.
    So while I accept your premise that under this limited simple circumstance, PS is easier, SQL backup as an enterprise process is very complex and has lots of exceptions, settings, and processes involved with it.  And it needs to be managed at an enterprise level in a way that's easy for the DBA team to report on and make changes to.  And having settings anywhere but in SQL will make that very hard when you're dealing with thousands of boxes and even tens of thousands of DBs.

    There I tried to keep that as short as I could.

    I just wanted to show people something that might simplify a potentially time consuming ad-hoc task for them, and get more folks interested in the topic.

    I find this to be a valuable skill to be able to call on. 
    πŸ™‚

    Sure, and it's a good piece for that... it really is.  I just didn't want anyone thinking that PS is a better solution for backup than tsql as an enterprise solution.  And when you get into it, that question isn't as easy to answer either cause both PS and tsql have their strengths when looking at a big backup routine.  Can you tell backup is a hot button topic of mine?

    As a vendor for a product that revolves around backup and restore, I'd expect nothing less! Can you recommend a freely available article or tutorial for advanced backup techniques in PowerShell? If people are reading the forum, I believe I've accomplished my goal of getting them excited and interested, and now we can take it further.

    Hey, I don't even try to hide my love for my product and since it's completely free I see no reason why I should.  However, I also don't hide the complexity of backup processes and how they need to operate in real environments.  No, I don't know of any advanced tutorials on PS backup techniques.  And I'm not likely to write one cause I firmly believe that the bulk of the backup process still belongs in tsql.  Now, will I eat my words in the future?  I don't know.  Maybe something will happen to make me change my mind and when that time comes I'll be the first to say I'm wrong.  Actually, someone on the internet will be the first to say I'm wrong, but I'll agree pretty quickly.  Until then though, I'll stick with the argument that most of backup should live in tsql.  And again, I'm really not trying to poopoo on your article.  I've always loved your PS pieces and you always give sound advice.  Barring this time of course when you recommended Ola's routine instead of Minion, but I may be biased.

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

  • hiramfleitas

    Valued Member

    Points: 53

    You had me at 

    Out-GridView -PassThru 

    Thx for another stellar article
    .:)

    Thanks,
    Hiram

  • KenpoDBA

    SSCrazy Eights

    Points: 9801

    Aaron Nelson - SQLvariant - Thursday, January 12, 2017 4:14 PM

    KenpoDBA - Thursday, January 12, 2017 2:31 PM

    Aaron Nelson - SQLvariant - Thursday, January 12, 2017 2:23 PM

    KenpoDBA - Thursday, January 12, 2017 2:09 PM

    While this article does a good job of showing the PS cmdlet for backup, I think it obscures the complexity of backup.  This method works ok when you treat all the DBs on the server the same, but I've been in large shops for yrs backing up servers with thousands of DBs going to many locations at once.  They've also got different requirements for striping, tuning settings, retention, cert backups and password mgmt, etc.  There's much more to backing up SQL than simply producing a loop with a simple statement.
    And even if all that other stuff went away, I think a PS solution disregards the complexity of deployment.  With PS you'd have to deploy a script to each server because the PS agent task is a piece of crap.  So now you've got to put a folder out in the same location on every box, so you've got to find a location that all boxes have in common where someone won't delete your scripts.  A lot of shops won't let you deploy anything at all to C: so you've got to find a mutual drive, which may be harder than it sounds.  And there are differences between versions of PS that are going to throw things off as well as some boxes may not even have PS on every server.
    Most of the stuff I've mentioned is easier and better managed in tsql because those settings would likely live there, and your code is less likely to be deleted out of SQL itself.
    So while I accept your premise that under this limited simple circumstance, PS is easier, SQL backup as an enterprise process is very complex and has lots of exceptions, settings, and processes involved with it.  And it needs to be managed at an enterprise level in a way that's easy for the DBA team to report on and make changes to.  And having settings anywhere but in SQL will make that very hard when you're dealing with thousands of boxes and even tens of thousands of DBs.

    There I tried to keep that as short as I could.

    I just wanted to show people something that might simplify a potentially time consuming ad-hoc task for them, and get more folks interested in the topic.

    I find this to be a valuable skill to be able to call on. 
    πŸ™‚

    Sure, and it's a good piece for that... it really is.  I just didn't want anyone thinking that PS is a better solution for backup than tsql as an enterprise solution.  And when you get into it, that question isn't as easy to answer either cause both PS and tsql have their strengths when looking at a big backup routine.  Can you tell backup is a hot button topic of mine?

    As a vendor for a product that revolves around backup and restore, I'd expect nothing less! Can you recommend a freely available article or tutorial for advanced backup techniques in PowerShell? If people are reading the forum, I believe I've accomplished my goal of getting them excited and interested, and now we can take it further.

    Oh and I want to do this in a place where everyone can see.  I made a mistake.  I was very busy when I was reading this originally, and I somehow skipped the paragraph where Aaron advised people that this was only for one-off backups, and that for a real enterprise solution, you'd be better off in T-SQL.  So I apologize to everyone for causing a stink where there wasn't one.  So sorry Aaron, I missed that part and your article does exactly what its stated goal is.  I try not to be that guy out there causing needless trouble and when I realize I am I like to correct it myself.

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    So much to learn in PowerShell.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply