Purpose

This document will provide you some not very common knowledge about SQL backup. I presume that at least you know how to do backup in SQL Server.  Microsoft official documentation for SQL Server Backup is found on this link.


Case Study: Database Back-up  Real World Problems

You work on CORP1 as a database administrator. The most common problem
in the company is that most of the time they  run out of disk space for database backup.
The problem with backup is that, Corp1 has a total of 100TB (terabytes)
of data. If you back up the database daily you'll end up with 1000TB(terabytes) of
for just 10 days of Full database backup.

You are to design a backup strategy in such a way that you only make
use of necessary hard disk space for backup as possible. You need to "compress" your
backup. During database change request you need to do emergency backup in such a way that it does not
disturb the existing back-up and restore Strategy. You are also required to automatically clean up the backupset
that are past the retention period.

Presentation #1: Compressing the Backup Set.

One way to conserve diskspace for backup set is to compress it.  In the olden days, way back SQL server 2000
This can be implemented by creating the backup set and then compressing it with Winzip.
To date with sql server 2008 you can now backup to compressed format.  Figures below demonstrate
how to do it and a comparison of compressed and uncompressed backup files

Figure 1. Compressing backup



Figure 2. Comparison of Compressed and Uncompressed Back-up files.

Presentation #2.  Creating an emergency backup for DBCR

As part of the company's policy you are required to backup the database  as part of fallback procedure
during database change request (dbcr). However the backup set created for DBCR would not be a part of
the companies over all backup and restore strategy. You can do this by making a copy-only back up as shown
below:

Figure 3.  Copy-only backup

Presentation #3. Deleting SQL Server backup files that are Beyond Retention.

To conserve back-up space you will need to delete backup set that are beyond retention days. For corp1 for example your retention
is limited to 5 days. All backup set that are beyond retention must be deleted automatically.

This can be achieve by running a Vb Script file. For the implementation below,  I would like to give the credit
to where the credit is due and the script below is available on this link.

This script will delete all files that are beyond 3 days old.  For the sake of documentation i would like to repost the code here.

    ‘ ################################################################
    ‘ # cleanup-folder.vbs
    ‘ #
    ‘ # Removes all files older than 1 week
    ‘ # Authored by Spencer Kuziw (s.kuziw-at-epic.ca)
    ‘ # Based on code by YellowShoe
    ‘ # Version 1.0 - Sept 23 2008
    ‘ ################################################################
    
    Dim fso, f, f1, fc, strComments, strScanDir
    
    ‘ user variables
    ‘ —————————————————————-
    
    strDir = “C:\test”
    strDays = 3
    
    ‘ DO NOT EDIT BELOW THIS LINE
    ‘ (unless you know what you are doing)
    ‘——————————————————————
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set f = fso.GetFolder(strDir)
    Set fc = f.Files
    For Each f1 in fc
          If DateDiff("d", f1.DateCreated, Date)> strDays Then
                ‘strComments = strComments & f1.name & ” “ & f1.DateCreated & vbCrLf
                fso.DeleteFile(f1)
          End If
    Next
    
    ‘wscript.echo strComments
    WScript.Quit
    ‘ eof

Script 1. Clean-up Script

You have to save this code as a .VBS and to schedule it to run automatically. You can schedule it as
SQL Jobs or you can Schedule it using windows scheduler.