SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Powershell Database Backup Script


Powershell Database Backup Script

Author
Message
KenpoDBA
KenpoDBA
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1426 Visits: 634
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.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:


David Bird
David Bird
SSChasing Mays
SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)

Group: General Forum Members
Points: 647 Visits: 1224
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
KenpoDBA
KenpoDBA
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1426 Visits: 634
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.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:


jimmy.atsaves
jimmy.atsaves
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 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
jimmy.atsaves
jimmy.atsaves
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 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
Joe Kelly-376311
Joe Kelly-376311
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 Visits: 84
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

KenpoDBA
KenpoDBA
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1426 Visits: 634
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.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:


calvo
calvo
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2070 Visits: 4016
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.
Jeffrey Irish
Jeffrey Irish
Old Hand
Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)

Group: General Forum Members
Points: 372 Visits: 1144
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 w00t
KenpoDBA
KenpoDBA
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1426 Visits: 634
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.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:


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