Free Online Courses for Software Developers - MrBool
× Please, log in to give us a feedback. Click here to login
×

You must be logged to download. Click here to login

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

Backup and restore strategies – Storing the Backup on Tape – Part 2

In this article we will teach you how to record a database backup on tape to a SQL database.

The need to perform backups in databases is becoming increasingly crucial for businesses due to the large amount of information stored. Because of this large amount of information is no longer feasible to store the backup on a hard disk, it is necessary to choose other alternatives for backup storage. One of the most widely used alternatives to the hard disk storage is the use of tapes, which allows for storage of large amounts of information in a reliable, fast access and with an excellent cost/benefit ratio. Yet, few companies use this technology because they don’t know its benefits and do not find detailed information on this subject in books and databases courses.

In this article we will discuss the requirements, technologies, strategies and recommendations for the recording of a database backup on a tape. In addition to presenting details about the recommended hardware and software this article also indicate two hardware solutions for the storage of tape backup.

This last part of the article covers four backup strategies that can be employed with Microsoft SQL Server 2008 regarding the backup. The strategies are discussed and explained according to the need of database backup.

Backup strategies

Once the hardware and software features have been defined you need to plan a backup strategy that is consistent with the characteristics of the company's database. Unfortunately it is not possible to suggest a generic strategy because as each database has unique properties, features, options and features. Therefore, you need a deeply knowledge of these features to build a proper backup strategy.

The strategy should be aligned with the capabilities of the hardware and software chosen. Usually the IT staff make the following division: all the commands that create the backup are implemented with the tools of their own database, which stores data on a local hard disk to later be read and copied into the tape. The generation of files from the backup database is the first step: after that the specific tape backup software will be used to copy the files to the tapes. This division must be synchronized to prevent the situation where a backup is not recorded on tape or that the tape store a backup that should not be recorded.

Planning a backup strategy is a complex task that must take into account many factors beyond the characteristics of the database. This planning should consider the performance of the server, the frequency, types of backup, the personal responsible for the backup, local storage of the tapes, whether external or not, procedures, processes, techniques and actions to be taken before, during and after backup and restore.

To provide a starting point for creating backup strategies this article will present four general types of backup strategies based on SQL Server 2008. Each one is suitable for a specific scenario and apating of the commands suggested in the four strategies the reader can make up their own backup solution for his/her environment.

Strategy 1

The first backup strategy is based on a database of small size that is not used frequently. Examples of databases used in this strategy are those that have simple entries that are not updated constantly, as a record of public parks or type of employees of a company. Due to these characteristics it is reasonable to set up a backup strategy that makes only a full backup of the database daily, preferably in a period that the database is not being accessed. In this strategy can use two sets of tapes that will be rotated every week.

Full backup: A full SQL Server backup reads all the objects, structures, and data stored in data files and transaction log. If this backup is restored, the entire contents of the database will overwrite the contents of the current database. This type of backup typically takes a considerable amount of time to be done and to be restored, because it almost makes a full copy of the database. Every kind of restoration of databases in SQL Server needs to be initiated by a full backup.

        

The recovery time of this strategy is fast, but it is possible that some information is lost according to the problem that occurred. Listing 1 shows the Transact-SQL commands that can be used as a base to mount this type of strategy.

-- ***************************

-- STRATEGY 1:

-- ***************************

-- ONE FULL BACKUP PER DAY

-- RECOVERY: ONLY THE FULL BACKUP

-- RECOVERY TIME: SHORT

-- USE: SMALL DATABASES

-- OBS: FORMAT THE BACKUP DEVICES ONCE A WEEK

-- EXAMPLE:

-- BACKUP DEVICE

EXEC SP_ADDUMPDEVICE 'DISK','STRATEGY1_FULL','C:\BACKUP\ST1_FULL.BAK'

GO

-- EVERY DAY

BACKUP DATABASE DB_BACKUP

TO STRATEGY1_FULL

-- RECOVERY

RESTORE DATABASE DB_BACKUP

FROM  STRATEGY1_FULL

WITH FILE = ...

Listing 1. Transact-SQL commands used in the backup strategy 1.

Listing 1 begins by presenting the command that adds a backup device in SQL Server. This device creates an object in SQL Server that points to a file stored on local hard drive. In Listing 1 the device is called STRATEGY1_FULL and points to the file C: \ BACKUP\ST1_FULL.BAK. The use of backup storage devices in SQL Server is designed to facilitate and organize and it is possible to record more than one backup in a single device. Then we have the command that does a full database backup. Finally, the reader can see the command that restores the backup, where the FILE option must be filled with the number that was stored inside the device. This number is generated sequentially according to the number of backups stored on the device.

Strategy 2

The second strategy is based on small or medium  database that is changed constantly, i.e. it is updated every minute. As an example of a target database for strategy 2 we mention a telemarketing system or a system of a store. In this scenario there is the need for a strategy that allows the least amount of possible loss of information, because in this scenario is not possible to retype the information or the cost of this retyping is prohibitive.

For this strategy it is recommended to use a full backup once a day (in a time where the database use is minimal) and a transaction log backup every hour. Thus, the process of restoring from backup will consist of restoring the last full backup and all transaction log backups to the desired point. In addition, the use of this type of backup allows backup the option point in time, i.e. it allows you to specify up to date, with accuracy to the second, the data you want to restore from backup.

Transaction Log Backup: A transaction log backup only reads information from the transaction log, which takes much less time to make a backup of the entire database. Thus, in a database that is heavily modified onbe can make several daily backups of the transaction log without performance problems. Because the log contains only the changes made to the data, if the backup is restored only those changes will be applied to the data. After performing a this type of backup the transaction log is cleaned (except transactions that are still being updated).

In relation to the tapes, we recommend the use of four sets: two to store the full backups of the current and previous week and two to store the transaction log backups of the current and previous weeks. Listing 2 shows the Transact-SQL commands that can be used as a base to mount this type of strategy.

-- ***************************

-- STRATEGY 2:

-- ***************************

-- 1 FULL BACKUP FULL PER DAY + 1 T. LOG BACKUP  EACH HOUR

-- RECOVERY: LAST FULL BACKUP + CORRECT SEQUENCE OF T. LOG BACKUPS

-- RECOVERY TIME: MEDIUM

-- USE: SMALL OR MEDIUM DATABASES USED DAILY

-- OBS: FORMAT THE BACKUP DEVICES ONCE A WEEK

-- EXAMPLE:

-- BACKUP DEVICES

EXEC SP_ADDUMPDEVICE 'DISK','STRATEGY2_FULL','C:\BACKUP\ST2_FULL.BAK'

GO

EXEC SP_ADDUMPDEVICE 'DISK','STRATEGY2_TLOG','C:\BACKUP\ST2_TLOG.BAK'

GO

-- ONCE PER DAY

BACKUP DATABASE DB_BACKUP

TO STRATEGY2_FULL

-- ONCE PER HOUR

BACKUP LOG DB_BACKUP

TO STRATEGY2_TLOG

-- RECOVERY:

-- FIRST THE LAST FULL BACKUP

RESTORE DATABASE DB_BACKUP

FROM  STRATEGY2_FULL

WITH FILE = ...,NORECOVERY

-- NOW EACH TRANSACTION LOG BACKUP AND

-- THE POINT IN TIME OPTION IN THE LAST T. LOG BACKUP

RESTORE LOG DB_BACKUP

FROM  STRATEGY2_TLOG

WITH FILE = ..., RECOVERY, STOPAT = ...

Listing 2. Transact-SQL commands used in the backup strategy 2.

Listing 2 shows the first two backup devices: one for full backups and one for the transaction log backups. The following are the commands that make the full backup and transaction log, respectively. Next, the script shows the commands for the restoration of the database, which must be initiated by the last full backup and the restoration of the transaction log backups in the correct order. If necessary, you can use the feature point in time recovery through STOPAT of the RESTORE LOG command in order to indicate the exact date and time when restoring the last transaction log backup.

Strategy 3

The third strategy focus on  medium size and large databases which are being constantly updated and contain data from more than one system or are integrated with external systems. These databases store vital data for companies and any type of data loss is unacceptable because all systems that depend on the bank may have problems if any data is lost. As an example of databases that would benefit from the strategy we mention a third-party system that runs on the company with several custom modules.

In this scenario it is recommended to use three types of backups available for SQL Server as follows: once a day a full backup of the database is made at a time that does not affect the use of systems, twice a day two differential backup are performed usually one at midnight and another at noon. Finally, a transaction log backup is made each the hour. Three sets of tapes are recommended each composed of two strands, so that there is a set for the current week and a fortnight ago for each backup type.

Differential Backup: This backup type is similar to backing up the transaction log, with the difference that this type will back up only changed values since the last full backup. That is, if information has been changed twenty times since the last backup, a backup of transaction log would have the 20 modifications that information, while a differential backup would have only the last value stored. Because of this property this type of backup creates smaller files although take a little longer to be performed than the transaction log backup. But on the other hand, it takes a shorter time to restore in comparison with the transaction log backup.

This strategy is very similar to strategy 2. But here we use a differential backup twice a day. The purpose of this type of backup is to shorten the recovery time, because the restore sequence of the database in this strategy should begin with the last full backup followed by the last differential backup, and finally restore as many of the transaction log backups as necessary, possibly using the point in time option. Thus, the recovery time is shorter than the time spent on strategy 2 because the amount of transaction log backups need to be restored will be less. Listing 3 shows the Transact-SQL commands that can be used as a basis to build the strategy 3.

-- ***************************

-- STRATEGY 3:

-- ***************************

-- 1 FULL BACKUP PER DAY + 2 DIFFERENTIAL BACKUPS PER DAY + 1 TRANSACTION LOG BACKUP EACH HOUR

-- RECOVERY: LAST FULL BACKUP + LAST DIFFERENTIAL BACKUP + SEQUENCE OF T. LOG BACKUPS

-- RECOVERY TIME: SHORT

-- USE: SMALL OR MEDIUM DATABASES USED DAILY THAT CANNOT LOST A SINGLE BIT OF DATA

-- OBS: FORMAT THE BACKUP DEVICES EVERY 15 DAYS

-- EXAMPLE:

-- BACKUP DEVICES

EXEC SP_ADDUMPDEVICE 'DISK','STRATEGY3_FULL','C:\BACKUP\ST3_FULL.BAK'

GO

EXEC SP_ADDUMPDEVICE 'DISK','STRATEGY3_DIFF','C:\BACKUP\ST3_DIFF.BAK'

GO

EXEC SP_ADDUMPDEVICE 'DISK','STRATEGY3_TLOG','C:\BACKUP\ST3_TLOG.BAK'

GO

-- ONCE PER DAY

BACKUP DATABASE DB_BACKUP

TO STRATEGY3_FULL

-- TWICE PER DAY: AT NOON AND MIDNIGHT

BACKUP DATABASE DB_BACKUP

TO STRATEGY3_DIFF

WITH DIFFERENTIAL

-- ONCE PER HOUR

BACKUP LOG DB_BACKUP

TO STRATEGY3_TLOG

-- RECOVERY:

-- FIRST THE LAST FULL BACKUP

RESTORE DATABASE DB_BACKUP

FROM  STRATEGY3_FULL

WITH FILE = ...,NORECOVERY

-- NOW THE LAST DIFFERENTIAL BACKUP

RESTORE DATABASE DB_BACKUP

FROM  STRATEGY3_DIFF

WITH FILE = ...,NORECOVERY

-- NOW EVERY T. LOG BACKUP AND

-- THE POINT IN TIME OPTION IN THE LAST RESTORED BACKUP

RESTORE LOG DB_BACKUP

FROM  STRATEGY3_TLOG

WITH FILE = ..., RECOVERY, STOPAT = ...

Listing 3. Transact-SQL commands used in the backup strategy 3.

Listing 3 begins with the commands that create three backup devices: one for full backups, one for differential backups to another and for the transaction log backups. The following are the commands that make the full backup, differential and transaction log, respectively. Finally, the order of restoration is made mandatory by the RESTORE DATABASE command, used to restore the full backup, differential, and the RESTORE LOG command to restore the transaction log backup.

Strategy 4

The fourth strategy is recommended for big databases which constantly undergo changes and are used to store information from multiple systems that need a high level of availability. To support the management of these databases is common to use high-availability techniques such as clustering, replication, mirroring and redundant power and network components. These environments are critical and any downtime can negatively affect not only the company but also all its partners, suppliers, customers and employees. Thus, the backup strategy must take into consideration the high availability and criticality of the database and the entire computing environment. As an example we can mention a database that are considered essential to the functioning of a city, as the service provided by police stations, hospitals, registries and forums.

Due to the size of these databases is only possible to perform a full backup at least once a week and only when the system does not exceed a certain level of use. For example, the full backup can only be done after a purge weekly in the database and only if the amount of server processing remains around 10% of the total. It is possible to do a full backup of a single data file. However, this option requires a design of the internal structure of the database, which should have been implemented when the database was created.

In this strategy the differential backup can be done every hour, and between a differential backup and the other two you need to perform transaction log backups. Thus we have the same sequence of restoration of strategy 3, but in the strategy 4 differential backups and transaction log will be smaller and more frequent.

The recommendation for the organization of backup tapes is to use at least four sets in a cyclic way and sequential. In each set you will need four separate tapes for full backups, two tapes for differential backups and one tape for the transaction log backups, adding seven tapes per set and 28 tapes in total. The period in which the tapes will be reused is monthly for the full backup and weekly full for the differential and transaction log backups. Listing 4 shows the Transact-SQL commands that can be used as a basis to build the strategy 4.

-- ***************************

-- STRATEGY 4:

-- ***************************

-- 1 FULL BACKUP PER WEEK + 1 DIFFERENTIAL BACKUP PER HOUR + 1 T. LOG BACKUP EACH HALF HOUR

-- RECOVERY: LAST FULL BACKUP + LAST DIFFERENTIAL BACKUP + SEQUENCE OF T. LOG BACKUPS

-- RECOVERY TIME: HIGH

-- USE: BIG DATABASE HEVEALY USED THAT REQUIRE HIGH AVAILABILITY WITH NO LOOF OF DATA

-- OBS: FORMAT THE BACKUP DEVICES ONCE PER WEEK

-- EXAMPLE:

-- BACKUP DEVICES

EXEC SP_ADDUMPDEVICE 'DISK','STRATEGY4_FULL','C:\BACKUP\ST4_FULL.BAK'

GO

EXEC SP_ADDUMPDEVICE 'DISK','STRATEGY4_DIFF','C:\BACKUP\ST4_DIFF.BAK'

GO

EXEC SP_ADDUMPDEVICE 'DISK','STRATEGY4_TLOG','C:\BACKUP\ST4_TLOG.BAK'

GO

-- ONCE PER WEEK

BACKUP DATABASE DB_BACKUP

TO STRATEGY4_FULL

-- ONCE PER HOUR

BACKUP DATABASE DB_BACKUP

TO STRATEGY4_DIFF

WITH DIFFERENTIAL, FILENAME = ...., FILEGROUP = ...

-- ONCE EVERY HALF HOUR

BACKUP LOG DB_BACKUP

TO STRATEGY4_TLOG

-- RECOVERY:

-- FIRST THE LAST FULL BACKUP

RESTORE DATABASE DB_BACKUP

FROM  STRATEGY4_FULL

WITH FILE = ...,FILENAME = ..., FILEGROUP = ..., NORECOVERY

-- NEXT THE LAST DIFFERENTIAL BACKUP

RESTORE DATABASE DB_BACKUP

FROM  STRATEGY4_DIFF

WITH FILE = ...,NORECOVERY

-- NOW EVERY T. LOG BACKUP AND

-- THE POINT IN TIME OPTION IN THE LAST RESTORED BACKUP

RESTORE LOG DB_BACKUP

FROM  STRATEGY4_TLOG

WITH FILE = ..., RECOVERY, STOPAT = ...

Listing 4. Transact-SQL commands used in the backup strategy 4.

Listing 4 follows the same sequence of commands in the previous strategy: first the backups devices are created for each backup type, then there is the creation of backups for each device. Finally, the correct order of restoration of backups is presented. The highlight of this strategy is to schedule the backup commands should be used and also the FILENAME and FILEGROUP options that enable the backup or restore only one database file or all files in a filegroup, respectively. With the specification of these options the DBA can make the backup of only part of the database. But first we need to create objects (tables, indexes, stored procedures etc.) and data on specific files and filegroups.

Conclusion

Performing a backup of a database is a crucial task for any environment. Due to the amount of information, redundancy and reliability companies are storing the backup on tapes as an alternative to hard disk usage.

This paper presented the requirements, technologies, strategies and recommendations for the recording of a backup database into a tape. In addition, the article also pointed out two different solutions of the reading device and tape recording that provide a complete solution for backup storage.

To facilitate the planning and implementation of a backup strategy, four different types of generic backup strategies were presented with suggestions for scenarios easily found in companies. The commands, actions, details and recommendations for each strategy were explained, highlighting that each environment has its own characteristics and it was suggested that each strategy should be adapted according to the needs and requirements of the backup.

The investment in the backup process is fundamental. Knowing how to address the resources to this process is one of the actions that sooner or later  will pay off because this process deals with the storage of critical information for businesses and that should always be available and operational. To achieve this goal is necessary to concentrate efforts to obtain resources, strategies and tactics related to the backup, as this action represents one of the most important contingencies that a company can implement in the computing environment.

This last part focused on backup strategies. Four different strategies were explained with details that covered backup types, set of tapes, periodicity and other important aspects needed to be considered when setting up a backup strategy for the database.

To see the first part, access:  http://mrbool.com/p/Storing-the-Backup-on-Tape-Part-1/23489 

References

Details about the HP Storage Works  1/8 G2 Tape Autoloader:

http://h10010.www1.hp.com/wwpc/us/en/en/WF05a/12169-304612-82176-82176-82176-3319912.html

Details about the Dell PowerVault 114T:

http://www1.la.dell.com/content/products/productdetails.aspx/pvaul_114t?c=br&cs=brbsdt1&l=pt&s=bsd

Information about  DLT e SDLT tapes:

http://en.wikipedia.org/wiki/Digital_Linear_Tape

Information about  LTO tapes:

http://en.wikipedia.org/wiki/Linear_Tape-Open



Mauro Pichiliani has the Master of Science degree on collaborative systems by the Aeronatics Institute of Technology (ITA) in Brazil. He is a specialist on database technologies with more than 8 years of experience on the industry...

What did you think of this post?
Services
[Close]
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually – in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits – in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!


> More info about MrBool Credits
[Close]
You must be logged to download.

Click here to login