Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Powershell Database Backup Script


Powershell Database Backup Script

Author
Message
Jeffrey Irish
Jeffrey Irish
SSC-Enthusiastic
SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)

Group: General Forum Members
Points: 163 Visits: 1122
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! :-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.

Regards,

Irish w00t
GabyYYZ
GabyYYZ
Say Hey Kid
Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)

Group: General Forum Members
Points: 673 Visits: 2332
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

kcrowder
kcrowder
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 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: (Smile [], 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/
Joe Kelly-376311
Joe Kelly-376311
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

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

kcrowder
kcrowder
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 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.
freakypriest
freakypriest
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

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

Group: General Forum Members
Points: 127 Visits: 84
That's what I'm talkin' about ...


Tks,

JK

freakypriest
freakypriest
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

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

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

Rao.V
Rao.V
Old Hand
Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)

Group: General Forum Members
Points: 351 Visits: 691
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
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