Database backup. Database recovery

The previous lecture discussed the topic of security - protecting data from unauthorized access. This lecture talks about protecting data from unexpected loss. Preventing data loss is one of the most important problems you can face when managing database systems. Data loss can occur as a result of many different problems.

Hardware faults

• Incorrect use of UPDATE and DELETE statements

  Errors software

  Emergency situations, such as fire or flooding

To avoid data loss, you can implement a recovery strategy for the database. The recovery strategy must be planned, implemented and tested taking into account possible malfunctions, which can be encountered during the operation of the system, and the required level of data protection. In data marts, that is, cases where data can be recovered from other systems, it is probably not necessary to back up every single transaction. It may be sufficient to perform full backups of your data at regular intervals. Conversely, for a database that stores online store transactions, it may be necessary to maintain backup copies of each individual transaction. The SQL Server DBMS provides a full range of functions to implement exactly the type of backup that you need. This lecture discusses the most widely used strategies for protecting data in Microsoft SQL Server.

Full database backup

The most common backup strategy is to back up the entire database at predetermined intervals (for example, every night). With this disaster recovery strategy, you can restore your database to the state it was in when you last took a backup. This strategy is implemented by performing full database backups, as discussed below.

Full backup copy database contains all the database data and metainformation that is needed to restore the entire database, including full-text catalogs. When you restore a database from a full backup, all files in the database are restored and the data is retrieved in a consistent state at the point in time at which the backup was performed. While the backup is running, the database is in production mode, and the user can perform transactions, changing data in the usual way. The term "consistent state" means that all transactions that were committed while the database backup was running are applied, and all transactions that were not committed are rolled back. For situations that could cause data consistency to become compromised due to transactions that modify the data during the backup process, SQL Server has a special process that helps ensure data consistency. This process writes to the backup device both the data pages and the transaction log.

Advice. Full-text catalogs were introduced into databases to add full-text indexing functionality to SQL Server. Full-text indexing allows you to search the database faster and with greater accuracy. For more information about full-text indexing, see SQL Server Books Online, Full-Text Indexes.

The speed of backup is determined by the speed of the input/output devices used (those input/output devices that are used to collect and store information). To achieve best performance SQL Server reads files sequentially. If your I/O devices are capable of simultaneously processing backup I/O data and I/O data coming from normal system use, then creating a backup will have a negligible impact on system performance. However, it is better to perform a full database backup when there are no peak loads.

In the next section, we'll look at options for implementing this backup strategy.

Simple recovery model

You should notify SQL Server in advance what type of backup you intend to use, so you must configure the database to match the backup type you choose. This configuration is done by selecting the Database Recovery Model option. The default database recovery model is derived from the recovery model of the database model that was defined when the database was created. To implement a backup strategy that includes only full backups, you should select the simple recovery model (SIMPLE).

Selecting the SIMPLE recovery model

From the Start menu, select All Programs. Microsoft SQL Server 2005, SQL Server Management Studio (All Programs, Microsoft SQL Server 2005, SQL Server Management Studio).

In the Connect To Server dialog box, click the Connect button.

On the Standard toolbar, click the New Query button to open the New Query window.

You can use the ALTER DATABASE statement to set the recovery model. Enter the text of the following instruction and click the Execute button.

ALTER DATABASE AdventureWorks

SET RECOVERY SIMPLE;

Additional Information.This lecture focuses primarily on creating backups and restoring using SQL statements. Lectures 6-7 will cover how to perform many of these same procedures not through T-SQL statements, but through the SQL Server Management Studio user interface.

Checking the settings of the disaster recovery model

To view the recovery model specified for a database, you can use the DATABASEPROPERTYEX function, which retrieves the settings of the current date base or properties of the specified database. Follow the instructions below to extract information about the AdventureWorks database recovery model.

SELECT DATABASEPROPERTYEX("AdventureWorks","Recovery")

Make sure the query results include the SIMPLE recovery model.

Close the SQL Server Management Studio window.

Backup devices

Before you begin performing backup operations, you must determine where your backups will be stored. The location where backups are stored is called a backup device. Each backup device can store multiple backups of different types. There are two different types backup devices:

Tape devices.Can be used to store backup copies on tape. Tape devices must be installed locally. A backup can span multiple tapes, and a single tape can contain both SQL Server and Windows backups.

Disk devices.Files on local or remote disk or disk drive. These files are accessed by specifying the path to the file where the backup is stored. To access remote repositories, use a UNC path.

Note. This book will only cover backup to disk devices. Backup SQL files Server on tape devices is not used very often these days. When SQL Server backups are stored on tape, they are usually created using third-party software that offers additional features, such as the use of remote tape storage. Alternatively, a tape device can be used to provide additional insurance against data that is already backed up on a disk device.

Backup devices are identified by their device name. The device name can be a logical or physical device name. The physical disk device name is the path to the backup file, for example, "\\BACKUPSERVER\Backups\adv\AdventureWorks.bak". This path can be included directly in the backup statement. The logical device name is a name that points to the name of the physical backup device and is stored in SQL Server. When a logical device name is used in a backup statement, SQL Server looks for the corresponding physical device in the system catalog and performs the backup, storing the backup in the specified folder.

To add a logical device to the system directory, you can use the sp_addumpdevice stored procedure. IN following example a logical device named Adv_FullDb_Dev is defined.

EXEC sp addumpdevice "disk", "AdvFullDbDev", "T:\BACKUPS\AdvFullDbDev.bak";

Advice. Be sure to change the file path to match your computer. T:/, then change this part of the file path in the instructions to match the drive letter on your computer. Also, make sure that all folders specified in this path exist on your computer.

Logical and physical device names are interchangeable; both names can be used for database backup and recovery. Of course, it's generally best to use one of the two naming conventions at all times to keep your code simple. You should choose the agreement that you like best in advance.

You should never back up to a disk device that is located on the same physical storage device as the database itself. Even if disk storage is fault-tolerant due to RAID, there is always the possibility of controller failure and data corruption on the disks. Additionally, you should consider storing the backup device backup files on tapes and storing those tapes in a remote location.

Advice. The abbreviation RAID comes from the expression "redundant array of independent disks". These arrays are multi-drive disk systems that are used to increase storage availability and capacity.

Performing a full database backup

Once you have set the backup model to SIMPLE and decided which backup device to store your backups on, you can begin performing your backups. A full database backup is performed using a fairly simple BACKUP DATABASE statement. In its simplest form, you only need to tell the system which database to back up and on which device to save it. To back up the AdventureWorks database to a pre-selected logical device, use the following T-SQL statement:

TO Adv_FullDb_Dev;

If you want to perform a full database backup to a physical device, you must specify the device type and location of the backup in the BACKUP DATABASE statement. To create a backup copy of the database in the t:\adv.bak folder, use the following instructions:

BACKUP DATABASE AdventureWorks

TO DISK="t:\adv.bak";

As mentioned, any backup device can store more than one backup. The argument to the BACKUP DATABASE statement can specify whether SQL Server should overwrite the existing backup on this device or append it to existing backups. For this purpose they are used keywords INIT or NOINIT. If you specify INIT, the backup device is formatted before starting a backup, and all backups that are on that device are deleted. NOINIT, which is the default unless otherwise specified, allows SQL Server to append a backup to an existing backup device, preserving all existing backups on it. These options are set using the WITH block at the end of the BACKUP DATABASE statement.

If you want to create the same backup as the previous example, but tell SQL Server to wipe the device first, use the following statement:

BACKUP DATABASE AdventureWorks

TO DISK="t:\adv.bak"

As you can see, performing a full database backup is not difficult. In the next section, you will see that a full backup is the basic backup type on which all other backup types are built. Other types of backups depend on having a full backup because they require the restored database as a starting point. These types of backups, including differential backups, preserve changes that were made to the database after a full backup was created. Thus, we can see that full backups are important not only in a recovery strategy that performs only a full backup, but also in other backup strategies, which are discussed below.

Differential backup

The main advantage of a full database backup is that a full backup contains all the data needed to restore the entire database. But this advantage can also be a disadvantage. Imagine a database that is backed up every night. If you have to restore the database, you will have to use last night's backup anyway, and as a result, you will lose a whole day's work. One way to reduce the potential amount of time that may be missed by backups is to perform full database backups more frequently. But this in itself is problematic. Because all data and transaction log fragments are written to the backup device, the backup may take a long time to complete. In addition, storing all of these backups requires a lot of disk space h°, and a full backup can reduce database performance due to the large amount of I/O required. Wouldn't it be better to back up once at night and then back up only the data that was changed during the day? Such functionality provides a differential backup type.

A differential backup only saves data changes that occurred after the full backup was created. If the same data has changed several times since a full backup was taken, a differential backup retains the most recent version of the changed data. To restore data from a differential backup, you will first need to restore the full backup data, and then apply only the latest differential backup, as shown in rice. 4.1.

Rice. 4.1. Backup strategy using differential backups

Performing a differential backup

Performing a differential backup is not much different from performing a full backup. The only difference is that the WITH block declares instructions for creating a differential backup. The syntax for the BACKUP DATABASE statement to back up the Adventure Works database to a physical device, overwriting other existing backups on that device, would be:

BACKUP DATABASE AdventureWorks

TO DISK="t:\adv_diff.bak"

WITH INIT,DIFFERENTIAL;

If you want to use a logical device, you must first create it, just like with a full backup.

EXEC sp_addumpdevice "disk", "Adv_Diff_Dev",

"T:\BACKUPS\AdvDiffDev.bak";

BACKUP DATABASE AdventureWorks

TO Adv_Diff_Dev

WITH INIT,DIFFERENTIAL;

Important. To restore data from a differential backup, you always need the most recent full backup. Be careful not to overwrite or delete a full database backup while it is needed to restore data from differential backups.

Transaction log backup

By combining full and differential copies of a database, you can create a snapshot of your data and restore it. But in some situations, it is also desirable to have backup copies of all events that occurred in the database, down to records of each statement executed. This way it would be possible to restore the database to any required state. Transaction log backup provides just such an opportunity. As the name suggests, the transaction log backup method creates backup copies of all transaction log entries that have occurred in the database. The main benefits of transaction log backup are as follows:

Transaction log backup allows you to restore data to a specific point in time.

Because transaction log backup creates backup copies of log entries, you can back up from the transaction log even if the data is corrupted. Using such a backup, you can restore the database back to the last transaction that occurred before the problems began. This way, if a failure occurs, none of the committed transactions will be lost.

As with differential backups, restoring a database from transaction log backups in a recovery strategy requires an underlying full database backup. A backup strategy using transaction log backups is shown in rice. 4.2. Full database backups are performed during off-peak hours, and transaction log backups are performed at specific times during the day. Transaction log backups contain all transactions that have occurred since the last transaction log backup was created. Therefore, to restore a database using transaction log backups, you need a full database backup and all transaction log backups since the full backup was created. As you can see, it is important that all backups are available. If a full database backup or one of the transaction log backups is lost, it will not be possible to recover the desired amount of data.

Rice. 4.2. Backup strategy using transaction log backups

Combining differential and transaction log backups

Another possible strategy is to combine full and differential backup methods with transaction log backups. This strategy is used when restoring data from transaction log backups would be too time consuming. Because restoring data from a transaction log backup means that all transactions must be performed again, restoring all data, especially in large databases, can take a very long time. Differential backups only apply data changes, which can be completed faster compared to re-execution all transactions.

To restore the database when using a combined strategy as shown in rice. 4.3, you need to restore first the data from the last full backup, then the last differential backup, and finally the data from all subsequent transaction log backups.

Rice. 4.3. Combined backup strategy

For example, to restore data to a T3 transaction log backup, you would restore the F1 full backup, the D1 differential backup, and the T3 transaction log backup.

The time interval between transaction log backups depends on:

The number and size of transactions performed in the database. With this backup strategy, SQL Server must retain all transactions until the transaction log is backed up. Therefore, the transaction log file must contain all transactions that occurred during the period of time between two consecutive transaction log backups. If the log fills up too quickly, you should reduce the time interval between transaction log backups or increase the size of the transaction log file.

Acceptable amount of data loss. As mentioned above, it is possible to restore data back to the last transaction if data files are lost. However, if the transaction log becomes corrupted or lost, you can only restore the data to the point of the last transaction log backup. Reducing the amount of time between transaction log backups will reduce the amount of data lost in this situation.

Proper database backup is critical to the integrity of the IT infrastructure. As a rule, it is important to comply with the requirements of the security policy. Bacula Systems' advanced solutions, implemented on the basis of DBMS plugins, will allow any company that uses Bacula Enterprise to quickly and reliably backup databases.

When simplicity and speed are important, the right solution for system administrator working under tight time constraints is the use of Bacula Systems tools. You do not need special knowledge to take care of the safety of data and metadata, including data about users, rights, roles, etc. At the same time, the user has access to ample opportunities for customizing the software, providing the necessary flexibility when working in the most complex professional environments.

Oracle Database Backup

Simplifies backup and recovery procedures for Oracle databases. Oracle database administrators can use RMAN commands to make database backups easier and more convenient. In addition to speed and simplicity, the Oracle plugin significantly optimizes other important functions, including restoring the database to any point in time, as well as filtering objects during backup and restore. All these capabilities are available using any of the two backup methods, namely the “Dump” method and the Predefined Point Restore (PITR) method using the plugin's tight integration with the Oracle RMAN recovery manager.

The plugin also uses the RMAN API sbt, which allows you to avoid writing files to the first place. local disks. In both “Dump” and RMAN modes, the plugin creates a backup copy of important information, which is best practice Oracle Administration D.B. Typically, the “Dump” and RMAN PITR methods are used together to back up the same cluster.

Improving Oracle Incremental and Differential Backup Efficiency
​

Bacula Systems' Oracle plugin leverages many RMAN components, including change tracking, to improve backup performance by writing blocks from each data file to a change tracking file. When change tracking is enabled, RMAN uses the change tracking file to identify changed blocks to perform incremental copies, avoiding the need to scan every block in the data file.

​
Oracle Data Recovery
​

As shown in the figure below, the Oracle plugin allows you to restore the database using RMAN utilities or using the Dump method.

​Supported versions of the Oracle platform for backup​​

The plugin is available for 32 and 64 bit Linux versions and is compatible with Oracle database versions 10.x, 11.x.

PostgreSQL Backup and Restore

It was developed to simplify the procedures for backing up and restoring PostgreSQL database clusters. The administrator does not need to know PostgreSQL's internal backup methods or the procedures for writing complex scripts. The plugin automatically backs up important information such as database configuration, user definitions, tablespaces. The PostgreSQL plugin supports two backup methods: “Dump” and PITR.

Using Bacula Systems' unique Late Data Inclusion (LDI) technology, this plugin effectively captures data right up until the backup is completed, thereby minimizing the risk of data loss. This is an advantage of the plugin compared to solutions from other vendors.

​Hot backup of a PostgreSQL database​​​​

The Bacula Systems plugin for PostgreSQL also allows the database administrator to create backups of PostgreSQL servers in a “hot backup” mode with WAL file backups.

​PostgreSQL recovery​​​

Cluster Contents

Database Contents

Supported versions of the PostgreSQL platform for backup

​​​PostgreSQL database backup is available for 32 and 64-bit versions of Linux and supports PostgreSQL versions: 8.4, 9.0, 9.1 and 9.2. Runs on Bacula Enterprise software version 6.0.6 and higher.

MySQL Database Backup and Restore

It was developed to simplify the backup and recovery procedures for MySQL servers. The administrator does not need to know MySQL's internal backup methods or the procedures for writing complex scripts. The plugin is configurable and automatically creates backups of important information, such as database configuration and user definitions. The MySQL plugin supports two backup methods: “Dump” and “Binary”.

Possibility to choose a MySQL database backup method

Allows the administrator to select the Dump or Binary method for faster backup and creation of larger backups. The MySQL plugin processes MySQL log files when using the PITR backup function.

MySQL database data recovery

Restoring a single MySQL database

Supported versions of the MySQL platform for backup

​MySQL database backup and restore is available for 32 and 64-bit versions of Linux and supports MySQL versions 4.0.x, 4.1.x, 5.0.x, 5.5.x, 5.6.x.
​

Backing up a SQL Server database

​VSS plugin is one of two ways to backup a SQL Server database using Bacula Systems software. Database backup SQL data Server via VSS plugin is designed to backup several specific Windows components, in particular the SQL Server database. The Bacula Enterprise VSS plugin greatly simplifies the creation of SQL Server database backups.
​

A quick and easy way to restore a SQL Server database

​VSS plugin is capable of restoring either master or other database instances. All databases, with the exception of master, can be restored while MS SQL is running. The VSS plugin can also be used in conjunction with the MS SQL database migration process.

SQL server tree during recovery

Moved database

Supported SQL Database Backup Versions

SQL database backup is performed under Windows 2003 SP1 and higher, Windows 2008 R1 and Windows 2008 R2.

SQL Database Backup - Advanced Features

- This modern solution to create backup copies of multiple MS SQL databases, allowing:

• Perform a full backup that saves database files and transaction log files, which avoids storage system failure.
• With differential backup, only the data that has changed since the last full backup was created is saved. The Bacula Enterprise plugin for SQL Server includes integrated options to upgrade backups from differential to full if needed.
• Creating incremental backups by backing up transaction logs. In case of configuration according to the corresponding model, this method allows you to use PITR mode.
• Backup master database to create a backup copy of MS SQL configuration information.

Powerful SQL Database Recovery Tool

The Bacula Enterprise plugin for SQL can restore data in several ways, including using PITR checkpoint recovery. In this case, the recovery process assumes the following scenarios:

• Recovering files to disk
• Restoring the original database
• Restoring a database with a new name
• Restoring a database with a new name and moving files

Model for creating a database backup “with full and partial logging”

Supported versions of the MS SQL platform for backup

​MS SQL database backup is possible under Windows 2003 R2, Windows 2008 R2, Windows 2012 and for MS SQL Server 2005, 2008 and 2014.

Interested in database backup with Bacula Enterprise and expert support? Watch the video.​

One of most important tasks The thing that a database administrator must constantly do is perform backups. If something goes wrong, it is the DBA's job to get the server back up and running as quickly as possible. Lost productivity, or worse, lost data, can be very costly for a business.

If you want to have an up-to-date database backup, you will want to use a RAID disk configuration that creates a mirror to protect the data, but this is not a panacea. RAID arrays are only the first step in protecting data from loss. Depending on the configuration of the RAID array, one or even more hard drives must fail before irretrievable loss data. Additionally, the use of hot-swap and hot-spare disks can be used to ensure that the server continues to operate without shutting down even if a hard drive fails. The key thing that a DBA needs to understand is that RAID arrays can protect data in the event of hard drive failure. What happens if there is an emergency (fire, flood, robbery, etc.)? Will your database files become corrupted as a result of a software error or hardware failure? And finally, what happens if the user (or the DBA itself) mistakenly deletes data that will suddenly be needed in the future? A RAID configuration will not help in these cases.

The most important thing is that the administrator can always replace the server, but the data on this server is extremely difficult to recover, sometimes simply impossible.

Let's look at some types of database backups.

SQL Server supports three types of backup: Full, Differential or Differential, and Log copy. In addition to the three main types of backups, which work on the entire database as a whole, there are also several additional types of backups that are used to copy a single file or group of files.

Full Backup - Creates a full backup of all database extents. If the DBA restores the database using a full backup, it will only need the most recent copy it created. However, a full backup is the slowest type of backup.

Differential Backup - Backs up only those extents that have changed since the last time full copy. If the DBA restores the database using a differential backup, it will need the latest full backup and the last differential backup it created. Differential backups are much faster, but require more recovery time because they require both a full backup and a differential backup.

Log Backup - Creates a transaction log backup from the last full backup or the previous transaction log copy. The DBA will (or may not) need to create transaction log backups depending on the recovery model it uses. If he restores the database using a full backup and transaction log copy, he will need the latest full backup and all (in order) transaction log backups to recover.

It should be noted that backup is usually carried out while the database is running (on-line). This process is called "fuzzy backup" because it is performed over a period of time. If any changes occur while the database extents are being backed up, the copy process will of course continue. To maintain integrity, full and differential backups capture the portion of the transaction log file that corresponds to the time from the start to the end of the backup.

SQL Server can back up to a file located on your hard drive on network drive, on a magnetic tape device.

All changes made to the database are necessarily recorded in the transaction log. In the event of a disaster (such as a power outage or blue screen), the transaction log can be used to recover changes to the database. In addition, checkpoints are used to write all RAM pages to the hard disk, thereby reducing the time required to restore the database. So, if in control point All data pages are written to the hard drive, why do we need to store transaction information? In answering this question, we will have to talk about recovery models.

Each database running on SQL Server can adhere to one of three recovery models: Full, Bulk_Logged, and Simple.

The full recovery model provides greatest number options in case of data corruption. If all transactions are logged and full recovery mode is used, transactions are retained in the journal until they are backed up. Once the database is backed up, the disk space that was used to store transaction information becomes free and can then be used to log new transactions. Because all transactions are backed up, a full backup makes it possible to restore a database at a "point in time" using only those transactions that occurred before that point in time. For example, we can perform a restore using a full backup and then restore all copies of the transaction log up to a certain point before any important data was deleted.

If the full recovery model keeps track of all changes made to the database and allows us to recover all transactions to any point in time, then the question arises - why not always use this model? Since all statements must be fully committed, the output can be quite a “heavy” log file. Additionally, commands such as BULK INSERT will slow down the server because all changes they make must be logged.

The batch logging model (bulk_logged) is quite similar to the full recovery model, with some additions and advantages. Like the full recovery model, the batch logging model also captures all UPDATE, DELETE, and INSERT statements. However, for certain commands, in this model only the fact that the operation took place is recorded. This is true for commands such as BULK INSERT, bcp, CREATE INDEX, SELECT INTO, WRITETEXT and UPDATETEXT. The batch logging model is similar to the full recovery model in that it does not reuse (overwrite) log space until all transactions have been backed up.

Unlike the full recovery model, if the transaction log contained batch statements, then you cannot perform a point-in-time recovery; you must only recover to the end of the entire log. Also, a database log backup can be large in size because in the batch logging model, the log backup process must copy all extents that have changed.

The benefits of this model are that transaction log files for databases can be smaller if you use a lot of batch statements. In addition, batch statements are executed faster, since only the fact that the execution of this statement took place should be recorded, and not the change itself in the database.

A simple recovery model. Unlike the full and batch logging models, the simple recovery model does not use transaction log backups. In this model, transaction logs are often truncated (truncating is the process of removing old transactions from the log) automatically. Model easy recovery can use full and differential backups.

database administration document management

Hello, dear readers of the blog site. Today I want to talk about the concept of backing up files and databases of your resource.

Yes, of course, many hosters () perform backups automatically and, if something happens, you can turn to them for help. But as they say: rely on the hoster, but don’t make a mistake yourself.

Situations in which you may lose your project data, you can cite a lot, and you yourself have probably heard about it. You shouldn't rely on the mercy of your host. You need to make a backup yourself and store it on your computer.

This will be much more reliable and calmer. If, after all, your Internet project has collapsed, and there is nothing to restore it from, then try your luck in Webarchive (written about it in more detail here), because it constantly makes snapshots of the vast majority of sites on the Internet.

How to backup website files using FileZilla

As you probably already know, sites created on the basis of any engine, be it Joomla, WordPress or SMF, consist of two important parts:

1. Firstly, these are the actual files of the engine and the extensions installed in it, pictures and...
2. And secondly, these are databases where the texts of your articles, posts, etc. are stored.

The database (DB) can also store settings for some parameters of the engine and its extensions. I already wrote about this in an article about. Such an organization has many advantages.

So our task comes down to backing up all this wealth. Moreover, the frequency of database backup is usually determined by the frequency of new information appearing on your project. The optimal way, in my opinion, is to copy the database daily. Fortunately, they usually don’t weigh very much and such backups are carried out very quickly. You should probably update backup copies of your project files only after you have made some changes to them: installed some extensions, updated the engine version, etc.

Let's start, perhaps, with our first assistant called FileZilla, although you can use any other FTP manager instead, up to , but I prefer this particular free software creation. I have already described them in some detail in the above article, therefore we will not dwell on this in detail (if you want, read for yourself, especially about storing passwords in this program and the problems associated with it).

Let's look at how to backup files using it. Once you have access to your hosting server, you should go to the root folder (usually called public_html or htdocs). Remote server in Filezil is displayed on the right, and on the left is the contents of your computer.

If you plan to make backups regularly, then I advise you to create a folder on your computer’s hard drive with a “speaking” name, and inside it there are directories with the names of your projects. Within these directories you can create folders with current date, into which the files of your web project will be copied. Thanks to this, it will then be easier to navigate through backups and delete very outdated ones to free up space.

Now open on the left side of FileZilla the folder where the backup will be carried out, and on the right side - the root folder of the website. I advise you to enable the ability to show hidden files in the settings of this program: in top menu select “Server” - "Force hidden files to be shown".

This is necessary so that hidden files, such as .htaccess, are included in your backup. Next, you select all the objects on your site in the root directory while holding down the Shift button on your keyboard. Click on the selected objects right click mouse and select from context menu paragraph "Download".

File backup will begin, which may take quite a long time - depending on the number and total weight of the objects being copied, as well as the speed of the server. But you don’t have to watch the process of creating a backup. While copying, you can go about your business without closing Filezilla, of course.

At the end of the process you will feel better pack everything downloaded into one archive, because this can significantly reduce the volume and number of stored objects. After archiving, you leave only one archive, and delete everything downloaded - everything will be nice and neat. To restore site files from such a backup: you will need to unpack it and copy the contents of the archive to the server in a manner similar to that described above.

True, if you packed the files into a ZIP archive, then you can upload it to the server and unpack it there (described here how). But in this case, some troubles may arise later, which can be solved by PHP means (read the link about access rights and changing Cmod programmatically).

How to make a database backup using phpMyAdmin

Let's see how to backup a database using phpMyAdmin script. It can be accessed from your hosting control panel. If you have, then in order to launch phpMyAdmin, you need to follow the following path: find the area called “Databases” on the cPanel main page and click on the icon of this script there.

If your hosting does not have access to this script, then you you can do it yourself to the root folder of your site and access your database through it. You can download the program from here.

After downloading the archive to your computer, you must unpack it and upload the resulting folder (for simplicity, you can first rename it to phpmyadmin) into the root directory. In general, that's all. Now all that remains is to enter the following URL in the address bar of your browser: http://vash_sait.ru/phpmyadmin

In any case, the PhpMyAdmin program window will open, with which we can easily backup your project databases. This is the main page of the program (on some sites I have several outdated version, but I just got used to it):

If you are on any other phpMyAdmin page, then in order to get to the main page, you need to click on the house highlighted in the picture. On one account with the hoster you can have many databases and therefore you must first choose from the left menu base, which you want to back up.

You can see the list of databases in the program window on the left (under the house icon). In order to make a database backup you will need to click on the tab "Export" above the list of tables.

At the bottom of the page that opens, check the box "gzip". And click the "ok" button.

True, this is in the old (convenient) version of the script. Now, by default, you are offered to quickly download the database without compression, and if you want to customize something (including activating its gzip compression on the fly), you will need to check the “Normal” box and select gzip among many other settings , which is not very convenient in my opinion.

As a result, after some time (which depends on the speed of the server, its load and the size of your database), a standard copy dialog will open, in which you must select the location to save the backup of this database.

Restoring a database from a previously created backup

To restore a database from a backup, you need to proceed as follows. First, you must clear the existing database of all tables. To do this, you log into the phpMyAdmin program, select the desired database that you want to restore in the left column.

In the window that opens with the tables of this database, go down to the very bottom and under the list of tables click on "Select all". Then, again at the bottom of the page, select the item “With marked” from the drop-down list "Delete".

A window will open with a list of all tables to be deleted. You click on the “Yes” button.

You can now restore the database from a previously made backup. To do this, select the bookmark "Import":

In the window that opens, click on the “Select file” button and find the previously made backup of this database on your hard drive. Click on the “Forward” button (or “OK” in older versions of the script) at the bottom of the page and wait for the download to finish (the time again depends on the speed of the server and the size of the database). All.

Having up-to-date file backups and database backups on your computer, you can sleep peacefully. They can also be used when moving a site to another hosting.

Transferring a site to a new hosting

So, how can we transfer the site to a new location? After purchasing hosting, you will be provided with data to access the hosting server via FTP, which you will enter into the Filezila program to gain access to the server.

First, unzip the backup data on your computer and place it in the root folder, similar to the process described above. Without waiting for the files to be copied, you can begin restoring database tables from a backup made at the old location of your resource.

But for this you need to first go to a new hosting (where the tables you saved will be copied later). You will learn how to do this from the article about phpMyAdmin, the link to which I provided just above. Please note that you most likely will not be able to choose the same name for the database and its user as at your previous place of residence. The fact is that hosting usually adds your login to the database name you choose.

Therefore, after finishing copying the files and database, before accessing the site from the browser, you should enter the appropriate changes to your website engine settings. To do this, you will again need to access the site files via FTP and make changes to configuration files one or another engine (Joomla, WordPress, SMF, etc.). Let's look at the settings for each engine separately.

What to change in WordPress settings when migrating it

Transferring a blog to WordPress will require changing the following settings. You will need to open the file for editing using FileZilla WP-CONFIG.PHP, which is located in the root directory on the server. In it you need to edit the lines responsible for the name of the database and the user.

// ** MySQL Settings- You can get them from your host ** // /** Database name for WordPress */ define("WP_CACHE", true); //Added by WP-Cache Manager define("DB_NAME", "enter the new name of your database here"); /** MySQL username */ define("DB_USER", "enter new username here"); /** MySQL database password */ define("DB_PASSWORD", "anipiimaaxai"); /** MySQL server- sometimes you need to change this value, for example, on Masterhost */ define("DB_HOST", "localhost"); /** Database encoding used when creating tables. */ define("DB_CHARSET", "utf8"); /** Database mapping. DO NOT CHANGE THIS VALUE. */ define("DB_COLLATE", "");

After editing, save this file back and you can consider that the transfer of WordPress to the new hosting has been successful. If you change the domain name when transferring your blog, then in order for everything to work correctly, you will need to open the backup copy of the database with the SQL extension in a text editor (extract it from the gzip archive).

Next, using the built-in “search and replace”, find all references to the old URL of your blog and replace its new address (for example, vasy.ru with vova.ru). After this, save the file with the database backup and “Import” it in the phpMyAdmin program.

After you log into the WordPress admin area, you will need to enter the correct absolute path to the objects of your blog (it has changed because you moved WordPress to another hosting). The absolute path is set via the UPLOAD_PATH parameter in the global WP settings. You can get into these settings by adding to the URL home page following path:

/wp-admin/options.php

For my blog address it will look like this:

Https://site/wp-admin/options.php

But first you need to log in to the WordPress admin. read the link provided.

What needs to be changed in Joomla settings when changing hosting

Transferring a Joomla website to another hosting will require changing the following settings. You will need to open for editing CONFIGURATION.PHP in the root folder of the server. Find the lines in it that are responsible for gaining access to the database:

Var $user = "enter new username here"; var $db = "enter your new database name here";

In addition, you will also need to change the absolute path to the folders for storing logs and temporary files in Joomla. You need to change it in these lines:

Var $log_path = "/home/xxxxx/public_html/logs"; var $tmp_path = "/home/xxxx/public_html/tmp";

Transferring the SMF forum to a new hosting

Moving the forum to SMF will require changing some settings. You will need to open it for editing SETTINGS.PHP from the forum root folder. Just as in the case of Joomla, here you will also need to not only change the name of the database and SMF user, but also the absolute paths to the forum folder and the forum SOURCES folder.

########## Database Info ########## $db_server = "localhost"; $db_name = "enter your new database name here"; $db_user = "enter new username here"; $db_passwd = "hoighaebaeto"; $db_prefix = "smf_"; $db_persist = 0; $db_error_send = 1; ########## Directories/Files ########## # Note: These directories do not have to be changed unless you move things. $boarddir = "/home/xxxx/public_html/forum"; # The absolute path to the forum"s folder. (not just "."!) $sourcedir = "/home/xxxx/public_html/forum/Sources"; # Path to the Sources directory.

But besides this, after transferring SMF to a new hosting, you will need to change the absolute path to the currently installed folder. To do this, you will need to go to the forum admin area and select “Current theme” from the left column. In the window that opens, in the “Theme folder” area, you enter the absolute path to the desired folder.

How to start working with a website immediately after transferring it to a new hosting

You attached yours (website, in my case) to it. Or you, in accordance with the above, carried out the transfer. In principle, it doesn't matter, but you will have to link anyway new server with a domain. To do this, you will find your new host in the control panel of your registrar (where you purchased the domain name).

You can see the DNS server addresses in the letter your new hoster will send you. Where exactly you need to enter these DNS in the registrar panel, it’s hard to say for sure, but it should not be buried deeply and lie in plain sight. As a last resort, contact technical support.

So, despite the successful transfer of the site to a new host, you still have to wait from several hours to a couple of days while your domain is being delegated. Until this process is completed, your resource will not be available at your new location.

Sometimes the hosting owner may indicate in a letter a technical address at which you can access your resource while the records on all DNS servers on the Internet are updated. But this doesn't always happen. In addition, for example, for WordPress, the technical address will not allow you to fully start working with the newly transferred blog, because this engine is strictly tied to the domain name.

But the owner of the host always indicates the IP address of your new server in the letter. Using it, you can access your resource without waiting for DNS registration. But in this case, only you will get access and only on the computer where you make the settings described below. So, you need to do the following:

1. using any file manager open for editing (at this link you will find a detailed article on where this file is located, how to find it in Windows 7 and what should be written in it), located at the following path: c:\Windows\System32\drivers\etc\ hosts
2. at the end of the HOSTS content you need to add the line: 109.77.43.4 site where at the beginning there is the IP address of the new server, and after it, separated by a space, the domain
3. save this file and you can safely type in your browser the address of the resource that you just transferred (you may need to reset the DNS cache on your computer - read about this in the article just above about the Hosts file)

Thus, without waiting for the domain to be delegated, you can already check the functionality of the transferred resource and, if necessary, fix everything before it becomes available to all other visitors. After the domain is delegated, you will need remove added line in HOSTS.

You can also look at a video on the topic from a well-known website builder in RuNet:

Well, and a selection video tutorials on transferring a Joomla CMS website to hosting I advise you to look. They will be played one after another automatically, and if you want, you can switch to the next lesson using the corresponding button on the player panel or select the desired lesson from the drop-down menu in the upper left corner of the player window:

Enjoy watching!

Good luck to you! See you soon on the pages of the blog site

You can watch more videos by going to

");">

You might be interested

Database servers are one of the key servers in any organization. They are the ones who store information and provide output upon request, and it is extremely important to preserve the database in any situation. The basic package usually includes the necessary utilities, but an administrator who has not previously encountered a database will have to spend some time understanding the peculiarities of the work in order to ensure automation.

Types of database backups

First, let's figure out what kind of backups there are. A database server is not a regular desktop application, and to ensure that all ACID (Atomic, Consistency, Isolated, Durable) properties are met, a number of technologies are used, and therefore creating and restoring a database from an archive has its own characteristics. There are three different approaches to backing up data, each with their own pros and cons.

With a logical, or SQL, backup (pg_dump, mysqldump, SQLCMD), a snapshot of the contents of the database is created, taking into account transactional integrity and saved as a file with SQL commands (you can select the entire database or individual tables), with which you can recreate the database on another server. This takes time (especially for large databases) to save and restore, so very often this operation cannot be performed and is performed during minimal load (for example, at night). When restoring, the administrator will need to run several commands to prepare everything necessary (create an empty database, Accounts And so on).

Physical backup (level file system) - copying files that the DBMS uses to store data in the database. But when simple copying locks and transactions that are most likely to be incorrectly saved and broken are ignored. If you try to attach this file, it will be in an inconsistent state and will result in errors. To get an up-to-date backup, the database must be stopped (you can reduce downtime by using rsync twice - first on a running one, then on a stopped one). The disadvantage of this method is obvious - you cannot restore specific data, only the entire database. When starting a database restored from a file system archive, you will need to check its integrity. Various assistive technologies are used here. For example, in PostgreSQL there are WAL (Write Ahead Logs) proactive logging logs and a special function (Point in Time Recovery - PITR) that allows you to return to a specific database state. With their help, the third scenario is easily implemented, when a file system level backup is combined with a backup copy of WAL files. First, we restore the backup files of the file system, and then, using WAL, the database is brought to the current state. This is a slightly more complex approach for administration, but there are no problems with the integrity of the database and restoring databases to a certain time.

Logical backup is used in cases where it is necessary to make a one-time full copy of the database or in everyday use, creating a copy does not require much time or space. When unloading databases takes a lot of time, you should pay attention to physical archiving.

Barman

License: GNU GPL

Supported DBMS: PostgreSQL

PostgreSQL supports physical and logical backup capabilities, adding another WAL layer to them (see sidebar), which can be called continuous copying. But managing multiple servers using standard tools is not very convenient even for an experienced administrator, and in the event of a failure, seconds count.

Barman (backup and recovery manager) is an internal development of the 2ndQuadrant company, which provides services based on PostgreSQL. Designed for physical PostgreSQL backup (logical does not support), WAL archiving and quick recovery after failures. Supports remote backup and recovery of multiple servers, point-in-time-recovery (PITR) functions, and WAL management. SSH is used to copy and send commands to a remote host; synchronization and backup using rsync allows you to reduce traffic. Barman also integrates with standard utilities bzip2, gzip, tar and the like. In principle, you can use any compression and archiving program, integration will not take much time. Various service and diagnostic functions have been implemented to monitor the status of services and regulate bandwidth. Pre/Post scripts are supported.

Barman is written in Python, and backup policies are managed using the user-friendly barman.conf INI file, which can be located in /etc or the user's home directory. The delivery includes a ready-made template with detailed comments inside. Works only on *nix systems. To install on RHEL, CentOS and Scientific Linux, you should connect EPEL, a repository that contains additional packages. The official repository is available to Debian/Ubuntu users:

$ sudo apt-get install barman

The repository does not always have the latest version; to install it, you will have to refer to the source texts. There are few dependencies and the process is easy to understand.

Sypex Dumper

License: BSD

Supported DBMS: MySQL

MySQL comes with the mysqldump and mysqlhotcopy utilities, which allow you to easily create a database dump; they are well documented, and you can find a large number of ready-made examples and frontends on the Internet. The latter allow a beginner to quickly get started. Sypex Dumper is a PHP script that allows you to easily create and restore a copy of a MySQL database. Created to work with large databases, it works very quickly, is understandable and easy to use. Knows how to work with MySQL objects - views, procedures, functions, triggers and events.

Another plus, unlike other tools that perform transcoding in UTF-8 when exporting, in Dumper the export is performed in the native encoding. The resulting file takes up less space and the process itself is faster. One dump can contain objects with different encodings. Moreover, it is easy to import/export in several stages, stopping the process during load. When resuming, the procedure will begin from where it stopped. There are four options available for recovery:

• CREATE + INSERT - standard recovery mode;
• TRUNCATE + INSERT - less time for creating tables;
• REPLACE - we restore old data in the working database without overwriting new ones;
• INSERT IGNORE - we add deleted or new data to the database without touching existing ones.

Supports copy compression (gzip or bzip2), auto-deletion of old backups, viewing the contents of a dump file, and restoring only the table structure. There are also service functions for managing the database (creating, deleting, checking, restoring the database, optimization, cleaning tables, working with indexes, etc.), as well as a file manager that allows you to copy files to the server.

Management is carried out using a web browser, interface with using AJAX localized out of the box and gives the impression of working with a desktop application. It is also possible to run jobs from the console and on a schedule (via cron).

For Dumper to work, you will need a classic L|WAMP server; installation is standard for all applications written in PHP (copy files and set permissions), and will not be difficult even for a beginner. The project provides detailed documentation and video tutorials demonstrating how to use Sypex Dumper.

There are two editions: Sypex Dumper (free) and Pro ($10). The second has more features, all the differences are listed on the website.

SQL Backup And FTP

License:

Supported DBMS: MS SQL Server

MS SQL Server is one of the popular solutions, and therefore occurs quite often. A backup job is created using SQL Server Management Studio, Transact-SQL itself, and SQL PowerShell module cmdlets (Backup-SqlDatabase). On the MS website you can easily find great amount documentation that helps you understand the process. The documentation, although complete, is very specific, and information on the Internet often contradicts each other. A beginner will really need to practice first, “getting good at it”, so, even despite everything that has been said, third party developers there is room to turn around. In addition, the free version of SQL Server Express does not have built-in backup tools. For earlier versions of MS SQL (before 2008), you can find free utilities, for example SQL Server backup, but in most cases such projects have already been commercialized, although they offer all the functionality often for a symbolic amount.

For example, SQL development Backup And FTP and One-Click SQL Restore follows the “set it and forget it” principle. With a very simple and clear interface, they allow you to create copies of MS SQL Server (including Express) and Azure databases, save encrypted and compressed files on FTP and cloud services (Dropbox, Box, Google Drive, MS SkyDrive or Amazon S3), the result can be viewed immediately. It is possible to launch the process either manually or according to a schedule, send a message about the result of the task by email, or run custom scripts.

All backup options are supported: full, differential, transaction log, copying a folder with files and much more. Old backups are deleted automatically. To connect to a virtual node, use SQL Management Studio, although there may be nuances here and this will not work in all such configurations. There are five versions available for download - from the free Free to the sophisticated Prof Lifetime (at the time of writing these lines it cost only $149). Free's functionality is quite sufficient for small networks with one or two SQL servers installed, all basic functions are active. The number of backup databases, the ability to send files to Google Drive and SkyDrive, and file encryption are limited. Although the interface is not localized, it is very simple and understandable even for a beginner. You just need to connect to the SQL server, after which a list of databases will be displayed, you should select the ones you need, configure access to remote resources and specify the time for the task to complete. And all this in one window.

But there is one “but”. The program itself is not intended for archive recovery. For this, a separate free utility, One-Click SQL Restore, is offered, which also understands the format created by the BACKUP DATABASE command. The administrator only needs to specify the archive and server to which to restore the data, and press one button. But in more complex scenarios you will have to use RESTORE.

Features of MS SQL Server backup

Creating a backup copy and restoring a DBMS has its own differences that need to be taken into account, especially when transferring an archive to another server. As an example, let's look at some of the nuances of MS SQL Server. To archive using Transact-SQL, use the BACKUP DATABASE command (there is also a DIFFERENTIAL command) and the BACKUP LOG transaction log.

If the backup is deployed on another server, you need to make sure that the same logical drives. As an option, you can manually specify the correct paths for the database files using the WITH MOVE option of the RESTORE DATABASE command.

A simple situation - backup and transfer of databases to other versions of SQL Server. This operation is supported, but in the case of SQL Server it will work if the version of the server on which the copy is deployed is the same or newer than the one on which it was created. Moreover, there is a limitation: no more than two versions are newer. After restoration, the database will be in compatibility mode with the version with which the transition was made, that is, new functions will not be available. This can be easily fixed by changing COMPATIBILITY_LEVEL. You can do this using GUI or SQL.

ALTER DATABASE MyDB SET COMPATIBILITY_LEVEL = 110;

You can determine which version the copy was created on by looking at the archive file header. To avoid experimentation, when upgrading to a new version of SQL Server, you should run free utility Microsoft Upgrade Advisor.

Iperius

License: commercial, there is a Free version

Supported DBMS: Oracle 9–11, XE, MySQL, MariaDB, PostgreSQL and MS SQL Server

When you have to manage several types of DBMS, you cannot do without combines. The choice is large. For example, Iperius is a lightweight, very easy to use, yet powerful file backup program that features hot database backups without interruption or blocking. Provides full or incremental backup. Can create full disk images to automatically reinstall the entire system. Supports backup to NAS, USB devices, streamer, FTP/FTPS, Google Drive, Dropbox and SkyDrive. Supports zip compression with no limit on file size and AES256 encryption, launching external scripts and programs. Includes a very functional task scheduler, parallel or sequential execution of several tasks is possible, the result is sent by email. Numerous filters, variables for personalizing paths and settings are supported.

FTP upload capability makes it easy to update information across multiple websites. Open files are backed up using VSS (volume shadow copy) technology, which allows you to make hot backups of not only DBMS files, but also other applications. For Oracle, the RMAN (Recovery Manager) backup and recovery tool is also used. To avoid overloading the channel, it is possible to configure the bandwidth. Backup and recovery are managed using a local and web console. All functions are visible, so to set up a task you only need to understand the process; you don’t even have to look at the documentation. We just follow the wizard's instructions. You can also note the account manager, which is very convenient when large quantities systems

Basic functions are offered free of charge, but the ability to back up a database is included only in the Advanced DB and Full versions. Installation from XP to Windows Server 2012 is supported.

Handy Backup

License: a commercial

Supported DBMS: Oracle, MySQL, IBM DB2 (7–9.5) and MS SQL Server

One of the most powerful control systems relational databases data - IBM DB2, which has unique scaling features and supports many platforms. It comes in several editions, which are built on the same base and differ functionally. DB2 database architecture allows you to manage almost all types of data: documents, XML, media files, and so on. The free DB2 Express-C is especially popular. Backup is very simple:

Db2 backup db sample

Or a snapshot using the Advanced Copy Services (ACS) feature:

Db2 backup db sample use snapshot

But we need to remember that in the case of snapshots, we cannot recover (db2 recover db) individual tables. There are also opportunities for automatic backup, and much more. The products are well documented, although manuals are rare on the Russian-language Internet. Also not in all special solutions you can find DB2 support.

For example, Handy Backup allows you to backup several types of database servers and save files to almost any media (hard drive, CD/DVD, cloud and network storage, FTP/S, WebDAV and others). Database backup is possible via ODBC (tables only). It is one of the few solutions that supports DB2 and also carries the "Ready for IBM DB2 Data Server Software" logo. The entire procedure is performed using a regular wizard, in which you only need to select the desired item and create a task. The setup process itself is so simple that even a beginner can figure it out. You can create several tasks that will run on a schedule. The result is recorded in a log and sent by email. There is no need to stop the service while the job is running. The archive is automatically compressed and encrypted, which ensures its security.

Two versions of Handy Backup support working with DB2 - Office Expert (local) and Server Network (network). Works on computers running Win8/7/Vista/XP or 2012/2008/2003. The deployment process itself is simple for any administrator.