sql backup. Backing up Microsoft SQL Server databases

The extensive functionality of Bacula Enterprise Edition, among other things, allows you to quickly and easily create database backups for . For example, we are talking about a tool with which you can backup MS SQL Server. The user can make a backup of MS SQL by creating large-volume backups of specific MS SQL databases used by the Windows platform, at lower costs for third-party software, with the ability to restore data to a certain point in time (PITR recovery) on the network and local disk.

The Bacula Systems script for creating MS SQL Server backups is characterized by extreme efficiency, achieved through the implementation of a modern, highly reliable architecture. Moreover, the software allows you to make a backup of MS SQL Server and use a variety of options for creating MS SQL backups.

The MS SQL Bacula Systems backup script operates independently of VSS. This means that the tool Reserve copy MS SQL does not use VSS snapshots to create backups. Therefore, the user can set the following value “Enable VSS = no” in Bacula FileSet. Effective creation of MS SQL Server backups and their restoration using this solution is achieved through using Microsoft API for SQL Server. This allows Bacula Systems to support the security mechanisms and all types of authentication implemented in Microsoft SQL Server.

MS SQL transaction log backup and MS SQL point-in-time recovery: Bacula Enterprise Edition software allows you to recover MS SQL data blocks or specific settings to a specific point in time. With the implementation of full and bulk-logged recovery models, you can recover MS SQL using PITR recovery, or use LSN to restore the system to a specific state. You can restore a specific state of an MS SQL database at any specific point in time, down to the second. In the case of a MS SQL transaction log backup, when restoring, the database state will be restored from various selected backups.

Features at a Glanceautomatic backup and recovery of MS SQL with Bacula Enterprise

Bacula Systems has created a MS SQL Server backup plugin for use with Bacula Enterprise Edition. MS SQL Server backup with Bacula has the following features:

  • Supports full and differential MS SQL backups
  • MS SQL incremental backup support
  • MS SQL backup to network and local drive
  • Scheduled MS SQL backup
  • Creating backups at the MS SQL Server database level
  • Ability to include/exclude databases from the backup creation procedure
  • Support for creating read-only database backups
  • Restoring MS SQL backups to disk
  • Sending a stream backup copy directly to the Storage Daemon
  • MS SQL point in time recovery

Review and configuration of backup MS SQL 2008, 2008 R2, 2012 and 2014

IN this document solutions are presented for Bacula Enterprise Edition 8.4 and later, which are not supported by earlier versions of the software. MS SQL database backup has been tested and is supported by MS SQL 2003 R2, MS SQL 2008 R2, MS SQL 2012, MS SQL 2005, MS SQL 2008, MS SQL 2014. MS SQL backup from Bacula can work with SQL Express.

MS SQL Backup Glossary 2008, 2008 R2, 2012 and 2014

  • MS SQL stands for Microsoft SQL Server.
  • Transaction log. Any MS SQL Server database has a transaction log, which records all transactions and database modifications performed during such transactions. The transaction log is an important element of the database. In the event of a system failure, the transaction log may be required to restore the database to a working state. More detailed information you will find at https://msdn.microsoft.com/en-us/library/ms190925.aspx.
  • Differential backup of MS SQL Server database. Differential backup is based on the last full one. During a differential backup, only the data that has changed since the last full backup was created is captured. More information can be found at https://msdn.microsoft.com/en-us/library/ms175526.aspx.
  • Full backup of MS SQL Server database. During a full database backup, a backup copy of the entire database is created. The backup includes part of the transaction log for the purpose of restoring the complete database from the backup. Full database backups contain the database at the time the backup was completed. More information can be found at https://msdn.microsoft.com/en-us/library/ms186289.aspx.
  • Backup “copy only” (CopyOnly). Copy-only backups are MS SQL backups that are independent of the normal flow of traditional SQL Server backups. Sometimes it is useful to create backups for special needs without affecting general process database backup and recovery. More information can be found at https://msdn.microsoft.com/en-us/library/ms191495.aspx.
  • VDI(Virtual Device Interface) is Microsoft technology, allowing you to create named pipe between programs.
  • standard masks specify sets of strings with wildcards. For example, the standard production* mask will include the lines production1 and production2.
  • line
  • integer.
  • LSN Each entry in the MS SQL Server transaction log is identified by a unique transaction serial number (LSN). More detailed information can be found at https://technet.microsoft.com/en-us/library/ms190411%28v=sql.105%29.aspx.

Backup MS SQL Server 2008, 2008 R2, 2012 and 2014

Full backup of MS SQL Server 2008, 2008 R2, 2012 and 2014 databases

During a full backup of an MS SQL database, the database files and transaction log are saved, which allows you to fully protect the MS SQL database in case of media failure. If one or more files are damaged, restoring the MS SQL database from a backup will allow you to restore all completed transactions. All transactions that were in progress will also be rolled back. IN this mode backups of the master and mbdb databases are created.

Differential backup of MS SQL Server 2008, 2008 R2, 2012 and 2014 databases

Differential backup of MS SQL Server database is based on the latest full backup of MS SQL database. When creating a differential MS SQL backup, only the data that has been changed since the last full MS SQL backup was created is captured. For the MS SQL differential backup function, the sequence of backups is extremely important. If for some reason the full backup referenced by MS SQL is not available, differential backups of the MS SQL Server database cannot be used. Bacula's MS SQL Backup uses specific techniques to solve this problem. Therefore, if difficulties arise, the status of a differential database backup can be automatically upgraded to a full backup.

Transaction log backup of MS SQL 2008, 2008 R2, 2012 and 2014

Setting up MS SQL backup and database configuration

Restoring a MS SQL database from a backup

You can use everything standard methods launching the procedure for restoring a MS SQL database from a backup. However, you must ensure that in case of restoring differential data, the full previous backup of the MS SQL database will also be restored. In this case, recovery occurs automatically if you run it in the console bconsole using recovery options 5 or 12. In the generated file structure, you need to mark the recovery of full databases or DB instances.

Options for restoring a MS SQL database from a backup

Bacula Enterprise Edition software allows users to use multiple MS SQL recovery options and apply the most various ways"rollback" of the database. The most commonly used recovery options are described below:

  • Where parameter: In the case of Bacula Enterprise Edition, this parameter allows the administrator to restore the database to a specific location.
  • Replace parameter: Used to define how Bacula should behave with the current database when restored. Bacula's MS SQL backup also allows you to use several more options when restoring, such as:
  • Instance: Since MS SQL uses multiple instances, MS SQL database backup from Bacula allows you to choose which instance to restore. This parameter is optional, and if it is not specified, the value specified when creating the backup will be used when restoring. By default, an instance named “MSSQLSERVER” is used.
  • Database. This option specifies the name of the database to restore and it uses the value specified at the time the database was created. This parameter is optional. By default, SQL Server database backups use the Where parameter to determine the name of the new database. If both the Where and Database parameters are assigned a valid database name, then the Database parameter will be used.
  • User. The username used to connect to the MS SQL database instance. This parameter is optional, and if it is not specified, the value specified when creating the backup will be used when restoring.
  • Password. Password used to connect to the MS SQL database instance. This parameter is optional, and if it is not specified, the value specified when creating the backup will be used when restoring.
  • Domain. The domain used to connect to the MS SQL database instance. This parameter is optional, and if it is not specified, the value specified when creating the backup will be used when restoring.
  • Recovery. The parameter allows you to determine whether the database will be rolled back to its previous state during recovery or not. By default, when restoring a database, it will roll back to the previous state.
  • Stop_before_mark. Condition WITH STOPBEFOREMARK = Used to indicate that the transaction log entry immediately preceding the flag is the restore point. The recovery point can be a date and time, an LSN, or a mark_name flag.
  • Stop_at_mark. Condition WITH STOPATMARK = Used to indicate that the marked transaction is a recovery point. STOPATMARK moves forward to the flag and replays the marked transaction. The recovery point can be a date and time, an LSN, or a mark_name flag.
  • Stop_at= . Condition WITH STOPAT = is used to indicate that the restore point is date/time.
  • Restrict_user. The WITH RESTRICT_USER clause is used to restrict access to the restored database. The default is no.

Restoring MS SQL to a point in time can be performed directly from the MS SQL backup plugin. You can also restore files locally and perform operations from the Microsoft SQL Server Management Console to gain more functionality.

LSN

The LSN number of the log entry at which a specific backup and recovery event occurred can be viewed in one of the following ways:

  • When displaying a description of tasks for creating a backup using Bacula software
  • In the log file name
  • In the msdb.backupset table
  • In the msdb.backupfile table

When performing a task to create a backup of an MS SQL database, the following information about LSN numbers will be displayed when displaying the task description:

Number First LSN corresponds to the last LSN number of the last transaction log backup. Such a backup can be the very first full backup or the last backup (incremental).

Number Last LSN matches the last transaction recorded in the journal.

In the case of a transaction log backup (incremental), the name of the file associated with this database in the task for creating an incremental backup will look like this:

The number in the name, in our case 42000162001, corresponds to the last LSN number of the previous task (to create a full or incremental backup).

Figure 2: First LSN, Last LSN, and LSNs in File Names

As shown in the example in Figure 2, if the administrator needs to restore the MS SQL database to a state corresponding to LSN number 14, the following steps can be performed:

  • In the database recovery menu, use option 5
  • Select last file full backup “data.bak” (LSN: 10)
  • Select incremental backup “log-10.trn”

Or, if the latest full backup of MS SQL Server is not available, but the previous full backup is available, then:

  • Use restore option 3, select appropriate jobids values
  • Select the database directory “/@mssql/db29187”
  • Select the full backup file “data.bak” (LSN: 2)
  • Select incremental backups “log-2.trn”, “log-3.trn”, “log-10.trn”
  • Set the stop_at_mark parameter to “lsn:14”
  • Run the task to restore the backup

MS SQL recovery scripts

Description Where Database Example
Recover files to disk Path where=c:/tmp
Restore original database where=/
Restore with new name Name where=newdb
Restore with new name Name database=newdb
Recover with new name and move files Name

Table 1: MS SQL recovery scenarios

2.3.1 Restoring a MS SQL database with the original name

To restore the database with the original name, the option Where must not be specified (empty value), or the value “/” must be specified, and the parameter Replace must be assigned a value Always, or you must first delete the source database.

Restoring a MS SQL backup with a new name

To restore a MS SQL database backup with a new name, you may first need to move the database files to disk. It all depends on whether the original database still exists.

If the source database is no longer available, then the parameter where, or the “Plugin Options” field may contain the name of the new database. MS SQL Backup from Bacula will automatically create the database with a new name.

If the original database is still needed, the where parameter will be used to move the files to disk, and you will need to name the new database using the Plugin Options menu. In the recovery tree, you must select the layout.dat file.

Using My Catalog

Run the MS SQL recovery task:

Using the My Catalog, run the MS SQL database recovery task:

Recover MS SQL to local disk

If you specify where=c:/path/, the files will be restored to the local disk, and the MS SQL database administrator will be able to use the TSQL procedural extension for the Microsoft SQL Server Management Console to restore the database. The SQL commands required to restore the database are listed in the description Job output as shown in the picture below.

Let's consider an undesirable situation. Namely: for some reason the database failed. What do we have? A full copy, a differential copy for yesterday, but there is also data for today, was it really necessary to make a differential copy every hour? - No! Eat Transaction log.
Transaction log - A log that records all transactions and all database changes made by each transaction. Those. any action with the database is recorded step by step in the log. Each record is marked by the DBMS to determine whether the transaction is complete, whether it is completed or not. With its help, you can restore the state of the database not only after a failure, but also in case of unexpected actions with data. Roll back to a certain time. As with the database, the transaction log needs to be backed up, full, differential, incremental. To restore part of the transaction log after a failure in the interval between creating backups, you need to back up the final fragment of the log, which, in fact, is the finalization point of the backup. Executed after a failure, as a countdown point.
So, to restore a database after a failure, we need a current full copy of the database, a differential copy of the database, and a copy of the transaction log.

There are 3 recovery models for the database itself - simple, full and bulk-logged. Consider:

  1. Simple model - only full redundancy is used. No diff. backups, as well as transaction log backups. Complete copies should be created as often as possible. Relevant for databases used “read-only”.
  2. The Full recovery model is the most used model, in which all data backup and recovery functions are available. Supports recovery individual pages data. Transactions are fully logged and the transaction log is saved.
  3. The Bulk-Logged model is intended as a complement to the full recovery model. Most mass operations do not support logging, and accordingly, it does not support database recovery to a certain point in time.

Let's consider the most current backup chain: Full backup - once a week, Differential backup - once a day, Transaction log backup - once an hour.
There are several options for creating backups:

  • Using the built-in MS SQL task scheduler
  • Using Transact-SQL language
  • Using sqlcmd and OS Task Scheduler
  • Manually (which doesn’t suit us, because a working admin has to constantly mess around)

Let's consider the first option as the most usable. For this purpose it is used Windows Server 2008 R2 Enterprise and MS SQL Server 2008 Eng.

So, let's say we have a TECH database:

Let's move on to the Job creation tool:

Press the right mouse button and call Master Joba:
Select the checkbox “Execute each task separately”, we perform only one action

The master is without a turban, but the size of the turban is not the main thing)) We choose the type of desire, in our case - full reservation:

Master Joba, as it turns out, is a little bit Jewish, so he asks again:

“It’s worth choosing additional parameters, oh young paddawan!”:
Here we select the database, backup storage period, address (tape or disk), save path and most importantly - the task scheduler!

“You shouldn’t forget about the database when choosing yours. Concentrate your strength and choose a database”:

“You’re in a hurry to create the task too quickly, click on the button at the bottom with the name Shedule - Define.”
Actually, the task scheduler, where we select the type (repeat, once, etc.), day, time, start type:

That's it, we created it. Master Joba is cool and green. We look at the state in Maintance Plans:

For the paranoid, don’t be afraid to admit it to the mirror, it’s worth looking into the soul of SQL Server Agent - Job Activity Monitor, Job Wizard will show you everything in detail:

Now, if the specified conditions are met, a full backup of the database should be created. Using the same principle, a differential backup and a transaction log backup are created (these sub-items are located below “Full backup” in the task selection list).
Twist your MSSQL ears as you please, don’t unscrew them

In the next article - creation using Transact-SQL and a couple of examples.

Let's restore the “Test _Recovery” database to the “ t 4».

Let's start restoring the database from a full backup "Full2_Test_Recovery.bak" using "SQL Server Management Studio" " Click right click mouse based on " Test_Recovery ", in the menu that appears, select " Tasks", then "Restore", then "Database".

In the window that appears " Restore Database" in the "Sourse" section, select "Device". Next « Add ", enter the path "\\ vniz - tst - bkp 01. test . local\Backup_SQL\Full 2_Test_Recovery. bak", click "Ok". In the “Destination” section, select Database "Test Recovery"

Click “Ok”

The base will be restored successfully.

Let's look at restoring a database using Transact-SQL.

Right-click on the “Test_Recovery” database and select “New Query” from the menu that appears:

In the window that appears, enter:

USE master

RESTORE DATABASE Test_Recovery

FROM DISK = "\\vniz-tst-bkp01.test.local\Backup_SQL\Full2_Test_Recovery.bak"

WITH REPLACE

The base will be restored successfully.

In this example we used the "REPLACE" parameter:

Recovery typically prevents a database from being accidentally overwritten by another database. If the database specified in the RESTORE statement already exists on the current server, and the family GUID for the specified database is different from the family GUID for the database recorded in the backup set, then the database will not be restored.

The REPLACE option overrides several important checks typically performed by a restore operation. The following checks are cancelled.

  • Checking to restore over an existing database a backup created for another database.When you use the REPLACE option, a restore can write data over an existing database, regardless of which databases are contained in the backup set, even if the specified data name is different from what was written in the backup set. This may result in the database being accidentally overwritten by another database.
  • Testing to recover a database that uses the full recovery model or the bulk-logged recovery model for which a tail-log backup was not taken and the STOPAT option was not applied.When you use the REPLACE option, you may lose committed data because the most recently logged data has not yet been copied to the backup.
  • Overwrite existing files.

Database administrators are divided into those who make backups and those who will make backups.

Introduction

This article describes the most common 1C IB backup using MS SQL Server 2008 R2 tools, explains why you should do it this way and not otherwise, and dispels several myths. The article contains quite a lot of references to MS SQL documentation; this article is more of an overview of backup mechanisms than a comprehensive guide. But for those who are faced with this task for the first time, simple and step by step instructions, which apply to simple situations. The article is not intended for administration gurus, gurus already know all this, but it is assumed that the reader is able to install MS SQL Server himself and force this miracle of hostile technology to create a database in its depths, which in turn he is able to force to store 1C data.

I consider the TSQL BACKUP DATABASE command (and its brother BACKUP LOG) to be essentially the only means of backing up 1C databases using MS SQL Server as a DBMS. Why? Let's look at what methods we generally have:

How Fine Badly Total
Upload to dt Very compact format. It takes a long time to form, requires exclusive access, does not save some unimportant data (such as user settings in earlier versions), and takes a long time to deploy. It's not so much a backup method as it is a way to move data from one environment to another. Ideal for narrow channels.
Copying mdf and ldf files A very clear way for novice admins. Requires releasing database files from locking, and this is possible if the database is offline (take offline command context menu), is detached (detach) or the server is simply stopped. Obviously, users will not be able to work at this time. This method makes sense to use if and only if an accident has already occurred, so that when trying to recover, at least have the opportunity to return to the option from which the recovery began.
Backup using OS or hypervisor A convenient way for development and testing environments. Not always friendly with data integrity. Resource-intensive method. May have limited use for development. It has no practical meaning in a food environment.
Backup using MS SQL No downtime required. Allows you to restore a complete state at an arbitrary moment, if you worry about it in advance. Excellent automation. Economical in time and other resources. Not a very compact format. Not everyone knows how to use this method to the required extent. For food environments - the main tool.

The main difficulties when using backup using built-in MS SQL tools arise from a basic misunderstanding of the principles of operation. This is partly explained by great laziness, partly by the lack of a simple and understandable explanation at the level of “ready-made recipes” (hmm, let’s just say, I haven’t come across one), and the situation is also aggravated by the mythological advice of “underguru” on the forums. I don’t know what to do with laziness, but I’ll try to explain the basics of backup.

What and why do we save?

A long time ago, in a distant galaxy, there was such a product of engineering and accounting thought as 1C: Enterprise 7.7. Apparently due to the fact that the first versions of 1C:Enterprise were developed to use the popular format dbf files, its SQL version did not store enough information in the database to consider the MS SQL backup to be complete, and even with each change in the structure, the operating conditions of the full recovery model were violated, so it was necessary to resort to various tricks to force the backup system to perform its main function. But since version 8 came out, DBAs have finally been able to relax. Standard backup tools allow you to create a complete and holistic backup system. Only the logbook and some little things such as settings for the position of forms (in older versions) are not included in the backup, but the loss of this data does not affect the functionality of the system, although it is certainly correct and useful to make backup copies of the logbook.

Why do we need backup at all? Hm. At first glance, a strange question. Well, probably, firstly, to be able to deploy a copy of the system and secondly, to restore the system in case of failure? I agree about the first one, but the second purpose is the first backup myth.

Backup is the last line of system security. If a database administrator has to restore a product system from backup copies, it is likely that many serious mistakes were made in the organization of work. You cannot treat backup as the main way to ensure data integrity; no, it is rather closer to a fire extinguishing system. A fire extinguishing system is required. It must be configured, tested and operational. But if it worked, then this in itself is a serious emergency with a lot of negative consequences.

To ensure that backup is used only for “peaceful” purposes, use other means to ensure performance:

  • Ensure the physical safety of your servers: fires, floods, poor power supplies, cleaners, construction workers, meteorites and wild animals are all just waiting around the corner to destroy your server room.
  • Address information security threats responsibly.
  • Make changes to the system skillfully and make sure in advance that these changes will not lead to deterioration. In addition to a plan for making changes, it is advisable to have a plan for “what to do if everything goes wrong.”
  • Actively use technologies to increase system availability and reliability instead of later dealing with the consequences of accidents. For MS SQL you should pay attention to the following features:
    • Using MS SQL clusters (although, to be honest, I think this is one of the most expensive and useless ways to occupy a database administrator for systems that do not require 24x7)
    • Database mirroring (synchronous and asynchronous depending on availability, performance and cost requirements)
    • Transaction log delivery
    • Replication using 1C tools (distributed databases)

Depending on the system availability requirements and the budget allocated for these purposes, it is quite possible to choose solutions that will reduce downtime and failure recovery time by 1-2 orders of magnitude. There is no need to be afraid of accessibility technologies: they are simple enough to learn in a few days with basic knowledge of MS SQL.

But, no matter what, backup is still necessary. This is the same reserve parachute that you can use when all other means of rescue fail. But, like a real reserve parachute, for this:

  • this system must be correctly and professionally configured in advance,
  • a specialist using the system must have theoretical and practical skills in its use (regularly reinforced),
  • the system should consist of the most reliable and simple components (this is our last hope).

Basic information about storing and processing MS SQL data

Data in MS SQL is usually stored in data files (hereinafter referred to as FD - an abbreviation that is not commonly used; in this article there will be several more not very common abbreviations) with the mdf or ndf extensions. In addition to these files, there are also transaction logs (TL), which are stored in files with the .ldf extension. Often, novice administrators are irresponsible and frivolous when it comes to VT, both in terms of performance and storage reliability. This is a very serious mistake. In fact, it’s rather the opposite, if there is a reliably functioning backup system and a lot of time can be allocated to restore the system, then you can store data on a fast, but extremely unreliable RAID-0, but then the data must be stored on a separate reliable and productive resource (although would be on RAID-1). Why is that? Let's take a closer look. Let me make a reservation right away that the presentation is somewhat simplified, but sufficient for an initial understanding.

The FD stores data in pages of 8 kilobytes (which are combined into extents of 64 kilobytes, but this is not significant). MS SQL does not guarantee that immediately after executing a data change command, these changes will go to the FD. No, it's just that the page in memory is marked as "requiring saving." If the server has enough resources, then soon this data will be on disk. Moreover, the server works “optimistically” and if these changes occur in a transaction, then they may well end up on disk before the transaction is committed. That is, in the general case, when active work The FD contains scattered pieces of unfinished data and unfinished transactions for which it is unknown whether they will be canceled or committed. There is a special command "CHECKPOINT", which tells the server that it needs to flush all unsaved data to disk "right now", but the scope of this command is quite specific. Suffice it to say that 1C does not use it (I have not encountered it) and to understand that during operation the FD is usually not in an intact state.

To cope with this chaos, we just need VT. The following events are recorded in it:

  • Information about the start of the transaction and its identifier.
  • Information about the fact of committing or canceling a transaction.
  • Information about all changes in data in the FD (roughly speaking, what happened and what happened).
  • Information about changes to the FD itself or the database structure (increasing files, decreasing files, allocating and freeing pages, creating and deleting tables and indexes)

All this information is written indicating the identifier of the transaction in which it occurred and in sufficient volume to understand how to move from the state before this operation to the state after this operation and vice versa (the exception is the partial-logging recovery model).

It is important that this information is written to disk immediately. Until the information is recorded in the VT, the command is not considered executed. In a normal situation, when the size of the VT is sufficient and when it is not very fragmented, records are written to it sequentially in small records (not necessarily multiples of 8 kb). Only data that is actually necessary for recovery is included in the transaction log. In particular Not information is obtained about which request text led to the modifications, what execution plan this request had, which user launched it, and other information unnecessary for recovery. The query can provide some insight into the transaction log data structure

Select * from::fn_dblog(null,null)

Because of hard disks They work much more efficiently with sequential writing than with a chaotic stream of read and write commands, and due to the fact that SQL commands will wait until the end of writing to the VT, the following recommendation arises:

If there is even the slightest possibility, then in a product environment, VTs should be located on separate (from everything else) physical media, preferably with minimal access time for sequential recording and with maximum reliability. For simple systems RAID-1 is quite suitable.

If the transaction is canceled, then the server will return all changes already made to the previous state. That is why

Canceling a transaction in MS SQL Server usually lasts comparable to the total duration of operations for changing the data of the transaction itself. Try not to cancel transactions or make the decision to cancel as early as possible.

If the server unexpectedly stops working for some reason, then when it is restarted, it will be analyzed which data in the FD does not correspond to the integral state (unrecorded but committed transactions and recorded but canceled transactions) and this data will be corrected. Therefore, if you, for example, started rebuilding the indexes of a large table and restarted the server, then when you restart it, it will take a significant amount of time to roll back this transaction, and there is no way to interrupt this process.

What happens when the VT reaches the end of the file? It's simple - if there is free space at the beginning, then he will start writing in free place at the beginning of the file to the occupied space. Like a looped magnetic tape. If there is no space at the beginning, then the server will usually try to expand the transaction log file, while for the server the new piece allocated is a new virtual transaction log file, of which there can be a lot in the physical transaction file, but this has little to do with backup. If the server fails to expand the file (the disk space has run out or the settings prohibit expanding the file), then the current transaction will be canceled with error 9002.

Oops. What needs to be done to ensure that there is always a place in the railway? This is where we come to the backup system and recovery models. To cancel transactions and to restore the correct state of the server in the event of a sudden shutdown, it is necessary to store records in the JT, starting from the start of the earliest open transaction. This minimum is written and stored in ZhT Necessarily. Regardless of the weather, server settings and the wishes of the admin. The server cannot allow this information not to exist. Therefore, if you open a transaction in one session and perform different actions in others, the transaction log may end unexpectedly. The oldest transaction can be identified with the DBCC OPENTRAN command. But this is only the necessary minimum of information. What happens next depends on recovery models. There are three of them in SQL Server:

  • Simple— only the remainder of the VT necessary for life is stored.
  • Full— the entire VT is stored since the last backup transaction log. Please note, not from the moment of a full backup!
  • Bulk logged— part (usually a very small part) of operations are recorded in a very compact format (essentially just a record that such and such a page of the data file has been changed). Otherwise identical to Full.

There are several myths associated with recovery models.

  • Simple allows you to reduce the load on the disk subsystem. This is wrong. exactly the same amount is written as with Bulk logged, only it is considered free much earlier.
  • Bulk logged allows you to reduce the load on the disk subsystem. For 1C this is almost not the case. In fact, one of the few operations that can fall under minimal logging without additional dances with a tambourine is loading data from an upload in dt format and restructuring tables.
  • When using the Bulk logged model, some transactions are not included in the transaction log backup and it does not allow you to restore the state at the time of this backup. This is not entirely true. If the operation is minimally logged, then the current pages with data will be included in the backup copy and it will be possible to “play” the transaction log to the end (although this is not possible at an arbitrary point in time if there are minimally logged operations).

It is almost pointless to use the Bulk logged model for 1C databases, so we do not consider it further. But we will consider the choice between Full and Simple in more detail in the next part.

  • Transaction log structure
    • Recovery Models and Transaction Log Management
    • Transaction log management
  • Using Transaction Log Backups

How backup works in Simple and Full recovery models

Based on the type of formation, backup copies are of three types:

  • Full(Full)
  • Differential(Differential, difference)
  • Log(Backup copy of transaction logs, given how often this term is used, we will abbreviate it to RKZhT)

Don't get confused here: a full recovery model and a full backup are significantly different things. In order not to confuse them, below I will use English terms for the recovery model and Russian terms for types of backups.

Full and differential copy work the same for Simple and Full. Transaction log backup is completely missing from Simple.

Full backup

Allows you to restore the state of the database to a certain point in time (to the one at which the backup was started). Consists of a page-by-page copy of the used part of the data files and an active piece of the transaction log during the time the backup was being formed.

Differential backup

Stores pages of data that have changed since the last full backup. When restoring, you must first restore a full backup copy (in NORECOVERY mode, examples will be given below), then you can apply any of the subsequent differential copies to the resulting “blank”, but, of course, only from those made before the next full backup. Due to this, you can significantly reduce the volume disk space to store a backup copy.

Important points:

  • Without a previous full backup, a differential copy is useless. Therefore, it is advisable to store them somewhere close to each other.
  • Each subsequent differential backup will retain all the pages included in the previous differential backup taken after the previous full backup (although perhaps with different content). Therefore, each subsequent differential copy is larger than the previous ones, until a full copy is made again (if this is violated, it is only due to compression algorithms)
  • For recovery at some point it is enough last full backup at this point and last differential copy at this point. Intermediate copies are not needed for recovery (although they may be needed to select the moment of recovery)

RKZhT

Contains a copy of the VT for a certain period. Usually from the moment of the last RKZhT until the formation of the current RKZhT. RKZHT allows you to restore the state from a copy restored in NORECOVERY mode at any point in time included in the period of the restored copy of the ZHT at any subsequent point in time included in the interval of the restored backup copy. When creating a backup with standard parameters, space in the transaction log file is freed up (until the time of the last open transaction).

Obviously, the RKZhT does not make sense in the Simple model (then the ZhT contains only information from the moment of the last unclosed transaction).

When using RKZhT, an important concept arises - continuous chain of RKZhT. This chain can be interrupted either by the loss of some backup copies of this chain, or by converting the database to Simple and back.

Attention: a set of RKZhT is essentially useless if it is not a continuous chain, and the start point of the last successful full or differential backup must be inside period of this chain.

Common misconceptions and myths:

  • "RKZhT contains transaction log data from the moment of the previous full or differential backup." No, that's not true. The RKZhT also contains, at first glance, useless data between the previous RKZhT and the subsequent full backup.
  • "A full or differential backup should free up space inside the transaction log." No, that's not true. Full and differential backups do not affect the RKZhT chain.
  • VT needs to be periodically cleaned manually, reduced, and shredded. No, it’s not necessary and, on the contrary, it’s undesirable. If you release the VT between the RCVT, the RCVT chain needed for restoration will be broken. And constant reduction/expansion of the file will lead to its physical and logical fragmentation.

How it works in simple

Let there be a database of 1000 GB. Every day the database grows by 2 GB, while 10 GB of old data is changed. The following backups have been made

  • Full copy of F1 from 0:00 February 1 (volume 1000 GB, compression is not taken into account for simplicity of the picture)
    • Differential copy of D1.1 from 0:00 February 2 (volume 12 GB)
    • Differential copy of D1.2 from 0:00 February 3 (volume 19 GB)
    • Differential copy of D1.3 from 0:00 February 4 (volume 25 GB)
    • Differential copy of D1.4 from 0:00 February 5 (volume 31 GB)
    • Difference copy D1.5 from 0:00 February 6 (volume 36 GB)
    • Differential copy of D1.6 from 0:00 February 7 (volume 40 GB)
  • Full copy of F2 from 0:00 February 8 (volume 1014 GB)
    • Differential copy of D2.1 from 0:00 February 9 (volume 12 GB)
    • Differential copy of D2.2 from 0:00 February 10 (volume 19 GB)
    • Differential copy of D2.3 from 0:00 February 11 (volume 25 GB)
    • Differential copy of D2.4 from 0:00 February 12 (volume 31 GB)
    • Difference copy D2.5 from 0:00 February 13 (volume 36 GB)
    • Differential copy of D2.6 from 0:00 February 14 (volume 40 GB)

Using this set, we can restore data at 0:00 on any day from February 1 to February 14. To do this, we need to take a full copy of F1 for the week of February 1-7 or a full copy of F2 for February 8-14, restore it in NORECOVERY mode and then apply a differential copy of the desired day.

How it works in full

Let us have the same set of backup full and differential backups as in the previous example. In addition to this, there are the following RKZhT:

  • RKZhT 1 for the period from 12:00 January 31 to 12:00 February 2 (about 30 GB)
  • RKZhT 2 for the period from 12:00 February 2 to 12:00 February 4 (about 30 GB)
  • RKZhT 3 for the period from 12:00 February 4 to 12:00 February 6 (about 30 GB)
  • RKZhT 4 for the period from 12:00 February 6 to 12:00 February 7 (about 30 GB)
  • RKZhT 5 for the period from 12:00 February 8 to 12:00 February 10 (about 30 GB)
  • RKZhT 6 for the period from 12:00 February 10 to 12:00 February 12 (about 30 GB)
  • RKZhT 7 for the period from 12:00 February 12 to 12:00 February 14 (about 30 GB)
  • RKZhT 8 for the period from 12:00 February 14 to 12:00 February 16 (about 30 GB)

Note:

  1. The size of the RKZhT will be approximately constant.
  2. We can make backups less often than differential or full ones, or we can do them more often, then they will be smaller in size.
  3. We can now restore the system state to any point from 0:00 on February 1st, when we have the earliest complete copy, until 12:00 on February 16th.

In the very simple case To restore we need:

  1. Last full copy before recovery
  2. Last differential copy before recovery
  3. All RKZhT, from the moment of the last difference copy until the moment of restoration
  • Full copy of F2 from 0:00 February 8
  • Difference copy D2.2 from 0:00 February 10
  • RKZhT 6 for the period from 12:00 January 10 to 12:00 February 12

First, F2 will be restored, then D2.2, then RKZhT 6 until 13:13:13 on February 10. But a significant advantage of the Full model is that we have a choice - to use the last full or differential copy or NOT the last one. For example, if it were discovered that the copy of D2.2 was corrupted, and we needed to restore it to the moment before 13:13:13 on February 10, then for the Simple model this would mean that we could only restore the data to the moment D2.1. With Full - "DON"T PANIC", we have the following options:

  1. Restore F2, then then D2.1, then RKZHT 5, then then RKZHT 6 until 13:13:13 on February 10.
  2. Restore F2, then RKZHT 4, then RKZHT 5, then then RKZHT 6 until 13:13:13 on February 10.
  3. Or even restore F1 and run all RKZhT up to RKZhT 6 until 13:13:13 on February 10th.

As you can see, the full model gives us more choice.

Now let’s imagine that we are very cunning. And a couple of days before the failure (13:13:13 February 10) we know that there will be a failure. We are restoring the database on a neighboring server from a full backup, leaving the opportunity to roll up subsequent states with differential copies or RKZhT, i.e. we left it in NORECOVERY mode. And every time, immediately after the formation of the RKZhT, we apply it to this reserve base, leaving it in NORECOVERY mode. Wow! Why, it will now take us only 10-15 minutes to restore the database, instead of restoring a huge database! Congratulations, we have reinvented log shipping, one of the ways to reduce downtime. If you transfer data this way not once per period, but constantly, then you will get mirroring, and if the source base waits for the mirror base to be updated, then this is synchronous mirroring, if it does not wait, then it is asynchronous.

You can read more about high availability tools in the help:

  • High Availability (Database Engine)
    • Understanding High Availability Solutions
    • High level of accessibility. Interaction and collaboration

Other Backup Considerations

You can safely skip this section if you are bored with the theory and are itching to try out the backup settings.

File groups

1C:Enterprise essentially does not know how to work with file groups. There is a single file group and that’s it. In fact, a programmer or MS SQL database administrator is able to put some tables, indexes, or even pieces of tables and indexes into separate file groups (in the simplest version - into separate files). This is necessary either to speed up access to some data (by placing it on very fast media), or vice versa, by sacrificing speed and placing it on cheaper media (for example, little-used but voluminous data). When working with file groups, it is possible to make backup copies of them separately, and you can also restore them separately, but you need to take into account that all file groups will have to be “caught up” to one point by rolling the RKZhT.

Data files

If a person manages the placement of data in different file groups, then when there are several files inside the file group, then MS SQL Server pushes the data into them independently (if the volume of files is equal, it will try evenly). From an application point of view, this is used to parallelize I/O operations. But from the point of view of backups, there is another point. For very large databases in the pre-SQL 2008 era, it was a typical problem to allocate a contiguous window for a full backup, and the destination disk for this backup might simply not accommodate it. The most in a simple way in this case, it was to backup each file (or filegroup) into its own window. Now, with the active spread of backup compression, this problem has become less, but this technique can still be kept in mind.

Backup compression

MS SQL Server 2008 introduced a super-mega-ultra feature. From now on and forever, backups can be compressed when generated on the fly. This reduces the size of a 1C database backup by 5-10 times. And considering that the performance of the disk subsystem is usually the bottleneck of the DBMS, this not only reduces the cost of storage, but also greatly accelerates backup (although the load on the processors increases, but usually the processor power is quite sufficient on the DBMS server).

If in the 2008 version this feature was only available for the Enterprise edition (which is very expensive), then in 2008 R2 this feature was given to the Standard version, which is very pleasing.

Compression settings are not covered in the examples below, but I strongly recommend using backup compression unless there is a specific reason to disable it.

One backup file - many internals

In fact, a backup is not just a file, it is a rather complex container in which many backups can be stored. This approach has a very ancient history (I personally have been observing it since version 6.5), but at the moment for administrators of “regular” databases, especially 1C databases, there are no serious reasons not to use the “one backup copy - one file” approach . For general development It’s useful to explore the possibility of putting several backup copies into one file, but most likely you won’t have to use it (or if you do, it will be sorting out the rubble of a would-be administrator who unqualifiedly used this feature).

Multiple mirror copies

SQL Server has another great feature. You can create a backup copy in parallel in several receivers. How simplest example, you can dump one copy onto a local disk and at the same time put it on network resource. A local copy is convenient, since recovery from it is much faster; a remote copy will withstand physical destruction of the main database server much better.

Examples of backup systems

Enough theory. It's time to prove with practice that this whole kitchen works.

Setting up a typical server reservation through Maintenance Plans

This section is structured in the form of ready-made recipes with explanations. This section is very boring and long due to the pictures, so you can skip it.

Using the service plan creation wizard

Setting up server backup using TSQL scripts, examples of some features

The question immediately arises, what else is needed? It seems like you just set everything up and everything works like clockwork? Why bother with all sorts of scripts? Service plans do not allow:

  • Use mirror backup
  • Use compression settings different from the server settings
  • Does not allow flexible response to emerging situations (no error handling capabilities)
  • Does not allow flexible use of security settings
  • Service plans are very inconvenient to deploy (and maintain the same) on large quantities servers (even, perhaps, already 3-4)

Below are typical backup commands

Full backup

Full backup with overwriting the existing file (if any) and checking page checksums before writing. When creating a backup, every percentage of progress is counted

BACKUP DATABASE TO DISK = N"C:\Backup\mydb.bak" WITH INIT, FORMAT, STATS = 1, CHECKSUM

Differential backup

Similarly - difference copy

BACKUP DATABASE TO DISK = N"C:\Backup\mydb.diff" WITH DIFFERENTIAL, INIT, FORMAT, STATS = 1, CHECKSUM

RKZhT

Transaction log backup

BACKUP LOG TO DISK = N"C:\Backup\mydb.trn" WITH INIT, FORMAT

Mirror backup

It is often convenient to make not one backup copy at once, but two. For example, one can be stored locally on the server (so that it is at hand), and the second is immediately formed into a physically remote and protected from adverse influences storage:

BACKUP DATABASE TO DISK = N"C:\Backup\mydb.bak", MIRROR TO DISK = N"\\safe-server\backup\mydb.bak" WITH INIT, FORMAT

An important point that is often missed: the user on whose behalf the MSSQL Server process is launched must have access to the resource "\\safe-server\backup\", otherwise the copying will fail. If MSSQL Server is launched on behalf of the system, then access should be given to the domain user "server_name$", but it is still better to correctly configure MS SQL to run on behalf of a specially created user.

If you do not specify MIRROR TO , then it will not be 2 mirror copies, but one copy, divided into 2 files, according to the interleave principle. And each of them separately will be useless.

sqlcmd -S DECLSERVER\SQLGTD -E -Q "declare @s varchar(255) set @s='E:\backup\GTD_' + convert(varchar(1), datepart(dw, getdate())) + '. bak' backup database GTD to disk = @s with init, noformat, skip, nounload"

sqlcmd allows you to enter Transact-SQL statements, system procedures, and script files from command line to the query editor in SQLCMD mode,

  • -S - specifies the server name, server[\instance_name];
  • DECLSERVER\SQLGTD - server name/instance name on which the database runs;
  • -E - uses a trusted connection to connect to the SQL server instead of a username and password;
  • -Q "cmdlinequery" - when starting the program sqlcmd executes the request, but does not exit the program upon completion of its execution. Multiple queries can be executed, separated by semicolons. Enclose the query in quotes as shown above;
  • declare - declare the variable s, the variable name always starts with @, so @s. In our case @s- this is the folder (disk) for storing backups;
  • varchar(n) - sets the variable type @s as string with long line n, in the example 255 characters;
  • set - sets the value of a variable @s, in the example this is the backup folder on drive E ( E:\backup\), then the name of the backup file is specified, where the set of functions convert(varchar(1), datepart(dw, getdate())) returns to text format with a length of 1 character the current day of the week (Monday - 1 , Tuesday - 2 , etc.) and the extension is added bak. The output will be a file with the name GTD_Number of the Day of the Week.bak;
  • backup - creates a backup;
  • database - indicates the creation of a backup of the entire database;
  • GTD - in our example, the name of the database on the SQL server;
  • to disk - indicates the type of backup storage device, file hard drive, and the variable is specified @s, which is assigned the path and name of the file being created;
  • with init, noformat, skip, nounload - indicates that it is necessary to rewrite the data in a circle with redefining the headers, which will allow us to have 7 backup files for each day of the week, rewritten in a circle.

You can use other functions, such as compression, as needed, see Transact-SQL Query and Function Help.

Step 2. Change the text file extension to .cmd

As a result, we get the file backupGTD.cmd. You must run the created batch file from the machine where the MS SQL database is installed.

Step 3. Automate this process

Let's consider this step using MS Windows Server 2008 as an example: Server Manager -> Configuration -> Job Scheduler -> Job Scheduler Library.