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

Question

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

Aaron Nelson

SSCommitted

Points: 1898
More actions
January 12, 2017 at 12:07 am

#401362

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.

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

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

er.mayankshukla SSCarpal Tunnel Points: 4190 More actions · Answer 1

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: 1898 More actions · Answer 2

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.

Thomas Rushton SSC-Insane Points: 22648 More actions · Answer 3

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: 99126 More actions · Answer 4

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.
Larnu.uk

Andy Warren SSC Guru Points: 119902 More actions · Answer 5

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.

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

skreebydba SSChasing Mays Points: 646 More actions · Answer 6

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: 9811 More actions · Answer 7

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: 1898 More actions · Answer 8

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: 9811 More actions · Answer 9

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: 1898 More actions · Answer 10

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: 9811 More actions · Answer 11

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: 64 More actions · Answer 12

You had me at

Out-GridView -PassThru

Thx for another stellar article
.:)

Thanks,
Hiram
http://fleitasarts.com

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 13

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 More actions · Answer 14

akljfhnlaflkj

SSC Guru

Points: 76202

January 13, 2017 at 9:18 am

#1922606

So much to learn in PowerShell.