Design

  1. Select database
  2. Backup
    1. Check rights; backup operator or more.
  3. Verify
    1. Verify backup
    2. Check archive
  4. Create restore script
  5. Archive backupfile
  6. Cleanup on old backup files

Output is send to StdOut stream.

Use T-SQL in SQL Server Agent or PowerShell with ADO.NET.

Only SQL Server native compression is used. This is to ensure that a database can be restored on a SQL Server installation without this solution.
Only default is used. This is to ensure that a database can be restored on a SQL Server installation without this solution.

The solution should be secured against malicious change.

Backup file name

<database name>_<backup type>_<timestamp>.<filetype>
  • <database name>: Full name of the database
  • <backup type>: { “FULL” | “DIFF” | “LOG” }
  • <timestamp>: UTC timestamp on the start of the backup process. Format by ISO 8601 is yyyy-mm-ddThhmmss_<seven digits>, i.e. 2010-03-17T213657_8987964
  • <filetype>: Full backup has the file type “bak”, differential backup the type "dif" and log backup has the file type “trn”. { “bak” | "dif" | “trn” }

Components

Select database

Select databases to backup based on parameter value. This could have syntax like Ola Hallengrens solution.
The selection should be able to take wildcards as selection parameter.

Backup database

Backup a given database to a unique backup file.
The backup statement and its execution context should be logged.

Backup encryption key

The Master Key and each Database Key is backed up til a Unique backup file. The passwords for the backups are generated as secure passwords and written to Unique text files on a location different from the backup files.

Generate restore script

Generate restore script on each database. The script should use the files available on the local storage.
Scripts to restore encryption keys does not contain password.

Validate backup

Restore database using backup and generated restore script.

Archive backup file

Archive backup file to an external archive system like IBM Tivoli Storage Manager (TSM) or CommVault.

Report backups

Make summary report on all backups. The reports should be on all facets like database backup history, databases on a given server and all databases.

Backup file housekeeping

Delete backup files older than a given period. Consider if the file is archived.
Also obsolete restore scripts should be deleted.

Last edited Feb 14, 2014 at 8:33 AM by NielsGrove, version 8

Comments

No comments yet.