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
gsc_dba
gsc_dba
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1593 Visits: 1985
Rao.V (4/10/2012)
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



Exit

_____________________________________________________________________________________
[font=Courier New]gsc_dba[/font]
dbishop
dbishop
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 248
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.
Br. Kenneth Igiri
Br. Kenneth Igiri
SSC-Enthusiastic
SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)

Group: General Forum Members
Points: 169 Visits: 446
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"?

Br. Kenneth Igiri
www.scribblingsage.com
www.igiribooks.com
"All nations come to my light, all kings to the brightness of my rising"Smooooth
rstone
rstone
SSC-Addicted
SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)

Group: General Forum Members
Points: 405 Visits: 824
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.)

Randy
Helpdesk: "Perhaps I'm not the only one that does not know what you are doing." ;-)
sqlchaser
sqlchaser
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: 106
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!
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45123 Visits: 39921
dbishop (5/17/2013)
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.


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.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45123 Visits: 39921
Rao.V (4/10/2012)
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.


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?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45123 Visits: 39921
freakypriest (1/24/2011)
We don't like turning on xp_cmdshell on all our servers as this is a security problem.


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

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45123 Visits: 39921
sqlchaser (5/17/2013)
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!


+1 to that!

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
ijidak
ijidak
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 23
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!
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