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 Thursday, January 6, 2011 7:42 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/6/2011)
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.


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!

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.


Regards,

Irish
Post #1043760
Posted Friday, January 7, 2011 8:47 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, September 8, 2014 11:08 AM
Points: 810, Visits: 2,134
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.

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.


Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
Post #1044467
Posted Tuesday, January 11, 2011 12:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 9:40 AM
Points: 2, Visits: 52
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 : DotNetMethodException

Any 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/
Post #1046076
Posted Tuesday, January 11, 2011 4:51 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

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



Tks,

JK

Post #1046208
Posted Wednesday, January 12, 2011 8:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 9:40 AM
Points: 2, Visits: 52
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.aspx

Turns 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.
Post #1046523
Posted Monday, January 24, 2011 2:11 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 9, 2012 11:08 AM
Points: 6, Visits: 87
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.
Post #1052686
Posted Tuesday, January 25, 2011 12:57 AM
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
That's what I'm talkin' about ...


Tks,

JK

Post #1052901
Posted Tuesday, January 25, 2011 12:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 9, 2012 11:08 AM
Points: 6, Visits: 87
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?
Post #1053422
Posted Tuesday, January 25, 2011 5:30 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
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. JK
http://social.msdn.microsoft.com/Forums/en-US/sqldisasterrecovery/thread/2eb6810d-160f-4e65-a1ff-1d25eb1b0a5e



Tks,

JK

Post #1053603
Posted Tuesday, April 10, 2012 3:13 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 4:11 AM
Points: 339, Visits: 581
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
}

}

Thanks

Vinay

Post #1280676
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse