Backup db with PS Script

HI , 

After many trails , finally ended up here.

Could you please help me with working script on how to take Db back of SQL 2012 with a simple Poweshell script ? Working one

Thanks,
Abraham

April 20th, 2015 12:58pm

This one I just tested:

$BackupPath = "F:\Backup"
$ServerName = "Localhost"
$DatabaseServer = "localhost"

# little helper function to ping the remote server.
function Get-ServerOnline ([string] $server) 
{
    $serverStatus = (new-object System.Net.NetworkInformation.Ping).Send($server).Status -eq "Success"
    $dt = get-date -format yyyyMMddHHmmss
    Write-Host "$dt - Server Online: $serverStatus"
    return $serverStatus
}

# Load the SQL Server SMO Libraries
$assemblyInfo = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
if ($assemblyInfo.GetName().Version.Major -ge 10)
{
    # sql server version is 2008 or later, also load these other assemblies
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | Out-Null
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement') | Out-Null
}

$dt = get-date -format yyyyMMddHHmmss
Write-Host "$dt - Connecting to Database Server $ServerName"

if (get-serveronline($ServerName))
{
    $s = new-object (Microsoft.SqlServer.Management.Smo.Server) $DatabaseServer

    $dbs = $s.Databases

    foreach ($db in $dbs)
    {
        if($db.Name -ne tempdb) # We dont want to backup the tempdb database
        {        
            $DatabaseName = $db.Name
            
            if (!(Test-Path -path $BackupPath\$DatabaseName\))
            {
                $dt = get-date -format yyyyMMddHHmmss
                Write-Host "$dt - Creating the Backup Directory for $DatabaseName"
                New-Item $BackupPath\$DatabaseName\ -type directory | Out-Null
            }
            
            $dt = get-date -format yyyyMMddHHmmss
            Write-Host "$dt - Starting the Backup of the Database $DatabaseName"
            
            $dt = get-date -format yyyyMMddHHmm # We use this to make a file name based on the timestamp
            $dbBackup = new-object (Microsoft.SqlServer.Management.Smo.Backup)
            $dbBackup.Action = Database
            $dbBackup.Database = $DatabaseName
            $dbBackup.Devices.AddDevice($BackupPath +"\" + $DatabaseName + \ + $DatabaseName + _Database_ + $dt + .bak, File)
            $dbBackup.SqlBackup($s)
        }
    }
    
    $dt = get-date -format yyyyMMddHHmmss
    Write-Host "$dt - Database Backup Complete"
}

http://www.virtualrealm.com.au/mykre/Powershell-Script-to-Backup-all-SQL-Server-Databases-on-a-Server

April 20th, 2015 1:19pm

Hi Dave,

Many thanks for your quick turn around. 

I have tried your second script and It was working like a charm. But unfortunately its taking back up of all the databases, except tempdb.

But my requirement is to take specific Db as back up eg. TestDb . How can I modify the script to take back up of only 1 Db. 

Request you to please help me.

Thanks,

Free Windows Admin Tool Kit Click here and download it now
April 21st, 2015 8:59am

$BackupPath = "F:\Backup"
$ServerName = "Localhost"
$DatabaseServer = "localhost"
$DatabaseName = "TestDb" 

# little helper function to ping the remote server.
function Get-ServerOnline ([string] $server) 
{
    $serverStatus = (new-object System.Net.NetworkInformation.Ping).Send($server).Status -eq "Success"
    $dt = get-date -format yyyyMMddHHmmss
    Write-Host "$dt - Server Online: $serverStatus"
    return $serverStatus
}

# Load the SQL Server SMO Libraries
$assemblyInfo = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
if ($assemblyInfo.GetName().Version.Major -ge 10)
{
    # sql server version is 2008 or later, also load these other assemblies
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | Out-Null
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement') | Out-Null
}

$dt = get-date -format yyyyMMddHHmmss
Write-Host "$dt - Connecting to Database Server $ServerName"

if (get-serveronline($ServerName))
{
    $s = new-object (Microsoft.SqlServer.Management.Smo.Server) $DatabaseServer

        
           
            
            if (!(Test-Path -path $BackupPath\$DatabaseName\))
            {
                $dt = get-date -format yyyyMMddHHmmss
                Write-Host "$dt - Creating the Backup Directory for $DatabaseName"
                New-Item $BackupPath\$DatabaseName\ -type directory | Out-Null
            }
            
            $dt = get-date -format yyyyMMddHHmmss
            Write-Host "$dt - Starting the Backup of the Database $DatabaseName"
            
            $dt = get-date -format yyyyMMddHHmm # We use this to make a file name based on the timestamp
            $dbBackup = new-object (Microsoft.SqlServer.Management.Smo.Backup)
            $dbBackup.Action = Database
            $dbBackup.Database = $DatabaseName
            $dbBackup.Devices.AddDevice($BackupPath +"\" + $DatabaseName + \ + $DatabaseName + _Database_ + $dt + .bak, File)
            $dbBackup.SqlBackup($s)
  
    $dt = get-date -format yyyyMMddHHmmss
    Write-Host "$dt - Database Backup Complete"
}
April 21st, 2015 9:07am

Thanks Dave.

Its working like a charm :) 

Thanks,

Free Windows Admin Tool Kit Click here and download it now
April 24th, 2015 3:43am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics