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 Wednesday, January 5, 2011 9:04 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
If you guys are interested, I have tons of powershell videos on my site for free. Everything from beginning, to some pretty cool task-based stuff.
http://www.MidnightDBA.com


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 #1043130
Posted Wednesday, January 5, 2011 10:10 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Today @ 12:13 PM
Points: 184, Visits: 1,015
I could see using this script on a SQL express installation without SQL Agent installed. Windows Scheduled Taks would be used for scheduling it.

I am not sure how I would use a SQL script to delete the old backup without using XP_CMDSHELL. So if I need to use DOS commands to delete the old backup why not use Powershell for file deletes and backups.



Thanks Jimmy for the article


David Bird

My PC Quick Reference Guide
Post #1043187
Posted Wednesday, January 5, 2011 10:42 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
Yeah, in the limited scope of having sql express I could see how initially you might want to do this, but it's still not the best option. Even assuming you decided to use PS to run the backup you're still better off calling a backup SP from invoke-sqlcmd instead of the overly-complicated SMO method.

Also, I don't really want to put that much effort into backing up just a couple express installs. Most companies aren't overrun with them so they're the exception and I'm not going to hinge my entire backup scenario on the LCD.

But if I were faced with this issue, I'd still code it in Agent, I would just run it from another box. Or I would schedule it in an SSIS pkg from another box. Make something you can gather history for the standard. Keep SQL where SQL belongs.

And as for cmdshell... I talk about this all the time. It's not evil, and it's not even unsafe. You just have to lock it down and make sure that it's being used well. So what's wrong with using cmdshell for deleting your backup files? That's what I do. Cmdshell is no more dangerous than having an sa acct. You protect your sa, so protect your cmdshell and use it wisely. But it's not inherently evil or bad or even ill-advised. It just got a bad rep there for a while cause it came unprotected out of the box and got exploited. Now it's locked down by default, so open it up only to the DBAs and start using it again.


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 #1043213
Posted Wednesday, January 5, 2011 2:38 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
if you coded this as part of a winform, then there is a progress bar:
$Form = New-Object Windows.Forms.Form
$progress = New-Object System.Windows.Forms.ProgressBar
$Form.controls.add($progress)
this is the only way i can think of
cheers
Post #1043355
Posted Wednesday, January 5, 2011 2:49 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
i'm sorry i have missed this out
$location variable is a reference to the location of the file on the server
could be
$location="C:\Data\dbName.bak" to upload the daily backup file
or
$location="C:\Archives\"+$b to upload the archive zip of the backup file
thank you
Post #1043367
Posted Wednesday, January 5, 2011 4:45 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 22, 2013 5:49 PM
Points: 97, Visits: 58
I agree with the points being made in the previous posts, but I still think this was an excellent article. It was clear and concise, without being distracted by going into too much detail and allowing for the undefined location variable. It laid out the general sequence of the script and the objects and syntax to use to get started. We can all then lookup the finer switches to add more control.
I understand that running Backups within the SQL Server GUI gives useful info, but that same info can also be collected by the script and logged to a simpler Stats or Monitoring DB. Having managed over 350 servers in one role, I believe that scripted processes are more controlable, predictable repeatable, testable and documentable, than GUI tools. I also know that I may put a solution in place, but I am rarely around to run and Support that process, so I also agree with Jeffery's point re Keep it Simple ...

Thanks again for the great article, Jimmy.
JK



Tks,

JK

Post #1043414
Posted Wednesday, January 5, 2011 5:27 PM


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'd like to make it perfectly clear that I never once said to run your backups through the GUI. Anyone who knows me knows I would never advocate such a thing.

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 #1043429
Posted Thursday, January 6, 2011 6:35 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 9:20 AM
Points: 1,264, Visits: 3,567
David Bird (1/5/2011)
I could see using this script on a SQL express installation without SQL Agent installed. Windows Scheduled Taks would be used for scheduling it.

I am not sure how I would use a SQL script to delete the old backup without using XP_CMDSHELL. So if I need to use DOS commands to delete the old backup why not use Powershell for file deletes and backups.



Thanks Jimmy for the article

If you're interested in using SQL Server to delete your old backup files without using xp_cmdshell, you could look into master.dbo.xp_delete_file

exec master.dbo.xp_delete_file
0, --File type [(0) FileBackup | (1) FileReport]
N'<folderPath>', -- Folder path to look in
N'<ext>', --File extension to be deleted (do not precede with a period)
N'<date>', --Date line. All files meeting criteria created before this date will be deleted
1 -- Subfolder flag. Indicates to delete from subfolders [ 1 | 0 ]

keep in mind this procedure is undocumented and not supported, use at your own risk.
I use it and it works fine for deleting native sql backups. This procedure is what is run when a maintenance task for deleting backup files is scripted. Please do plenty of testing before implementation.


______________________________________________________________________________________________
Forum posting etiquette. Get your answers faster.
Post #1043701
Posted Thursday, January 6, 2011 7:05 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)
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.


KenpoDBA,

I did not state this in my previous post, but we were just calling the Native Backup via PowerShell. However, the only disadvantage is that the user in question does not get a progress bar. For me, it is not an issue because I have a pretty good idea on how long a process will take to complete, but for the OPs guys that may not be the case.

In all honesty, I'm not as worried about the OPs guys as I am the business users. It is almost worth it to write a front end that is a winform that can display the progress and maybe time remaining. I've had a number of business users panic when the ad hoc backup of a 150 GB Database did not finish in 3 minutes!

Ah, well. Have to love the users or I would not be employed.


Regards,

Irish
Post #1043724
Posted Thursday, January 6, 2011 7:20 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
Yeah, I understand that, but it's not a good idea to allow business users to backup DBs to begin with. They put backups in odd locations and fill up the drive. One guy doesn't know another guy just took a backup and next thing you know your resources are being spent taking one after another.

A much better way to handle it is to train them to email the DBAs when they need a backup... or better yet, schedule it for them. A progress bar isn't necessary then and a DBA can tune the backup to perform better anyway. I try never to let anyone outside the DBA group backup a DB if at all possible. It only brings heartbreak later when they call us to fix whatever they've done wrong. So I put in some procedures that make it easy for us to back them up should they need something outside the regular scheduled process, and then just respond to those emails quickly. If you do it right, it only takes a few secs to kick off the backup so it's little burden on you.

So just explain to them that you've got some intricate things in place that have to be done a certain way (space mgmt, log backups, etc) and you need to make sure nobody is taking backups out-of-cycle... and explain to them that they don't wanna be spending their time taking backups anyway. They usually agree as long as they get good service. Have them try it your way on a trial basis if they're concerned. The first couple times are the hardest then they find they actually like not having to mess with it.

As for the article, I agree with the others that it was quite well-written. It was clear, methodic, and said what it needed to say. The only problem I have is with the content. It's just not something that needs to be taught. It's like having a very well-written article on how to turn on autoShrink for all your DBs.


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 #1043737
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse