Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Powershell Database Backup Script


Powershell Database Backup Script

Author
Message
jimmy.atsaves
jimmy.atsaves
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 40
Comments posted to this topic are about the item Powershell Database Backup Script
hrc
hrc
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 52
Great as (for me) an introduction into the world of PowerShelling. In your text I think you syntactically need to to point out the values that need change e.g. "mailserver" should be "<mailserver>" so it stands out.
Alberto dbLearner
Alberto dbLearner
Mr or Mrs. 500
Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)

Group: General Forum Members
Points: 508 Visits: 227
Please explain me about the variable $location. It is used in the line $webclient.UploadFile($uri, $location) to send the file to a FTP server, but $location is never initialized nor populated.

Alberto
------
Jeffrey Irish
Jeffrey Irish
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 1126
Jimmy,

Have you found a method to provide percent completed with a PowerShell script? We use P$ to run ad hoc backups, but the most frustrating part is that there is no progress meter or percentage complete.

Regards,

Irish w00t
KenpoDBA
KenpoDBA
Mr or Mrs. 500
Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)

Group: General Forum Members
Points: 580 Visits: 617
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.

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

Minion Maintenance is FREE:


barryc-707782
barryc-707782
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 8
Let me add that thought the author makes note of the fact that there more complex scenarios may result in even more complex scripts, this script is also very complex when compared to using the SQL Server Management Studio tools for building a backup plan, let alone a full maintenance plan.

I'm prejudiced against this approach - it was fine when there were no alternatives to scripting - but doing this today seems more a case of showing off scripting chops than actually using one's time productively.

While I have issues with Microsoft's increased use of CLIs and scripting, they've generally gone about it in a way to which I subscribe: Build scripts using a GUI so they can be generally error free while enabling access to the code for replication (though I still think doing it over in the GUI would be as fast or faster) and possible advanced automation.

But that's just me.
Jeffrey Irish
Jeffrey Irish
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 1126
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 w00t
KenpoDBA
KenpoDBA
Mr or Mrs. 500
Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)

Group: General Forum Members
Points: 580 Visits: 617
Thanks Jeffrey...
however in the scenario you described, doing a native SQL backup and putting it into an agent job is still the best solution. All you have to do now is call the job from a PS script. This way the DBAs can support the job from their end, and the OPs guys can support it from their end. And you still get all the benefits from having it in SQL.
Just because a process can use PS doesn't mean the entire solution has to be written in PS. You can use it just as a step in the process.

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

Minion Maintenance is FREE:


SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22633 Visits: 18259
Nice article and interesting read. As an intro to powershell I can see the use for it. As Sean has stated though - I would rather the jobs to backup the databases be done through SQL Agent at a minimum. Knowing the history of the backup jobs is essential. Being able to see backup size from a central location is also nice to have for trending purposes.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

297t73g
297t73g
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 246
I would also agree with Kenodba, but am very interested in understanding how PS could be leveraged otherwise for dba.
BTW, I am always nervous about deleting a previous days backup, until I know my current backup is good, assume there is not a HD space constraint in place. Just a thought...



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search