Help required in automating backup for SQLServer 2008 R2

Hi,

I am new to the forums and thank you all for the great info posted.

I have hosted a website with discount asp with a SQLServer 2008 R2 DB. Is there any tool avalible to automate the backup creation at specific interval. What I see in the control panel is a manual tool to create a DB backup.

Is there any tools avaliable. Please advice.

Thank you
Dama

 

The easiest way is to use Powershell. I have a script that creates the backup and restores it locally that I use all of the time. I just made the change to call the API to automate the backup (I was using the Control Panel to create the backup before). Unfortunately, the new version doesn't let me .zip up the backup, but the database I am dealing with is not too large.

$databaseName = "SQL2008R2_xxx_xxx"
$userId = "ftp-user-id"
$password = "ftp-password"
$apiKey = "zzzzzzzzzzzzzz"

$ftpFileUrl = "ftp://ftp.xxxx.webyyy.discountasp.net/_database/$($databaseName)_backup.bak"
$localDir = "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup"
$localZip = "$localDir\$($databaseName)_backup.zip"
$localBackup = "$localDir\$($databaseName)_backup.bak"
$proxyUrl = "https://api.discountasp.net/1.0/customerapi.asmx?WSDL"


function Resolve-Error ($ErrorRecord=$Error[0])
{
$ErrorRecord | Format-List * -Force
$ErrorRecord.InvocationInfo |Format-List *
$Exception = $ErrorRecord.Exception
for ($i = 0; $Exception; $i++, ($Exception = $Exception.InnerException))
{ "$i" * 80
$Exception |Format-List * -Force
}
}


function CreateBackup($apiKey, $databaseName)
{
$proxy = New-WebServiceProxy -uri https://api.discountasp.net/1.0/customerapi.asmx?WSDL
if ($proxy.VerifyKey($apiKey))
{
Write-Host "Creating backup for $databaseName"
$proxy.Sql2008R2CreateBackup($apiKey,$databaseName)
}
else
{
throw "Unable to verify API key with Discount.Net"
}
}

function RemoveLocalFile($file)
{
if (Test-Path -LiteralPath $localZip)
{
Remove-Item $localZip
}
if (Test-Path -LiteralPath $localBackup)
{
Remove-Item $localBackup
}
}


function DownloadFile($ftpFileUrl, $userId, $password, $localZip)
{
Write-Host "Downloading $ftpFileUrl"
$webClient = New-Object System.Net.WebClient
$webClient.Credentials = New-Object System.Net.NetworkCredential $userId,$password
$webClient.DownloadFile($ftpFileUrl, $localZip)
}

function DeleteFtpFile($ftpFileUrl, $userId, $password)
{
Write-Host "Deleting $ftpFileUrl"
$request = [system.Net.FtpWebRequest]::Create($ftpFileUrl)
$request.Method = "DELE"
$request.Credentials = New-Object System.Net.NetworkCredential $userId,$password
$response = $request.GetResponse()
Write-Host "Delete status:" $response.StatusDescription
$response.Close();
}


function UnzipFile()
{
Set-Location $localDir
& "C:\Program Files\7-Zip\7z.exe" x -y `"$localZip`"
}


function Restore-Database()
{
#load assemblies
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
#Need SmoExtended for backup
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null

#get backup file
#you can also use PowerShell to query the last backup file based on the timestamp
#I'll save that enhancement for later
$dbname = "Lighthouse"

#we will query the db name from the backup file later

$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "(local)"
$backupDevice = New-Object ("Microsoft.SqlServer.Management.Smo.BackupDeviceItem") ($localBackup, "File")
$smoRestore = new-object("Microsoft.SqlServer.Management.Smo.Restore")

#settings for restore
$smoRestore.NoRecovery = $false;
$smoRestore.ReplaceDatabase = $true;
$smoRestore.Action = "Database"

#show every 10% progress
$smoRestore.PercentCompleteNotification = 10;

$smoRestore.Devices.Add($backupDevice)

#read db name from the backup file's backup header
#$smoRestoreDetails = $smoRestore.ReadBackupHeader($server)

#display database name
#"Database Name from Backup Header : " + $smoRestoreDetails.Rows[0]["DatabaseName"]

$server.KillAllprocesses($dbname)

$smoRestore.Database = $dbname

#restore
$smoRestore.SqlRestore($server)
Write-Host Done
}


function Restore-DatabaseFromWeb
{
#CreateBackup $apiKey $databaseName
RemoveLocalFile $localBackup
DownloadFile $ftpFileUrl $userId $password $localBackup
Restore-Database
}

Export-ModuleMember -Function Restore-Database,Restore-DatabaseFromWeb