Click an Ad

If you find this blog helpful, please support me by clicking an ad!

Thursday, February 20, 2014

MS SQL Server Backups - Going from Simple to Full Recovery Model

We used to just back up our databases nightly, but we .... ahem... had an issue... and lost some data, so we decided to start doing transaction log and differential backups on a few key databases.

After we identified which databases that we needed to protect, we worked out the following process on a test SQL server using the almighty AdventureWorks database. Microsoft distributes this fully populated test database for SQL admins and devs to play with.

You can find the option to switch the recovery model by right-clicking on the database, selecting properties, and then choosing options.

Important: when switching between Simple and Full recovery models, you have to perform a full backup before the database will start using the transaction log files. If you don't make this switch prior to setting up the subplans (see below), you will not be able to set up the transaction log backup subplan. Also, after switching to transaction logs, you should have a good process in place to monitor free space wherever your files are stored. But you already have that, right?

The schedule that worked for us:
1. Full Backup nightly at 12am.
2. Differential backup at noon and again at 6pm.
3. Transaction Log Backup every 2 hours, starting at 1AM (so on all odd hours: 1,3,5,7, etc).

In SQL Server Management Studio (SSMS), I created a maintenance plan with four subplans:
Full Backup
Diff at noon
TLog every 2 hours
Diff at EOD

For each subplan, I chose the appropriate backup type (full, diff, tlog) and chose the databases. I selected the radio button to "Create a backup file for every database", and chose the D:\DB_Backups path. I then checked the "Verify backup integrity" option and set the drop-down to "Compress Backup".

Set the schedule as required, of course.

Now, we need to take a little detour to create a SQL Server Agent Job. This job has one step:
C:\Windows\System32\WindowsPowerShell\v1.0\PowerShell.exe -File C:\PS\DB_Backup_DR_Mirror.ps1


Go back to your maintenance plan, and for each subplan you created, drag an "Execute SQL Server Agent Job Task" from the "Maintenance Plan Tasks" toolbar over into the process flow area. Connect the green arrow from your Backup database task to this box, and then set the box to run the job you created to run the Powershell script.

Now, you may be wondering what this Powershell script does!

Its job is to copy the backup files created off-SAN and off-site. My SQL server lives in a VMware environment on a Dell SAN. If the SAN died, I want access to my backups. If the building got hit by an airplane, I want off-site copies. Typical DR.

Here's the script:

#Initial Variables that you will modify on a per-case basis
$SQLServerName = "SQLSERVER1"
$DBName = "ImportantDatabase"

#Variables need that automatically generate
$LocalBackupPath = "D:\DB_Backups\$DBName\*.*"
$OffSANCopyLocation = "\\OnsiteNAS\SQLBackup\$SQLServerName\$DBName"
$OffSiteCopyLocation = "\\OffsiteNAS\SQLBackup\$SQLServerName\$DBName"

#Copy to Local NAS (Off-SAN)
Copy-Item -Path $LocalBackupPath -Destination $OffSANCopyLocation -Force

#Copy to Remote NAS (Off-Site)
Copy-Item -Path $LocalBackupPath -Destination $OffSiteCopyLocation -Force

#Remove copies on the SQL Server itself
Remove-Item -Path $LocalBackupPath -Force

#For off-site location, get the creationtime and delete anything older than 7 days
$Files = (Get-childitem $OffSiteCopyLocation)
Foreach ($file in $files){
$FileAge = ((get-date) - ($file.creationtime)).totaldays
If ($FileAge -gt 7){
remove-item $File.FullName -Force
} #End If
} #End Foreach

#For off-SAN location, get the creationtime and delete anything older than 7 days
$Files = (Get-childitem $OffSANCopyLocation)
Foreach ($file in $files){
$FileAge = ((get-date) - ($file.creationtime)).totaldays
If ($FileAge -gt 7){
remove-item $File.FullName -Force
} #End If
} #End Foreach

There at the end, I'm deleting copies older than 7 days. Since I'm writing to tape on a weekly basis, if someone needs an old copy of the database it's easy enough to pull.

No comments:

Post a Comment