 Network Backup & Restore Software Solution for SMBs |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| EN PL ES | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Article ref. no.: FS-FBS-20080218-I01
Last revised: 26 January 2015 Version : 1.1 SQL Database and Email Database Files BackupThis article deals with issues connected with large file backups. It presents hands-on guidelines concerning database backup: Oracle, MySQL, Microsoft SQL Server, Microsoft Exchange Server, InterBase, Firebird, PostgreSql, IBM DB2, Microsoft Access, dBase, Paradox, FoxPro and email client files: Microsoft Outlook, Outlook Express, Mozilla Thunderbird, Eudora, The Bat!.Big file, big problem ?Database files and email client files have at least two things in common: they’re big and they can’t easily be backed up. Large files can be broken down into two categories, according to storage location or availability. Files located on the drives of desktop, workstation or laptop computers are local files. These can include small, local databases like: Microsoft Access, dBase, Paradox, FoxPro and email client files such as Microsoft Outlook, Outlook Express, Mozilla Thunderbird, Eudora, The Bat!. Files stored on servers are mostly shared files shared by the server with other computers on the network. The most popular of these include Oracle, MySQL, Microsoft SQL Server, InterBase, Firebird, PostgreSql, IBM DB2.Large files, like any other files, can be locked when in use. However, this occurs much more often in the case of database and email client files. Data servers usually share their bases on a 24/7 basis. Email (local) files are rarely used non-stop because they are mostly accessed by only one user, but this means that the window of availability for backup is limited to about 8 hours a day. That kind of use and limited file availability makes backup complicated. In many environments, taking down a database server for the duration of the backup process is problematic, or even impossible. Backups of email files that are available only within a limited time window (while the user is working at the computer) and constantly locked can also be difficult without the proper tools. Open file backupThe issue of backing up database and email client files can be resolved using the Open File Manager functionality available in Ferro Backup System. OFM supports backups of files open and locked by other processes. It reads files by accessing the drive directly, bypassing the operating system. For more information on backups of locked and open files see "Backup of open and locked files" .Although it is possible to open and copy (back up) the contents of an open and locked database or email client file, there is still the issue of ensuring integrity. An integral file has a consistent structure for the software which uses it to store information. For database files integrity is ensured once all write transactions are completed. If a database file is relatively big, backing it up can take up to several dozen minutes. During such a long time the database server or email client will probably still write to the file. If this happens, the backup copy will not be integral. Any subsequent attempt to use the file will end up in a damaged file structure error message from the database server or email client. Which is why, when the backup software detects a write operation, it needs to restart the whole backup process. Thus, depending on the type of application, its workload and file size, any attempts to perform a backup copy in a traditional way can fail or produce a "crash-consistent" backup copy. Fast and reliable backupFerro Backup System V2.8 or newer includes the extended Open File Manager, which allows the file-write process to be temporarily suspended. Thus, the backup process can proceed smoothly. While the write process is being suspended, the computer user (if he/she is currently using the software) receives a message saying that the software is temporarily disabled for the duration of the backup process: Fig. 1 A window notifying the user that the program has been disabled The user can stop the backup process or reschedule it for later. Either selection, however, can be overriden by the Ferro Backup System administrator. Using the extended OFM mechanism, backing up email client files from Microsoft Office Outlook, Outlook Express, Mozilla Thunderbird, Eudora, The Bat! and small database files from Microsoft Access, dBase, Paradox, FoxPro etc. is fast and easy.
Table 1. File extensions of popular email clients and small database software Email client files are usually stored in the user’s folder. For Windows 2000, Windows XP, Windows 2003 this is: C:\Documents and Settings\user_name\Application Data\producer\software_name\ For Windows Vista, Windows Server 2008, Windows 7: C:\Users\user_name\Application Data\producer\software_name\ Incorporating the functionality to suspend processes for database servers may not be, however, as easy and useful as for local databases and email clients. In the case of servers, switching on the process locking function may disrupt access to services available on that server (the server will not respond). Therefore, for large database files (>10 GB) located on servers with many write transactions, it is recommended to make backup copies during times of lower workload or to use one of the following methods. Shadow copiesIf we use a database supporting the Volume Shadow Copy Service, then to make a backup copy we can use script which backs up a selected file using that service. The script can be run prior to the backup process using the Remote commands available in Ferro Backup System. Remote commands support among other things the running of specified scripts or commands before or after the backup process. The backup process should also include the file created by the VSS. After the process is completed, the database copy can be deleted from the disk (also using a remote command).VSS supports backups of files in use, despite continuing write operations, without putting the write process on hold. Still, the following constraints of the VSS need to be taken into account:
If the database server does not support VSS, the database backup copy will only be a crash-consistent copy, i.e. it will lack integrity and there will be problems if the user attempts to actually use it. For more about VSS see How Volume Shadow Copy Service Works. There are simpler ways - "Hot Backup"Backup copies can also be made using dedicated database backup applications. Backups which do not require the database server to be shut off are called hot backups, online backups or dynamic backups. Developers of all the most popular database systems provide such applications as commands to be executed from the command shell. To back up a database using the Ferro Backup System, such an application can be run on the database server using the abovementioned Remote commands.The procedure is the same for all databases. First a command is run to make a copy of the database, then the backup copy proper is made, and then the original database copy is deleted. The remote command defined in the FBS Server will thus be following: BEFOREBACKUP TRUE COMMAND PARAMETERS The following table presents a list of dedicated commands to make database backup copies for the most popular database systems.
Table 2. Commands to make backups of popular databases There are more reliable ways - "Cold Backup" (offline)If the database server does not feature dedicated applications to create backups, or for some other reason we do not want to back up a database in use, we can back up that database after stopping the database server service. This kind of backup, which requires the database to be previously shut down, is called cold backup or offline backup.As previously, the operation can be executed automatically using Remote commands. Prior to the backup process, a command to stop the server needs to be run, and then, after the backup process is completed, another command is run to restart the server. In order to stop the database server service, the following command can be used NET STOP service_name. Consequently, to resume server service, the following command must be used NET START service_name. The remote command defined in the FBS Server will thus be following: BEFOREBACKUP TRUE CMD "/C NET STOP SERVICE_NAME " Large databases, small backup copiesLimited file access and problems obtaining an integral backup are not the only issues to be dealt with while creating backups of large files. For email and database files with sizes measured in gigabytes, backup size and network load are also problematic in the backup process.Storage of several versions (from several days) of large files from multiple computers can quickly fill up even the largest hard disks and disk arrays. Database files are optimized for read and write speed. Such optimization means the database system allocated much more disk space than required for the amount of information stored. Such additional space is gradually filled up as more and more data are added to the database. Thus, the information does not need to be moved and copied (within the file) during subsequent transactions adding new information to or deleting existing information from the database. Free, available space within a database file is usually filled up with zero byte strings. Such files can thus be very easily compressed. The compression engine built into Ferro Backup System usually allows shrinking the database file by a 9 to 1 ratio. This means that the backup copy can be transferred to the backup server faster, and less disk space is needed. Furthermore, in order to resolve the issue of large amounts of data and speed up the backup process, Ferro Backup System includes differential backup options on the file fragment level, designed to back up large files such as database files and email client files. If it is active, Ferro Backup System only backs up those file fragments which have been altered or newly created since the last backup. If, say, since the last backup a 50 GB database file has only been modified by 1% (500 MB), then only that much information will be backed up again (assuming a compression of 9 to 1, only about 50 MB will need to be sent over the network and uploaded to the backup server). Backup copies made with the File fragment difference backup option active are restored in the same way as full backups. The Ferro Backup System Server handles the unpacking and merging of file fragments without the administrator’s involvement. SummaryBackups of database or email client files can be made relatively easy using the Ferro Backup System’s functionalities. The Open File Manager supports backups of files in use. The OFM extension which suspends the write process enables seamless backups of email and local database files. Large databases can be backed up using Remote commands and a dedicated backup application. Databases compatible with the Volume Shadow Copy Service can be backed up using Remote commands and the proper script. Finally, backup copies of files can be created after stopping the database server. In any case, differential file fragment backup combined with compression algorithms will speed any backup processes while saving space on the backup server disks. |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Home Help Where to Buy
Download
Contact Us
Partners |
Printable version |
Language: EN  PL 
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
How to Perform Differential Backup of Database Files All rights reserved. Terms and Conditions | Privacy Policy Copyright © 2000-2024 FERRO Software |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
