SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Jimmy Atsaves / Powershell Database Backup Script / Latest Posts

RE: Powershell Database Backup Script

ijidak — Sun, 19 May 2013 13:38:59 GMT

Excellent straightforward article. Regarding how and whether to schedule it, I won't say. But this is a great article to help a person discover clever ways of using PowerShell alongside SQL Server.Appreciate the article. Thank you!

RE: Powershell Database Backup Script

Jeff Moden — Fri, 17 May 2013 18:45:15 GMT

[quote][b]sqlchaser (5/17/2013)[/b][hr]Whether or not PS is the best to approach is definitely debatable. However I appreciate the way the article was presented in an easy to understand approach. I may never need it but feel very confident that in the event that I do I will be able to handle it. Great job![/quote]+1 to that!

RE: Powershell Database Backup Script

Jeff Moden — Fri, 17 May 2013 18:43:29 GMT

[quote][b]freakypriest (1/24/2011)[/b][hr]We don't like turning on xp_cmdshell on all our servers as this is a security problem.[/quote]Actually, it's only your perception that makes you think it's a security problem. The only people that can turn it on are the very same people that can use it. Except for a 3ms or so delay that an attacker's software would experience, there is no substantial extra security realized by having xp_CmdShell turned off.Again, I realize the post that I'm responding to is more than a year old but I wanted to explain that thinking xp_CmdShell is a security problem is an emotional and preferential response rather than anything substantial. Any one who can get in with "SA" privs can turn it on, use it against you, turn it off and delete the miniscule logging of the event before you even realize that someone hacked you.The key to security has nothing to do with xp_CmdShell disabling xp_CmdShell. The key to security is to keep unwanted people out of your system as "SA" and to limit what the SQL Server Service and SQL Agent can do if you do get hacked. In fact, because a lot of people believe that disabling xp_CmdShell will somehow increase your security, they may become lax in the security they should be doing.For the record, I leave xp_CmdShell enabled for my DBAs and then I make sure I take care of the real security problems. ;-)

RE: Powershell Database Backup Script

Jeff Moden — Fri, 17 May 2013 18:33:45 GMT

[quote][b]Rao.V (4/10/2012)[/b][hr]Hi all,I am using idea of script you mentioned to verify the backup status of backup files location in different location. Same like RESTORE VERIFYONLY.[/quote]I realize that this post is over a year old but I have to ask... Why are you building PowerShell to do the same thing as functionality built in to SQL Server?

RE: Powershell Database Backup Script

Jeff Moden — Fri, 17 May 2013 18:11:05 GMT

[quote][b]dbishop (5/17/2013)[/b][hr]KenpoDBA: I have a PoSH script that loads a considerable amount of data into a database in different stages. It would be great to include a backup (with CopyOnly = 1) prior to the load, and after each stage, in case there were issues with the load. I would not necessarily need to create a copy of the backup, but if there were issues, I would have a backup to restore to the original state. Can't do that in using SQL agent.[/quote]Why is it necessary to load the data using PowerShell to begin with? What's so special about the data that would require such a thing? Not a challenge. Just a curiosity.Also, even if PowerShell is required, it may be quite easy to do interim backups with SQL Agent. I;d have to know more about the overall process to say that definitively but a lot of people overlook some of the power built into SQL Agent.As a side bar, what size is your database? The ones I'm working with (~200GB) aren't huge but I can guarantee that I only have room to make 2 copies in the backup stagig disks. If I had to do interim backups, they'd most likely be Point-In-Time log backups which would also keep the log file from freaking out during the multiple loads.

RE: Powershell Database Backup Script

sqlchaser — Fri, 17 May 2013 14:43:37 GMT

Whether or not PS is the best to approach is definitely debatable. However I appreciate the way the article was presented in an easy to understand approach. I may never need it but feel very confident that in the event that I do I will be able to handle it. Great job!

RE: Powershell Database Backup Script

rstone — Fri, 17 May 2013 10:15:17 GMT

I would still prefer the job being scheduled in the agent. This avoids putting all your eggs in one basket by counting on the one scheduler to be maintained correctly. (I don't count on our SAN to not be down for 3 days. I don't count on our enterprise scheduler not stopping because it thinks the license expired.) There is a good solution that does most maintenance available already ([url=http://ola.hallengren.com/sql-server-backup.html][/url]). Ola's solution provides log files with details. It also has the option to clean old log files or backups. It should normal run without issue. If there is no DBA to monitor the maintenance, then perhaps a few alerts to a non-DBA so that they let management know that they need to find a DBA to fix it. If somebody needs a simple button on the desktop, then perhaps the PSH can get the maintenance history, check file space, start jobs, copy specific backup files, etc. I have been thinking about using PSH to start jobs because we have been using several instances on some beefy hardware. It would be nice to be able to run related jobs (e.g., backup) in order across instances with one start. (I manually schedule them to not overlap now.)Even if a PSH script is complex enough to replace Ola's solution, I would still want to run it in the agent. Perhaps as a PSH job step. Perhaps it could be instance aware to allow redundancy or complex scheduling? (I was thinking to create a job step using PSH to detect and wait if the same job is running on another instance of the same server. That would be easier than replacing Ola's solution.)

RE: Powershell Database Backup Script

kennethigiri — Fri, 17 May 2013 09:34:47 GMT

Wiered as it may seem, I am relatively new to Powershell. I notice in the script you declared a variable that append the current date to the backup file name. If so, how do you delete the file using the name "dBName.bak"?

RE: Powershell Database Backup Script

dbishop — Fri, 17 May 2013 09:12:12 GMT

KenpoDBA: I have a PoSH script that loads a considerable amount of data into a database in different stages. It would be great to include a backup (with CopyOnly = 1) prior to the load, and after each stage, in case there were issues with the load. I would not necessarily need to create a copy of the backup, but if there were issues, I would have a backup to restore to the original state. Can't do that in using SQL agent.

RE: Powershell Database Backup Script

gsc_dba — Mon, 08 Apr 2013 06:47:20 GMT

[quote][b]Rao.V (4/10/2012)[/b][hr]Hi all,I am using idea of script you mentioned to verify the backup status of backup files location in different location. Same like RESTORE VERIFYONLY.The 'C:\Temp\Paths.txt' is having backup file locations of different servers. The code is working perfectly when I use one path but its failing to execute when more than one path is specified. Please help me on what modifications to be done to the script in order verify the backup file status of different locations.[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") ("servername")$dbBackup = new-Object ("Microsoft.SqlServer.Management.Smo.Backup")$dbRestore = new-object ("Microsoft.SqlServer.Management.Smo.Restore")$a = Get-Content "C:\Temp\paths.txt"foreach ($path in $a){$dbRestore.Devices.AddDevice($path, "File")if (!($dbRestore.SqlVerify($server))){ $body = $path $emailFrom = "email" $emailTo = "email" $subject = "Backup Files Health" $smtpServer = "serverip" $smtp = new-object Net.Mail.SmtpClient($smtpserver) $smtp.Send($emailFrom, $emailTo, $subject, $body)[b] [strike]Exit[/strike][/b]}}ThanksVinay[/quote][b] [strike]Exit[/strike][/b]

RE: Powershell Database Backup Script

Rao.V — Tue, 10 Apr 2012 03:13:38 GMT

Hi all,I am using idea of script you mentioned to verify the backup status of backup files location in different location. Same like RESTORE VERIFYONLY.The 'C:\Temp\Paths.txt' is having backup file locations of different servers. The code is working perfectly when I use one path but its failing to execute when more than one path is specified. Please help me on what modifications to be done to the script in order verify the backup file status of different locations.[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") ("servername")$dbBackup = new-Object ("Microsoft.SqlServer.Management.Smo.Backup")$dbRestore = new-object ("Microsoft.SqlServer.Management.Smo.Restore")$a = Get-Content "C:\Temp\paths.txt"foreach ($path in $a){$dbRestore.Devices.AddDevice($path, "File")if (!($dbRestore.SqlVerify($server))){ $body = $path $emailFrom = "email" $emailTo = "email" $subject = "Backup Files Health" $smtpServer = "serverip" $smtp = new-object Net.Mail.SmtpClient($smtpserver) $smtp.Send($emailFrom, $emailTo, $subject, $body) Exit}}ThanksVinay

RE: Powershell Database Backup Script

Joe Kelly-376311 — Tue, 25 Jan 2011 17:30:19 GMT

Here is a link to a good discussion on that subject. Seems SMO passes a value back from the Restore at least that the code is using to test for success. JKhttp://social.msdn.microsoft.com/Forums/en-US/sqldisasterrecovery/thread/2eb6810d-160f-4e65-a1ff-1d25eb1b0a5e

RE: Powershell Database Backup Script

freakypriest — Tue, 25 Jan 2011 12:05:41 GMT

The only thing I don't know yet is error handling. Does SMO forward errors that happen in the SQL Server while the backup runs to powershell so you can log them?

RE: Powershell Database Backup Script

Joe Kelly-376311 — Tue, 25 Jan 2011 00:57:27 GMT

That's what I'm talkin' about ...

RE: Powershell Database Backup Script

freakypriest — Mon, 24 Jan 2011 14:11:23 GMT

Actually there are a business cases where such a script is quite usefull. I work for a global corporation with over 700 instances of SQL Server spread across the globe. We are less than 20 SQL DBAs. We don't like turning on xp_cmdshell on all our servers as this is a security problem. But our backup script needs to create subdirectories, create a log file for each day, move those log files to a central logging server, archive old log files on that server, delete even older log files, delete old backups, etc.As you can see, pure T-SQL without xp_cmdshell will not help. A stored procedure written in .NET can. But I don't like loading assemblies on each servers either. And if I have to run a T-SQL script from a batch file which does all the work for me, then why not use PS? It's much more comfortable to write such operations in PS than in standard DOS batches.

RE: Powershell Database Backup Script

kcrowder — Wed, 12 Jan 2011 08:19:04 GMT

After much searching, I found a post by Allen White that suggested to use a Trap to catch the inner exception.http://sqlblog.com/blogs/allen_white/archive/2009/06/08/handling-errors-in-powershell.aspxTurns out the Microsoft.SqlServer.BatchParser, Version=9.0.242.0, was "missing." Apparently it's a 64-bit box, with 32 bit SQL, and I was using the 32 bit powershell. Switching to 64 bit PoSh was the ultimate fix.

RE: Powershell Database Backup Script

Joe Kelly-376311 — Tue, 11 Jan 2011 16:51:38 GMT

Off the top of my head, that looks like it is looking for a Service Principle Name ?? What Security contaxt are you using to connect to the remote box ? If it is Kerberos, you need to setup an SPN for the SQL service. See this article for a start ...http://blogs.technet.com/b/askds/archive/2008/06/11/kerberos-authentication-problems-service-principal-name-spn-issues-part-3.aspxJK

RE: Powershell Database Backup Script

kcrowder — Tue, 11 Jan 2011 12:07:21 GMT

This script is genius - but I'm having some issues with it. I've manged to get it to run on my XP machine, running sql 2005 - but when I change out the DB names and the server, and run it on a server 2008 box, I run into issues.I've got server 2008, running SQL Express 2005 and another 2008 machine running SQL Express 2008. I ran into this same error on my XP machine, but it was a result of the path not existing. I have created the path on the two servers.The error message I'm getting is:Exception calling "SqlBackup" with "1" argument(s): "Back failed for server 'server\instance,port'. " At line:1 char:20+ $dbbackup.sqlbackup <<<< ($server);+categoryInfo :Notspecified: (:) [], MethodinvocationException+ FullyQualifiedERrorId : DotNetMethodExceptionAny assistance would be much appreciated.As a side note, I've already tried this: http://sqlaj.wordpress.com/2010/10/10/error-running-sql-2008-smo-sqlbackup/

RE: Powershell Database Backup Script

GabyYYZ — Fri, 07 Jan 2011 08:47:37 GMT

[quote][b]KenpoDBA (1/5/2011)[/b][hr]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.[/quote]While I do see a place for Powershell and SQL to mingle together in some aspects (basically where there is an order of magnitude benefit to justify the effort in using Powershell), for the most part I do agree to let SQL do what it does best. There is a place for Powershell, for example in creating an archiving strategy after the SQL backups are completed (so the zip feature would be useful combined with retrieval of timestamps).However, the one glaring example where this script can be very useful is for SQL Express which disables SQL maintenance jobs by not allowing you to run SQL Agent.

RE: Powershell Database Backup Script

Jeffrey Irish — Thu, 06 Jan 2011 07:42:56 GMT

[quote][b]KenpoDBA (1/6/2011)[/b][hr]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.[/quote]Oh how I wish it were so easy to limit the ability of creating backups to Database Admins. While I agree it is risky, sometimes there is little choice.I work for a Software Company and support a large number of corporations in a specific industry (I cannot be more specific than that). Due to the nature of the business and varying size of the corporations (500 to 40,000 employees) each one has a different skill set.I could tell you some nightmare-ish storied about recoveries I have done in the past, but we all have war stories. We have to apply the KISS principal very frequently just to ensure that backups are being created in the first place! :-D Anyway, I too agree that the article is well written and provides a great base to start with. PowerShell is a fantastic tool that I find to be under utilized.

RE: Powershell Database Backup Script

KenpoDBA — Thu, 06 Jan 2011 07:20:34 GMT

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.

RE: Powershell Database Backup Script

Jeffrey Irish — Thu, 06 Jan 2011 07:05:36 GMT

[quote][b]KenpoDBA (1/5/2011)[/b][hr]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.[/quote]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.

RE: Powershell Database Backup Script

calvo — Thu, 06 Jan 2011 06:35:49 GMT

[quote][b]David Bird (1/5/2011)[/b][hr]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[/quote]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_fileexec master.dbo.xp_delete_file0, --File type [(0) FileBackup | (1) FileReport]N'<folderPath>', -- Folder path to look inN'<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 deleted1 -- 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.

RE: Powershell Database Backup Script

KenpoDBA — Wed, 05 Jan 2011 17:27:55 GMT

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.

RE: Powershell Database Backup Script

Joe Kelly-376311 — Wed, 05 Jan 2011 16:45:15 GMT

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

RE: Powershell Database Backup Script

jimmy.atsaves — Wed, 05 Jan 2011 14:49:49 GMT

i'm sorry i have missed this out$location variable is a reference to the location of the file on the servercould be $location="C:\Data\dbName.bak" to upload the daily backup fileor$location="C:\Archives\"+$b to upload the archive zip of the backup filethank you

RE: Powershell Database Backup Script

jimmy.atsaves — Wed, 05 Jan 2011 14:38:09 GMT

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 ofcheers

RE: Powershell Database Backup Script

KenpoDBA — Wed, 05 Jan 2011 10:42:44 GMT

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.

RE: Powershell Database Backup Script

David Bird — Wed, 05 Jan 2011 10:10:54 GMT

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

RE: Powershell Database Backup Script

KenpoDBA — Wed, 05 Jan 2011 09:04:07 GMT

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

RE: Powershell Database Backup Script

npcrwill8 — Wed, 05 Jan 2011 08:57:58 GMT

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

RE: Powershell Database Backup Script

SQLRNNR — Wed, 05 Jan 2011 08:53:01 GMT

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.

RE: Powershell Database Backup Script

KenpoDBA — Wed, 05 Jan 2011 08:28:16 GMT

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.

RE: Powershell Database Backup Script

Jeffrey Irish — Wed, 05 Jan 2011 08:19:28 GMT

[quote][b]KenpoDBA (1/5/2011)[/b][hr]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.[/quote]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:

RE: Powershell Database Backup Script

barryc-707782 — Wed, 05 Jan 2011 08:06:59 GMT

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.

RE: Powershell Database Backup Script

KenpoDBA — Wed, 05 Jan 2011 07:54:58 GMT

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.

RE: Powershell Database Backup Script

Jeffrey Irish — Wed, 05 Jan 2011 06:49:31 GMT

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.

RE: Powershell Database Backup Script

Alberto dbLearner — Wed, 05 Jan 2011 05:46:08 GMT

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------

RE: Powershell Database Backup Script

hrc — Wed, 05 Jan 2011 03:35:14 GMT

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.

Powershell Database Backup Script

jimmy.atsaves — Tue, 04 Jan 2011 21:27:38 GMT

Comments posted to this topic are about the item [B]Powershell Database Backup Script[/B]