Automated SQL2008 Backups

The process of automating MSSQL 2008 backups is possible by combining a scheduled task and a Powershell script. The Powershell script takes a CSV file as a command line argument. This file contains a header on the first line, followed by the database name and FTP information for each customer requiring an automated backup. On a server where automation is not yet configured, two scheduled tasks are required: daily and weekly. Each scheduled task will run the same Powershell script, but the script will use a different CSV file. Once the configuration is complete, the server will run the scheduled task (daily or weekly) and all customers on the server requiring automated backups will be updated through the execution of a single script.   

1. First, ensure that Powershell is installed on the server.  If it is not under C:\Windows\system32\WindowsPowerShell\v1.0\powershell.exe, it will need to be installed via http://support.microsoft.com/kb/968930.
2. Next, ensure that C:\backups and C:\scripts exist on the server.  If not, create the directories.  
3. Within C:\scripts, create the following files:
   • sqlbackup.ps1 - This file contents are below:
   • sqlbackup_daily.csv
   • sqlbackup_weekly.csv 

   #################################

   # This script makes a backup #

   # of listed databases and #

   # uploads the backup file to  #

   # the web server #

   #################################

   #clear screen

   cls

    

   #name of script file - used for logging

   $scriptName = "sqlbackup.ps1"

    

   #Default Path for Database Backups

   $backup_path_base = "C:\backups\"

    

    

   #Default location for script log file

   $logFile = "c:\scripts\sqlbackup.log"

   $logFileSizeLimit = 100 #in KB

    

    

   #function to output to host and write to log file

   function logOutput([string]$text){

    $tmpTS = Get-Date

    Write-Host "$tmpTS | $text"

    Write-Output "$tmpTS  | $text" | Out-File $logFile -Append -NoClobber

   }

    

    

   #Check to make sure our log file exists, and make sure its not oversized, if not create it

   if((Test-Path $logFile)) {

   $tmpLogFile = Get-Item $logFile

   if($tmpLogFile.length / 1024 -gt $logFileSizeLimit){

   Remove-Item $logFile

   }

   }

   if(!(Test-Path $logFile)) {

   #log file does not exist, lets create it.

   try { 

   New-Item $logFile -type file | Out-Null

           logOutput("New Log file created")

   } catch [Net.WebException] {

        Write-Host "Error creating log file"

   }

   }

    

   #Starting work

   logOutput("$scriptName running $args[0]")

    

    

   #import list of databases and Remote FTP information

   #CSV file is in the following format

   #database,ftp_server,ftp_user,ftp_password,ftp_path

   #the CSV file must contain the following as the first line: database,ftp_server,ftp_user,ftp_password,ftp_path

   try {

   $list = Import-Csv $args[0] #CSV File Name is passed via command line

   } catch [Net.WebException] {

   logOutput("Import Csv error. Check your argument syntax")

   }

    

    

   #loop through csv and create backup/ftp to customer's site

   foreach($entry in $list){

   #Database to backup

   $db_to_backup = $entry.database

    

   #FTP Information

   $remote_server = $entry.ftp_server

   $ftp_username = $entry.ftp_user

   $ftp_password = $entry.ftp_password

   $ftp_path = $entry.ftp_path

    

   #full path for database backup

   $backup_location = $backup_path_base + "\" + $db_to_backup

    

   #check to see if the database backup directory exists

   if(Test-Path $backup_location){

   #directory exists...move along

   } else {

   #create missing directory

   New-Item -Path $backup_location -type directory | Out-Null

   }

    

   [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

    

   #create a new server object

   $server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "(local)"

   $db = $server.Databases[$db_to_backup]

   $db_name = $db.Name

    

   #generate timestamp for backup file name

   [string]$timestamp = Get-Date -format yyyyMMddHHmmss

    

   #Set file name of Backup File

   $backup_file_name = $db_to_backup + "_" +$timestamp + ".bak"

    

   try{

   #Backup the database

   $smoBackup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")

   $smoBackup.Action = "Database"

   $smoBackup.BackupSetDescription = "Full Backup of " + $db_to_backup

   $smoBackup.BackupSetName = $db_to_backup + " Backup"

   $smoBackup.Database = $db_to_backup

   $smoBackup.MediaDescription = "Disk"

   $smoBackup.Devices.AddDevice($backup_location + "\" + $backup_file_name, "File")

    

            logOutput("Backing up Database $db_to_backup")

    

   $smoBackup.SqlBackup($server)

   logOutput("successfully created temporary backup file: $backup_location" + "\" + "$backup_file_name")

    

   try{

   # Create a FTPWebRequest

   $ftp_uri = "ftp://" + $remote_server + $ftp_path + "/"

   $webclient = New-Object System.Net.WebClient

   $webclient.Credentials = New-Object System.Net.NetworkCredential($ftp_username,$ftp_password)

   $uri = New-Object System.Uri($ftp_uri + $backup_file_name)

    

   logOutput("Uploading file $backup_file_name")

    

   $webclient.UploadFile($uri, $backup_location + "\" + $backup_file_name)

   } catch [Net.WebException] {    

   logOutput("FTP File upload failed")

   }

   }catch [Net.WebException] {

        logOutput("Database backup did not complete properly")

   }

    

    

   #Cleanup: Remove temporary backup file

   try{

   Remove-Item $backup_location"\"$backup_file_name

   logOutput("successfully removed temporary backup file: $backup_location" + "\" + "$backup_file_name")

   } catch [Net.WebException] {

   logOutput($_.Exception.ToString())

   }

   }

    

   #Were Done!

   logOutput("$scriptName Completed")

   Write-Output " " | Out-File $logFile -Append # space our entries

   Write-Output " " | Out-File $logFile -Append # space our entries
4. Open both CSV files and add the following header:  database,ftp_server,ftp_user,ftp_password,ftp_path
5. Save the files.
6. Next, configure the scheduled tasks.  Navigate to Start > Control Panel > Administrative Tools > Task Scheduler Library.  Right-click Task Scheduler Library and select Create Task.
7. Name the scheduled task sqlbackup_daily or sqlbackup_weekly .
8. Click Change User or Group and set the user to SYSTEM.

9. Go to the Triggers tab and schedule the task for a specific time.  
10. Go to the Actions tab now. The action should be Start a Program.  Program/Script should be C:\Windows\system32\WindowsPowerShell\v1.0\powershell.exe.  The arguments would b the Daily and Weely scripts: C:\scripts\sqlbackup.ps1 C:\scripts\sqlbackup_daily.csv and C:\scripts\sqlbackup_weekly.csv
11. Start in C:\scripts.

12. Click OK and the task will run at the specified time(s).