Description

The following information pertains to FootPrints 12 and later versions:

This article describes how to examine a SQL Server system and determine if backups are being done properly.  If they are not being done properly, this article identifies how to fix the situation.

Related Articles
How to Backup FootPrints 12 (for Disaster Recovery purposes)

Create a FootPrints 12 Backup Set for BMC FootPrints Support

Implement Database Re-indexing to Improve Performance


Resolution

SQL Server Recovery Backups
Database backups are done so that a recovery is possible in the event of disk loss or inadvertent damage to the database caused by accident or malice. The recovery consists of restoring the database from one or more backups to a point in time in the past.  Data that was added to the database after the last backup would be lost.  Therefore it is important to make database backups regularly and to use a method that will adequately protect against data loss.

SQL Server Data Files
The actual database data is stored in two places:  the database data files which have the current active rows of table data, and the database log files which have the transactions that have occurred against the database.  The database is usually one file named "{database}.mdf" but it can be many files.   The database file slowly grows as data is added to the database.

The transaction log should only be one file named "{database}.ldf", but it can be many files as well.  The log file can grow very large as data is inserted, deleted, and especially updated and it must be emptied out via backup or it will become a very large noticeable problem when disk space runs out.   In that case, in addition to emptying out the data in the transaction log file(s), it is suggested to shrink the file size(s) in order to free up disk space.  

However, this still presents a large problem if a good plan does not exist for recovering data in the event of a failure.

Types of Backups
SQL Server supports FULL, DIFFERENTIAL, and TRANSACTION LOG backups.  A backup is not just a copy of the "{database}.mdf" and "{database}.ldf".  It is a copy of all committed data as of the end of the execution of the backup.

A FULL backup file usually has a .BAK extension.  A full backup has all of the data that has been committed to the database as of the completion of the backup.  The database size remains unchanged.  The full database backup is fairly large, comparable to the size of the database itself because it contains all of the database table and index data and other data stored in the database.

A DIFFERENTIAL backup file is based on the most recent previous full backup.  It backs up all of the data changed since this last full backup.  It is like a full backup in that it is either restored or not, it cannot be restored to an arbitrary point in time.  It is usually used to make full recovery faster on large databases if frequent full backups take up too much space.

A TRANSACTION LOG backup file usually has a .TRN extension.  It moves all of the transactions that occurred since the last transaction log backup out of the database.  It does not reduce the size of the "{database}.ldf" file. That requires the shrink operation which should only be done on an as needed basis when the file is inappropriately large.  The transaction log backup files are sequential, and they must be applied to a database that is in restoring mode and has been restored from at least one full backup and possibly additionally one differential backup.  After that, each transaction log backup is applied in order, and each transaction in each file is applied in order until the "recovery point in time" is reached.  Typically, that is "most recent possible" which brings the database up to the last committed transaction.

SQL Server Recovery Modes
The default and most proper SQL Server Recovery mode is Full Recovery.  It provides 24x7 recovery capability.  It allows the transaction log to grow until it is backed up so that the transaction file backups can be used to restore the database to any point in time prior to the last transaction log backup.  Because transaction log backups are incremental and only contain the data inserted, updated, or deleted since the last backup, transaction log backups can be done very frequently, and are typically done every 10 minutes, or every hour, or every day depending on the need for recovery.  If there is no disk loss, then recovery is possible up to the last committed transaction, or any point in time.  If there is a disk loss, recovery is possible up to the time of the last transaction log that was backed up to another device.  This is why transaction log backups are done frequently. 

The other SQL Server recovery mode is Simple Recovery mode.  In this mode, the transaction log is truncated periodically (every checkpoint) and the transaction log never grows and cannot be backed up.  Therefore,  the database can only be restored using the last full or differential backup.

Virtual Machine (VM) Backups and Volume Shadow Copy
One of the features provided by VM backup solutions is the ability to perform a point-in-time restore of the virtual machine image, or even the files contained within the VM.  For a long time, file based backups of the live MDF and LDF files simply did not work, so a SQL Server VM could not simply be restored and be expected to work.  With current technology, the backup uses the Volume Shadow-copy Service (VSS) to perform volume-based snapshots of the virtual machine hard disks.  In that case, those backups are supported for recovery with SQL Server in a virtualized environment. 

While it is true that a restore can be done to a point in time, that point is simply the last snapshot backup point for the VM being backed up. Depending on the frequency of the backups that are occurring, this might meet business recovery requirements, but it doesn’t provide the same capabilities as native SQL Server backups in Full Recovery Mode.  If you are in Full Recovery Mode,  the VM backup will either fail to clear your transaction log, or it will back it up to a null file, so that recovery is only possible to one of the hard date times of the VM backup.

SQL Server System Health SQL Scripts
To help quickly identify the overall health of a FootPrints 12 SQL Server install, BMC provides SQL scripts that give information on common database setup and maintenance issues.  See the attachments to this article to obtain the scripts mentioned below.

10 Database Backup Status - This is a helpful SQL Script that will identify the backup situation. Running this script will help identify the databases that exist on the SQL Server and what actions are needed.

The script can be run in MS SQL Server Management Studio without any modification.

Open the 10 Database Backup Status script in a text editor or directly in a New Query window in MS SQL Server Management Studio.

This script has two result sets.  The first identifies the server name,  server version, and backup file directory.  The default directory C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup is shown for MS SQL Server 2014.  The same information can be obtained from the Server properties dialog.




These are not necessarily the locations of any files, it is just the default directory that will be suggested in the dialogs used to create database and log files, and the directory to backup database and log files.  

The default location is not a good place to store the backups permanently.  Unless a DBA has changed this location, it will be on the hard disk of the SQL Server computer itself.  It is usually more efficient to backup data directly over the network to a network share (not the same VM/computer, or another VM that might itself need backup).

The second result set shows the list of databases and their backup information.  A typical example is shown below.

Sample Run 1 - Never Backed Up




So this database has had no maintenance done.  The transaction file has not yet grown to consume all remaining space on the C: drive, but we are headed that direction.

The lack of Full backup should be remedied first, because the database is in danger of being lost.  However, the database may have already stopped working because the log is full.

Scenario 1a – the transaction file LOG MB is very large and the disk is nearly or completely full

The transaction log needs to be emptied and space needs to be freed up to get the system in good health.  If the transaction log is very large, it may not be desirable or possible to obtain the disk space and time needed to back it up.  It may be desirable to truncate the log by switching the database to simple recovery mode and shrinking the log file.  These steps can be followed:

• Choose a fast network location to save the files.  This should be a SAN or File Share not on the same computer
• Change the database to simple recovery mode
• Shrink the log file to 10% of the database file size
• Switch the database back to full recovery mode
• Take a Full backup
• Implement Regular Full and Log backups
• Implement Regular Reindexing

These steps are outlined in other maintenance articles.

20 List Backup files in Server directory

This script lists MS SQL Server backup files in the default backup directory OR if backup files are stored in a location other than the default, that can be specified in the script using the instructions below.  The script can be run in MS SQL Server Management Studio.  The script makes no modification in MS SQL Server or any of its databases.

1. Download the 20 List Backup files in Server directory script which is attached to this article
2. If the backup files are stored in the default location (C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup for MS SQL 2012), no modification needs to be made to the script.
3. If the backup files are stored in an alternate location, perform the following steps:
   1. Open the 20 List Backup files in Server directory script in a text editor or directly in a New Query window in MS SQL Server Management Studio.
   2. Locate the line which contains the following (line 8):

3. Place the alternate directory path inside the single-quote characters (immediately after the "=" symbol).  For example, if the alternate backup location for MS SQL Server backups is C:\DATA\MyBackupFiles, the line would look as follows: