::::::::: PowerShell :::::::::
Saturday, July 29, 2006
  Back up SQL Server database with SQL Server Objects(SMO) using PowerShell
It is easy to set up SQL Server Agent jobs to backup database in timely fashion.
But why not in PowerShell while SMO is exposed in .NET assembly?

Usage:
Backup-Database -dbName [string] -deviceNames [string[]] [-deviceType [Microsoft.SqlServer.Management.Smo.DeviceType]] [-Overwrite [Switch]] [[-Differential] [Switch]]

Parameters:


Examples:
Photobucket - Video and Image Hosting

There are times that you do not know all the device types available,
then simply enter invalid device type
A great feature of PowerShell is that PowerShell will enumerate DeviceType
and kindly give you list of valid device type names when invalid type is entered
[^_^]PS[597]>Backup-Database -dbName:pubs -deviceNames:'d:\bak\pubs.bak' -deviceType:DontKnow
Backup-Database : Cannot convert value "DontKnow" to type "Microsoft.SqlServer.Management.Smo.DeviceType" due to invali
d enumeration values. The possible enumeration values are "LogicalDevice, Tape, File, Pipe, VirtualDevice".
At line:1 char:73
+ Backup-Database -dbName:pubs -deviceNames:'d:\bak\pubs.bak' -deviceType:D <<<<>


Prerequisites:

  1. Microsoft SQL Server 2005 Management Objects Collection
    : You need to download and install one of SQLServer2005_XMO_.msi versions
  2. How to create $server variable: Refer to Scripting Database Objects using SMO (Updated) on SqlTeam.com



Source:
# author: Sung Kim
# Description: Create Full or Differential backups for
# SQL Server database with SQL Server Objects(SMO) using PowerShell
function Backup-Database {
param([string]$dbName = $(throw 'Enter Database name to backup'),
[string[]]$deviceNames = $(throw 'Enter device name(s)'),
[Microsoft.SqlServer.Management.Smo.DeviceType]$deviceType =
[Microsoft.SqlServer.Management.Smo.DeviceType]File,
[Microsoft.SqlServer.Management.Smo.Server]$server,
[switch]$Overwrite = $false,
[switch]$Differential = $false)

trap { break; }

# Load SMO assembly
$PRIVATE:SmoDir =
'C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies'
[System.Reflection.Assembly]::LoadFile(
"$PRIVATE:SmoDir\Microsoft.SqlServer.Smo.dll") | out-null

# Initialize backup object
$PRIVATE:bkp = New-Object Microsoft.SqlServer.Management.Smo.Backup
$PRIVATE:bkp.Database = $dbName
$PRIVATE:bkp.Initialize = $Overwrite
$PRIVATE:bkp.Incremental = $Differential

# Add backup devices to backup database to
foreach ($PRIVATE:deviceName in $deviceNames) {
$PRIVATE:bkpItem =
new-object Microsoft.SqlServer.Management.Smo.BackupDeviceItem(
$PRIVATE:deviceName,
[Microsoft.SqlServer.Management.Smo.DeviceType]$deviceType)
$PRIVATE:bkp.Devices.Add($PRIVATE:bkpItem)
}

if ($Overwrite) {
Write-Warning "Overwriting existing backup set"
}

$PRIVATE:bkp.SqlBackup($server)

if ($Differential) {
Write-Host -NoNewLine -F Blue "Differential Backup: "
}
Write-Host -NoNewLine "$($dbName) has been backed up to "
Write-Host -ForegroundColor Cyan -NoNewLine "$($deviceNames)"
Write-Host " successfully"
}


Tags :
 
Comments:
Hi Kim - I don't think this script is working for Powershell RC2; do u have an update?
 
This idea is mind blowing. I think everyone should know such information like you have described on this post. Thank you for sharing this explanation.Your final conclusion was good.
DOT NET Course Chennai
DOT NET Training Institute in Chennai
 
I think everyone should know such information as you have described on this post. Thank you for sharing this explanation. Your final conclusion was good.


Regards, DOT NET Training in Chennai

 
hello sir,
thanks for giving that type of information.
best digital marketing company in delhi
HP DesignJet T120 In Delhi
 
Very complex and informative. Wonderful blog.
 
Wow, thanks. I love seeing all the pictures. It makes it so easy to browse. I'm pinning this for letter review next year. Right now, we working our way through the alphabet using super heroes



Dot Net Training in Chennai | Dot Net Training in anna nagar | Dot Net Training in omr | Dot Net Training in porur | Dot Net Training in tambaram | Dot Net Training in velachery


 
Good Post! Thank you so much for sharing this pretty post, it was so good to read and useful to improve my knowledge as updated one, keep blogging.
Reactjs Training in Chennai |
Best Reactjs Training Institute in Chennai |
Reactjs course in Chennai

 
how do neon lights work ... A neon sign is an entirely different type of light display. ... As you may know, neon signs are made by taking an electric charge.
 
NordVPN Crack is a hack-proof, encrypted tunnel for online traffic to flow. Nobody can see through the tunnel and get their hands on your! NordVPN Crack
 
Crack. A crack is either used as a: noun: A piece of software used to figure out passwords using a dictionary attack. It can also be a piece of software or .Malwarebytes Anti Malware Premium Serial Key
 
Post a Comment



<< Home
Let's get lazy with PowerShell!

Name:
Location: Flushing, NY, United States

Experimenting with a different format of blogs...

Links
ARCHIVES
10/01/2005 - 11/01/2005 / 11/01/2005 - 12/01/2005 / 12/01/2005 - 01/01/2006 / 01/01/2006 - 02/01/2006 / 02/01/2006 - 03/01/2006 / 03/01/2006 - 04/01/2006 / 04/01/2006 - 05/01/2006 / 05/01/2006 - 06/01/2006 / 06/01/2006 - 07/01/2006 / 07/01/2006 - 08/01/2006 / 08/01/2006 - 09/01/2006 / 10/01/2006 - 11/01/2006 / 11/01/2006 - 12/01/2006 /


Powered by Blogger