Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Powershell Database Backup Script Expand / Collapse
Author
Message
Posted Tuesday, January 4, 2011 9:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 12, 2011 10:34 PM
Points: 4, Visits: 40
Comments posted to this topic are about the item Powershell Database Backup Script
Post #1042804
Posted Wednesday, January 5, 2011 3:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 5, 2014 5:49 AM
Points: 12, Visits: 45
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.
Post #1042917
Posted Wednesday, January 5, 2011 5:46 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, November 6, 2013 12:34 PM
Points: 480, Visits: 214
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
------
Post #1042962
Posted Wednesday, January 5, 2011 6:49 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 21, 2014 1:55 PM
Points: 149, Visits: 1,027
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
Post #1043011
Posted Wednesday, January 5, 2011 7:54 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 7, 2014 8:23 AM
Points: 318, Visits: 351
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.ITBookworm.com

Read my book reviews at:
www.ITBookworm.com

Blog Author of:
Database Underground -- http://www.infoworld.com/blogs/sean-mccown
DBA Rant – http://dbarant.blogspot.com
Post #1043090
Posted Wednesday, January 5, 2011 8:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 6, 2011 6:54 AM
Points: 1, 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.
Post #1043094
Posted Wednesday, January 5, 2011 8:19 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 21, 2014 1:55 PM
Points: 149, Visits: 1,027
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.


Regards,

Irish
Post #1043101
Posted Wednesday, January 5, 2011 8:28 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 7, 2014 8:23 AM
Points: 318, Visits: 351
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.ITBookworm.com

Read my book reviews at:
www.ITBookworm.com

Blog Author of:
Database Underground -- http://www.infoworld.com/blogs/sean-mccown
DBA Rant – http://dbarant.blogspot.com
Post #1043109
Posted Wednesday, January 5, 2011 8:53 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:51 AM
Points: 21,747, Visits: 15,441
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1043120
Posted Wednesday, January 5, 2011 8:57 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, March 21, 2014 3:24 PM
Points: 53, Visits: 233
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...



Post #1043123
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse