Microsoft SQL Server Backup and Recovery for LiveCycle ES

This article outlines methods for performing backup and recovery of a Microsoft SQL Server database. This article is not intended as a comprehensive technical guide of specific database backup and recovery features, but is meant to be an outline of basic SQL Server commands needed for a database administrator to create a reliable database backup strategy for LiveCycle ES.



Caution: As with any other aspect of your LiveCycle ES implementation, your backup and recovery strategy needs to be developed and tested in a development or staging environment before being used in production in order to ensure that the entire solution is working as expected with no data loss.

The information provided in this article is a summary of the steps required to perform the tasks described. For detailed information, consult the manufacturers documentation provided with your database or visit www.msdn.microsoft.com.

Database back up should be performed on a regular basis and is required after a creating new database or index, or when executing certain non-logged operations.

• Full database backup - backs up the entire database.

There are several backup methods, each with its own strength and weakness: You will need to weigh each aspect of the database system and decide the best method for your LiveCycle ES environment.

The size and frequency of data modification in the database will determine the time and resources required to implement a backup strategy. Once you have completed a full backup of the LiveCycle ES application data, you will need to decide which of the following backup methods you will use on a regular basis:

• Differential backup - backs up the changes since the last (full or differential) backup only.
• Transaction log backup - backs up transaction logs.
• Tail log backup - backs up the tail (or active) log.

• Mirrored backup - backs up the database to mirror sites.

In addition to the user databases, the system databases need to be backed up. This allows the system to be rebuilt in the event of system or database failure (if a hard disk fails, for example). Best practice suggests creating a separate maintenance plan for backing up the system databases that does not mix it with the user databases. Within that too, backup the master separately from the other system databases. This is because only full backups of master database are allowed.

The system databases in SQL Server are master, msdb, model, tempdb, and distribution. It is important to have regular backups of these system databases, however, it is not necessary to back up the tempdb system database because it is rebuilt each time SQL Server is started. When SQL Server is shut down, any data in tempdb is permanently deleted. For this reason, do not store any application-specific data in the tempdb database, leave it exclusively for use by SQL Server.

The model database needs to be backed up only if it is customized. Similarly the distribution database comes into picture only if the server is configured as a Replication Distributor.

The instructions in following sections assume that the database name is adobe and the target backup storage disk location is C:\LCBackup\MSSQL. If your environment is different, modify the commands as required.

The following command line scripts are used to back up the LiveCycle ES SQL Server database to the target backup storage disk. All commands are run from the SQL Server install directory (for example, C:\Program Files\Microsoft SQL Server\90\Tools\Binn\sqlcmd).

A LiveCycle ES database must be configured for FULL recovery so that in the event of system failure, a point in time recovery can be performed. The following script sets the recovery to full recovery mode.

USE master GO ALTER DATABASE adobe SET RECOVERY FULL WITH NO_WAIT GO

If you do not require point in time recovery and want to set the database to simple or bulk_logged mode then use one of the following commands:

-- to set to simple -- USE master GO ALTER DATABASE adobe SET RECOVERY SIMPLE WITH NO_WAIT GO -- to set to bulk_logged -- USE master GO ALTER DATABASE adobe SET RECOVERY BULK_LOGGED WITH NO_WAIT GO

This method backs up the entire database and is required before you can perform differential or transaction log backups.

-- run this only once to set the recovery mode to FULL and allow for point in time recovery -- USE master GO ALTER DATABASE adobe SET RECOVERY FULL WITH NO_WAIT GO -- run this daily to take the complete database backup -- BACKUP DATABASE adobe TO DISK=N'c:\LCBackup\MSSQL\adobe_full.bak' WITH NOFORMAT, INIT, NAME=N'adobe-Full Database Backup', CHECKSUM GO -- run this daily to verify the backup -- RESTORE VERIFYONLY FROM DISK=N'C:\LCBackup\MSSQL\adobe_full.bak' GO

The following result is returned:

Changed database context to 'master'. Processed 456 pages for database 'adobe', file 'adobe' on file 1. Processed 1 pages for database 'adobe', file 'adobe_log' on file 1. BACKUP DATABASE successfully processed 457 pages in 0.436 seconds (8.586 MB/sec). The backup set on file 1 is valid.

Differential database backups are cumulative backing up all the changes from the last full database backup;not the last differential backup.

-- Back up adobe as differential backup BACKUP DATABASE adobe TO DISK = N'c:\LCBackup\MSSQL\adobe_diff.bak' WITH DIFFERENTIAL GO

Note: Transaction log backups cannot be run under the simple recovery model discussed in Configuring the SQL Server database.

Two transaction logs cannot contain the same transactions, therefore you must restore each backup in the order they were performed.

-- Back up the adobe transaction log BACKUP LOG adobe TO DISK = N'c:\LCBackup\MSSQL\adobeLog.bak' GO

A tail log backup is the last transaction log backup made prior to restoring a database. This method is required for point in time recovery. If your data file (MDF) is unavailable, you need to use the WITH NO_TRUNCATE option, as shown below.

-- Back up the adobe tail-log BACKUP LOG adobe TO DISK = N'c:\LCBackup\MSSQL\adobeTailLog.bak' WITH NO_TRUNCATE GO

If your database is in either OFFLINE or EMERGENCY state, then a tail log backup cannot be performed.

A mirrored backup simply writes the backup to more than one destination thereby increasing the possibility of a successful recovery if a backup media gets corrupted. You can write up to four mirrors per media set. The following script backs up to two destinations:

-- make one backup on d: disk and a mirror on e: disk BACKUP DATABASE adobe -- media family 1 TO DISK = 'd:\LCBackup\MSSQL\adobeMirror_1.bak' MIRROR -- media family 1 TO DISK = 'e:\LCBackup\MSSQL\adobeMirror_2.bak' -- create a new mirrored backup set WITH FORMAT; GO

Depending on the method used to back up the database, there can be two different approaches to the recovery process:

• Simple recovery - restores the last full database backup only.

• Full recovery - restores all backups including full, differential, and transaction logs.

The following command shows how to restore the database to the time the last full database backup command was executed.

RESTORE DATABASE adobe FROM DISK = N'C:\LCBackup\MSSQL\adobe_full.bak' WITH FILE=1, RECOVERY GO

Note: Using RECOVERY in the script above assumes that the database no longer exists. If the database exists, REPLACE should be used instead. (See the SQL Server documentation.)

If you have multiple backups, you need to restore each of them in the same sequence as they were taken. You will perform the tasks in the following order:

Note: To apply multiple transaction logs, use the NORECOVERY option on all restore operations except the last.

Example

RESTORE DATABASE adobe FROM DISK = N'C:\LCBackup\MSSQL\adobe_full.bak' WITH FILE=1, NORECOVERY RESTORE DATABASE adobe FROM DISK = N'C:\LCBackup\MSSQL\adobe_diff.bak WITH FILE=1, NORECOVERY RESTORE LOG adobe FROM DISK = N'C:\LCBackup\MSSQL\adobe_log.bak WITH FILE=1, RECOVERY GO

To restore and recover a database to a particular point in time, mark, or log sequence number (LSN) before the point of a failure. For example, if a transaction changed some data incorrectly, you might want to restore the database to a recovery point just before the incorrect data entry. Any restore that specifies the recovery point for the database is known as a point-in-time restore.

The recovery point for a point-in-time restore is usually within a transaction log backup. This is the last backup used in the point-in-time restore sequence. When you apply this log backup, you can recover only transactions that come before that point by specifying the target recovery point in a STOPAT, STOPATMARK, or STOPBEFOREMARK clause. When recovery finishes, the time of the database is determined by the recovery point to which you recovered the primary file. Subsequent restores, if any, must have recovery points that are consistent with the database at that time.

The target recovery point is specified by using one of the following:

• A specific point in time within a transaction log (discussed here)
• A named mark that has been inserted into a transaction log record.

• A log sequence number (LSN).

Example

RESTORE DATABASE adobe FROM DISK = N'C:\LCBackup\MSSQL\adobe.bak' WITH FILE=1, NORECOVERY GO RESTORE LOG adobe FROM DISK = N'C:\LCBackup\MSSQL\adobe_log.bak WITH FILE=1, RECOVERY, STOPAT = N'01/14/2008 10:07:53' GO

This section provides references to articles relevant to database administration and back up tasks.

Note: The external web sites referenced here were valid at the time this article was published.

There are several good reference guides available from Microsofts web site located at Microsoft Developer Network. You can search for the following documents or articles from this site:

• SQL Server 2005 Documentation
• Introducing SQL Server Management Studio
• Considerations for the "autogrow" and "autoshrink" settings in SQL Server (article number 315512)
• A transaction log grows unexpectedly or becomes full on a computer that is running SQL Server (article number 317375)

• SQL Server administration best practices
• BACKUP and RESTORE in SQL Server -- Full Backups at www.sqlteam.com

• Selecting a SQL Server recovery model at http://searchsqlserver.com

• Ntbackup command reference for Windows Server
• Utilizing the Built-in Windows Backup (ntbackup.exe) for Windows at www.fishbrains.com