Backing up Microsoft SQL Server databases. Database backup

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 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). The remote server in Filezil is displayed on the right, and the contents of your computer are displayed on the left.

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. Inside these directories, you can create folders with the 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 the 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. Right-click on the selected objects and select the item from the context menu "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 (here it is described 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 a slightly 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 re-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 "Mark 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 the configuration files of 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 hoster ** // /** 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 to these settings by adding the following path to the URL of the main page:

/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

Migrating 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.

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 associate the new server with the domain anyway. 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 (follow 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 in 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 watch a video on the topic from a well-known site builder in RuNet:

Well, and a selection video tutorials on transferring a Joomla CMS website to hosting I advise you to watch it. 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

Michael Vandine, Chief Technical Advisor

Introduction

A recovery strategy must be given due consideration to prevent accidental data loss. Data recovery capabilities must be at the same high level as backup tools.

Database recovery is data administrators' answer to Murphy's Law. Databases inevitably become damaged or lost for a variety of reasons: system or hardware failures, operator errors, incorrect or invalid data, software errors, computer viruses, and natural disasters.

Since every organization is highly dependent on its databases, it is necessary to follow the right strategy to quickly and accurately restore a database after it is lost or damaged. Additionally, since data recovery must be as good as data backup, this strategy must also include a consistent data backup system.

The following describes the main features that must be implemented to ensure accurate recovery:

1. Back up enough data to return the database to a consistent state, regardless of its current state.
2. Logging transactions and database changes.
3. Restoring a database with enough options to bring it back to its original working state, with minimal loss of data and time.

This article describes the options for each of these features that are available for SQLBase databases. Each of these options must be considered based on your specific situation. The best choice for a particular operation must be made based on various factors, including acceptable data loss or downtime, the amount of available disk space, and the size of the database.

Database backup

It describes various options for creating backup copies to provide the ability to restore data to the desired state.

A backup consists of a database (a file with a .bkp extension) and all the log files required to bring the .bkp file to a consistent state during the backup. It is important to remember that regardless of the type of backup performed, the data being backed up must be intact. Therefore, it is recommended to "check the database" before performing a backup and to perform this procedure only when the database is in a stable state.

Backup can be done either online using SQLBase backup software (SQLBase server is running and users are connected to the database) or offline using standard operating system commands, i.e. using COPY (SQLBase server is disabled or the database is unmounted) and then the "set nextlog" command. The most commonly used option is online backup because for most operations it is very difficult to find a suitable time when all users are disconnected from all databases so that the SQLBase server can be shut down gracefully or all databases can be unmounted. This is necessary because the SQLBase server maintains the database as an open file from the time the first process mounts until the database is dismounted or the SQLBase server is shut down.

SQLBase uses transaction log files to record the original and converted form of database change records and as regular transaction control checkpoints. They are used for disaster recovery (which occurs automatically after a power outage, server failure, etc.), rollback (if unnecessary changes have been made to the database or a failure causes a transaction to be cancelled), and for data recovery (database recovery).

Please note that the database consists of a file with the DBS extension and log files. If log files are deleted, the database becomes unusable! By default, SQLBase automatically deletes log files after they are backed up, or when they are no longer needed for disaster recovery or rollback. This behavior can be changed by setting the database's LOGBACKUP option to ON using the SQLTalk interface. With this setting, log files are saved until they are backed up using the special "backup logs" command. This is the only way to delete log files. This parameter must be set to ON if the "backup database" or "backup logs" commands are to be used. Conversely, you do not need to set this parameter to this position if you do not intend to use the "backup logs" command or implement the "rollforward" capability in your recovery strategy, i.e., if you intend to take only "snapshots" ".

The maximum size of log files generated per database can be specified by setting the LOGFILESIZE parameter using SQLTalk (the default size is 1 MB). Log files start at a minimum size, and then they grow in size up to the specified limit. If you set the LOGFILEPREALLOC parameter to ON using SQLTalk, you can create log file templates of the maximum size for each database. If you need a lot of log files, you can set the maximum size to be large (say 5 MB) and pre-assign the file size. This will reduce the number of I/O operations required to create new files or append existing ones.

Log files can only be deleted after using the "release log" or "backup logs" command and if they are not "locked". The log file becomes blocked in the following cases:

1. The current transaction writes to this log file. The lock can be released from this file only after the transaction completes or rolls back.
2. The penultimate checkpoint was created using this or the previous log file. For example, if a checkpoint was created in 6.log, then this log file and all subsequent ones will be blocked. In this case, the lock can be released using the "release log" command.
3. The LOGBACKUP parameter is ON and this log file is not yet backed up. The lock can only be removed from it using the "backup logs" command. Executing the "backup snapshot" command will not have any effect on blocking this log file.

Log files that have been backed up should be manually deleted regularly. Only log files that are directly related to the BKP file in the backup area are saved. For example, if a snapshot backup is created on a particular day, all previous log file backups may be deleted. Be careful: these files can take up a significant amount of disk space.

The advantage of this method is that there is no need to interrupt the work of users currently connected to the database. This is especially important for operations that require 24/7 access to the database. The online backup format is:

There are two main options for online reservations. The first corresponds to the use of the "backup snapshot" command (complete in itself), and the second to the sequential use of the "backup database", "release log" and "backup logs" commands (all of which are necessary for a consistent backup).

Regardless of the option chosen, backup consists of the following sequence of steps:

Creating a BKP file in the selected backup area (backup database);
- updating the most recent log files, since they contain all the information about the current reservation (release log);
- Copies all relevant unlocked log files preceding the current log file to the selected backup logs area.
When using the backup snapshot method, all these steps are performed automatically.

When performing a backup, the destination of the data can be the client computer or the network itself. This is specified in the "directory-name" option of the backup command. It is also necessary to have a good understanding of the difference between the "on server" and "on client" options of this command. When using the "on client" switch, all data and log files will be placed in the specified directory of a specific computer (even if it is a network server). Using the "on server" key will result in a backup directly on the server without first saving the data on the client computer. This can result in significant gains in backup speed and reduction in network traffic!

If the "on client" key is used, i.e. data is saved on the client computer, the directory must be specified as a full local path or as a network drive, for example, C:\SQLBASE\BACKUPS\DB1 (local path) or F:\BACKUPS\DB1 (network drive). However, when using the "on server" key, the specified disk must be local to the server, and not a network one. If a Novell server is used, the path to the desired directory must be specified in Novell server format. For example, if the database is backed up to a directory:

SERVER1\SYS:SQLBASE\BACKUPS\DB1

A snapshot of the DB1 database can be taken as follows:

BACKUP SNAPSHOT FROM DB1 TO \SQLBASE\BACKUPS\DB1 ON SERVER;

Please remember that you do not need to have write access or a connection to the server or directory where the data is stored. The server can store backups in its root directory without any problems if it has been instructed to do so. Therefore, you should be careful when setting their destination! Also, don't make the mistake of placing backups in a subdirectory of the database itself. When you delete a database (before restoring it), its entire directory with all subdirectories is deleted. In this case, you can say goodbye to backups.

Creating snapshots (the backup snapshot command) is the simplest method of backing up databases. This option copies the database and log files necessary to restore the database to a consistent state. Before the log files are backed up, they are updated to include the currently active log file in the backup. This backup option requires one command to back up the data and one to restore it. Typically this method is used once in the evening, before backing up the server to tape, and maybe once during the day. The disadvantage of this method is that taking snapshots of a very large database can be time consuming, and recovery options are limited to returning the database to the state it was in when the last snapshot was taken. Any changes made after this snapshot will be lost.

This data backup method is a bit more complex, but the recovery flexibility is worth the extra effort. It requires backing up the database file, unlocking the log file (since it contains information about the current backup), and then backing up the log files. Attention! Never back up just one database without log files. This backup method allows you to back up the database and its log files occasionally, and then back up just the log files on a regular basis, for example several times a day. This allows you to restore the database and then use the rollforward feature of the log files to recover the work done during the day. How often log files are backed up depends on the amount of disk space available and how much data the user can afford to lose. Note: the accumulation of log files can lead to disk filling very quickly. This approach is especially useful for large databases because time-consuming database file backups are rarely performed, but periodic log file backups can be completed quickly. The disadvantage of this method is that log files accumulate before they are backed up.

In the past, any SQLBase database larger than 2 GB had to be split into parts. Starting with SQLBase version 7.5.0, this limit was raised for databases on all operating systems except the Novell family. Now their size can grow up to 256 GB without the need for partitioning. However, this limitation applies only to the DBS file, and not to temporary files or with the .bkp extension. To cope with this limitation, a new method of segmented backup was introduced.

Configure the control file and place it in the same directory where the backups are saved. There is no need to make any changes to backup statements or running programs. The SQLBase server finds the control file in the target directory and segments the backup accordingly. The control file has the extension .bcf, and its name matches the name of the database being backed up. You can use any text file editor (for example, notepad.exe) to create this file.

The control file has the following format:

FILEPREFIX prefix
DIR directory where the segment backup is placed
SIZE size in MB

You can specify multiple DIR statements to split the database into segments, each smaller than 2 GB. Note that the total volume specified by all SIZE statements must be large enough to allow the entire database to be backed up. For example:

FILEPREFIX MIKE
DIR C:\BACKUPS\MIKE SIZE 1000
DIR C:\BACKUPS\MIKE SIZE 1000
DIR C:\BACKUPS\MIKE SIZE 500

allows you to create three files in the C:\BACKUPS\MIKE directory:

MIKE.1 1 GB
MIKE.2 1 GB
MIKE.3 0.5 GB

Please note that these files are only created if the database requires that much space. In the above example, if the database size was only 1.8 GB, then only two segments would be created: MIKE.1 and MIKE.2 of size 1 GB and 0.8 GB, respectively.

Can be used: provided by the company Gupta SQLTalk interface for executing the commands shown above; SQLConsole product (uses C/API calls) to schedule backups; proprietary software that, when making a backup, takes into account all possible features of a particular system, for example, it checks the database before backup.

SQLConsole - This product is supplied by Gupta and, with its superior monitoring capabilities, allows for scheduled backups, including database checks and statistics updates. This rather complex product has a large set of settings for performing backups.

Above we discussed the main points and recommendations for online backup. Let's give them a brief overview:

1. Please "check database" before backing it up. If the check finds errors in the database, do not write a backup copy of it over the intact copy.
2. If you are not planning to create a snapshot, check the backup sequence, i.e. the order of the commands ""backup database", "release log" and "backup logs".
3. To save time, use the "on server" switch whenever possible!
4. When using the "on server" switch on a Novell server, the path to the target directory must be specified in Novell server format, and not as a network drive.
5. Do not delete ANY log files from their current storage directory, specified by the logdir= statement in the server configuration file called sql.ini (or the dbdir= statement if logdir= is not defined).
6. Periodically delete old log files from the backup area to free up disk space. Do not erase files related to the current BKP file. It is safe to remove from the backup area any log files that were created before the current BKP file.
7. If the LOGBACKUP parameter is ON and the "backup snapshot" command is used, the log files will not be unlocked. To achieve this, run the "backup logs" command.
8. Do not place backups in a subdirectory of the database itself.
9. If the client computer is rebooted while the snapshot backup is being created, when you try to back up the database again, you may receive a message indicating that a backup is already in progress. This can happen when you connect to the server before your previous session has ended. You can resolve this issue by terminating the interrupted session using SQLConsole or restarting the SQLBase server.

The advantage of offline backup is that the backed up data goes directly to the archive. In this case, you can achieve significant disk space savings because the volume required to store the backup is equal to the size of the original database.

To create backup copies of the database DBS file and all log files in its directory, use operating system commands or utilities (for example, the "copy" command or the ARCServe tools). If the LOGBACKUP parameter is set to OFF, log files will not be copied. However, if it is set to ON, you must tell the SQLBase server whether the log files have been backed up or whether they will remain "locked". This can be done using the "nextlog" command from Gupta's SQLTalk interface. This requires being connected to the database. The following format is used:

SET NEXTLOG [integer]

The given number specifies the next log file to back up. For example, if the last backups sent to the archive were 4.LOG and 5.LOG, you need to run the "set nextlog 6" command.

The disadvantage of offline backup is that it requires dismantling the database or shutting down the SQLBase server. This is because from the moment you first connect, the SQLBase server acts as a write subscriber to the database, and this connection does not break until the database is dismounted or the server is turned off. This means that all users must be disconnected in order to dismount the database or shut down the SQLBase server. Finding the right time or disconnecting users who forgot to log out before leaving work can be a real hassle!

Database recovery

This describes the various options for restoring a database to a consistent state based on the backup settings you select.

The recovery kit consists of backup copies of the BKP file and its associated log files. A BKP file is useless without associated log files. The recovery process essentially takes place in three stages:

1. Copy the BKP file to the database directory.
2. Copy the log files to the database directory.
3. Run a two-step (redo and undo) rollforward process on the new DBS file.

The restore command has the following format:

If you use the "restore snapshot" command, no further action is required as all recovery steps will be completed automatically. If you select the "restore database" option, you must run the "rollforward" command after restoring the database. It should be noted that, as in the backup phase, when using the "on server" switch (which significantly reduces recovery time) on a Novell server, the path to the target directory must be specified in Novell server format, and not as a network drive. The rollforward process is to restore all changes made since the database was backed up to bring it into a consistent state. Log files contain information about all transactions, both successful and those that were rolled back. During the rollforward process, log files are accessed twice. During a "redo" pass, SQLBase localizes the starting point of all transactions and applies all successful transactions to that backup. The "undo" pass reverses all transactions that were rolled back. At the end of the rollback process, the database is in a fully consistent state with no pending transactions. The "rollforward" command has the following format:

Using the "rollforward to backup" command will restore all work done up until the moment the database backup was created. This is equivalent to executing the "restore snapshot" command. This does not restore any additional log files that may have been backed up since the original backup was created.

The "rollforward to time" command allows you to restore data up to a specific point in time. This is very good for rolling back a significant amount of erroneous changes. For example, if some new careless programmer deleted half the database and committed the change, it can be restored to its state at the time of deletion using a backup and the rollforward process.

As a result of the "rollforward to end" command, all log files with sequential numbers are processed, starting with the first file from the moment the backup was created. In this case, the maximum possible amount of work is restored. After the last log file has been processed, a message will appear stating that the next log file cannot be found. This is fine! Simply issue the "rollforward end" command to complete the restore and rollforward process.

Please note that ALL log files must be available and used consistently for the rollback to be successful. If any log files are lost, the rollforward operation will stop waiting for missing files. If possible, you can use the "restore logs" command to make the desired log files available, and then use the "rollforward continue" command to continue the process. If any of the required log files are not available, use the "rollforward end" command to end the process. The database will be restored only taking into account the last processed log file.

A couple of tips on what you need to pay special attention to when performing database recovery:

1. After the "restore snapshot" command completes, the message "cannot connect to the database" may appear. Essentially this is a timing problem. Once the restore is complete, the old database is deleted, the BKP file is copied from the backup directory to the database directory, and installed. The recovery process then attempts to connect to the database to process the log files. If the system is busy or the SQLBase server is too slow to process the database installation update message in time, the restore process cannot connect to it. The solution to this problem is to increase the value of the CONNECTTIMEOUT parameter in the router section of the sql.ini configuration file. This parameter specifies the amount of time (in seconds) to wait after a failed connection to the server before attempting the next connection. For example, if you need to set the CONNECTTIMEOUT parameter to 20 to successfully connect to the database, create a line in the router section with the value: connecttimeout=20.
2. Having just a BKP file and no log files means a big problem. However, in this case you can try to do something. Sometimes it works, but nothing is guaranteed. Let's assume the database is called MIKE.BKP. Using SQLTalk, issue the set server command, and then do the following:

RESTORE DATABASE FROM [path to backup directory] ON SERVER TO MIKE;

A message appears<> (The database has been restored. Use the rollforward command to complete the restore.)

ROLLFORWARD MIKE TO BACKUP; (Note that "to backup" seems to be the only option that might work)
A message appears<> (You need to restore the database first).

ROLLFORWARD MIKE TO BACKUP; (Yes, run this command again!) A message will be displayed<> (rollforward process completed).

CONNECT MIKE 1 username/password;
A message appears<> (Connection to MIKE database established).
Check the database ("check database" command) to check its status.

Scenarios

It compares different backup methods and the resulting recovery capabilities.

Below are several comparisons of different backup methods to give an idea of ​​disk space requirements and recovery options. The following is assumed: the standard log file size is 1 MB, their counting starts at 1.LOG, templates of the maximum size are created for all log files, and all transactions are completed before backups are created.

If the system fails at this point, recovery options will only be available for the previous backup. Recovering the last three transaction log files is not possible because the continuity of the log file sequence is broken (files 1-7 in the database area were unlocked because the transactions were completed and did not require crash recovery or rollback).

After the snapshot:

Database area Backup area

Note that the 4.LOG file in the backup area is intended to be deleted because the corresponding transactions are included in the DB1.BKP file, and it was created before this file.

Note that all log files are in the database area before they are backed up by the "backup logs" command, even if all transactions have completed.

Note that log files are automatically removed from the database area because they have already been backed up and there are no pending transactions.

If a system failure occurs at this point, recovery options are available for the most recent transaction by restoring the BKP file, processing log files 1-4 from the backup area first, and then log files 5-8 from the database area.

_____________________________________________
After completing the "release log" and "backup logs" methods

Note that no log files from the backup area are intended to be deleted as they ALL must be used in conjunction with DB1.BKP from the same area to make it consistent with the DB1.DBS file from the database area.

Note that once the database and log files have been backed up, log files 1-10 from the backup area can be released since the new BKP file should contain all relevant transactions and the date and time of their creation will be earlier. than the file DB1.BKP.

Conclusion

Anyone who has never had to recover their data can be considered a lucky person. However, statistics say that sometimes this has to be done! Develop your data recovery strategy now, before the real need arises.

Copying databases

The databases used are divided into two categories: 3 system databases (oktell, oktell_cc_temp and oktell_settings) and a database for Okapp web client modules. To start Oktell after recovery, only system databases are needed. The remaining databases are only needed if you want to save your web module settings.

For example, the WO_Module_journal database is used by the module Magazine stores tags of conversation recordings. The WO_Module_dashboards database is needed for the Okboard Dashboards module to work and contains settings for the indicators used.

Step 1. Copies of system tables are created automatically every day, by default at 02:00 server time, unless the DBAutoDailyBackup option is disabled. The creation of copies occurs in a special way, leaving copies

• last two weeks - every day
• then three months - once a week
• then two years - every month
• then once a year

All copies are located in the \oktell\server\Backup folder unless overridden in the DBBackupDir parameter.

In turn, you can make backups at any time. To do this, go to Administration/General settings/Database management. Click the button Perform a database backup.

After the backup is completed, the created backups will be available in the root of the folder oktell\server\backup.

Step 2. To create copies of other databases, open SQL Server Management Studio. Right-click on the desired database and select Tasks in the context menu, then Create a backup. In the window that opens, you can change the path for creating a backup; to start copying, click OK. By default, copies are created in the folder C:\Program Files\Microsoft SQL Server\MSSQL11.OKTELL\MSSQL\Backup\.

Database recovery

Databases can only be restored to the same version of SQL server or higher. If databases were created on SQL Server 2008 R2, they cannot be restored to SQL Server 2008.

Step 1. Stop the oktellserver service. Launch a command prompt with administrator rights and enter the following line:

Net stop oktellserver

Step 2. Start SQL Server Management Studio with the sa account:

• Login: sa
• Password: 123 Oktell321

Step 3. If you have previously installed Oktell databases, you need to delete them. This applies to system databases and databases used by web modules.

Step 4. Let's begin the recovery procedure. Right-click on System Database and click Restore Database(Restore backup).

Select the file with a copy of the databases. To do this, select the Device item in the Add window that opens and select your backup file, for example db_ok_130628.bak(in this case, this is the oktell database).

Type the name of the database at the top of the window that you are restoring. You can see the name of the database at the bottom of the window. Don't forget to check the box Restore(Restore).

Repeat the same with the remaining databases.

Step 5. After restoring the databases, you must create database users for full operation. To do this, download and run the following query.

Step 6. If you have transferred the database to a third-party server, check the settings in the server configuration file \oktell\server\oktell.ServerService.exe.config. Make sure that in the line with the DBConnectionString key, the link to the database, login and password are correct. By default, the connection string looks like this:

Server=(local)\OKTELL;database=oktell;uid=AutelService;pwd=;pooling=true

The new server name must be specified instead of the value (local)\OKTELL. For example, the SQL server is moved to the WORK server with the IP address 192.168.0.3. Therefore, in the parameter you need to specify WORK\OKTELL. If the server does not start with this setting, try specifying only the server name without the instance - WORK. Instead of the server name, you can specify the IP address - 192.168.0.3/OKTELL or just 192.168.0.3 .

If you have changed your main AutelService account, you must specify a new login and password in the fields uid And pwd respectively.

You can always find out the name of your server (instance) using the command

Sqlcmd.exe -L

on the Windows command line.

Step 7 Start the service oktellserver. To do this, on the command line run