sql backup. SQL

It is recommended to customize regular database backup(in case of hardware or software failures), and best of all with backups for the last few days, for example seven (for the last week).

To do this, you can use either the built-in SQL Server task scheduler - "SQL Server Agent" (in free version not included), or the standard "Windows Scheduler" in combination with the SQLCMD.EXE utility, which allows you to run queries against SQL Server from command line. You must create at least seven jobs in the scheduler (one for each day of the week), each of which will (once a week) replace one of the seven files containing the corresponding database backup.

In addition, it is recommended to store backup files not only on the hard drive of the computer where SQL Server is installed, but also to duplicate them on tape or HDD another computer on the network. To do this, you can use either special software that allows you to do backups the entire disk, or using the same scheduler to copy files to a tape or another computer (second step).

Using the "Windows Scheduler" (for the free version)

To create a task in Windows Scheduler" necessary:

Run the Notepad program (Start->All Programs->Accessories->Notepad) and enter the following two lines, then save them as a batch file (*.BAT):

SQLCMD -S (local) -E -Q "BACKUP DATABASE AltaSVHDb TO DISK = "D:\BACKUP\ AltaSVHDb_monday.bak" WITH INIT, NOFORMAT, SKIP, NOUNLOAD"
XCOPY D:\BACKUP\ AltaSVHDb_monday.bak \\BACKUP_SERVER\Folder\*.* /Y

where "(local)"- server name (if you are installing a named instance of SQL Server, you must specify the name in full: “COMP_NAME\SQLEXPRESS”), AltaSVHDb- database name, "D:\BACKUP\ AltaSVHDb_monday.bak"- the name of the file to create a backup copy in it (will vary by day of the week), "BACKUP_SERVER"- the name of the computer to which additional copying will be performed, Folder- a folder on this computer (it must be shared).

Launch the task scheduling wizard (Control Panel->Scheduled Tasks->Add Task) and click the "Next" button:

Click the "Browse" button and specify the path to batch file(*.BAT) created in step a):

Specify a name for the task, select the "weekly" run option and click the "Next" button:

Check the box next to the desired day of the week, and in the "Start time" field, specify the time when the process should start Reserve copy(usually this is done at night), then click the "Next" button:

Enter the username and password (twice) of the OS account under which the task will be executed, and click the "Next" button:

Attention! For the task to run successfully, you must provide the account specified here (domain or local computer) write permissions to the above folder "\\BACKUP_SERVER\Folder", as well as configure access to SQL Server itself.

Press the "Finish" button

Note. To check the operability of the created task, you need to right-click on the task of interest in the list of tasks (Control Panel->Scheduled Tasks) and select the “Run” item in the context menu, then make sure that the database backup file was successfully created using the paths that were specified in step a).

Using "SQL Server Agent" (not included in the free version)

To create a task in SQL Server Agent, you need to:

Run the SQL Server Management Studio utility and connect to the server with an administrator account.

In the left part of the window, right-click on the "Server objects / Backup devices" section and select the "Create backup device" item in the context menu:

In the "Device name" field, enter a name that will be associated with the database backup file, change the path in the "File" field if necessary, and click "OK":

In the left part of the window, right-click on the "SQL Server Agent/Jobs" section and select the "Create Job" item in the context menu:

In the "Name" field, enter the name of the task:

On the Steps page, click the Create button:

In the window that appears, enter a name in the "Step Name" field, check that "Transact-SQL (T-SQL) Script" is selected in the "Type" field, and enter the line in the "Command" field:

BACKUP DATABASE AltaSVHDb TO AltaSVHDb_monday WITH INIT, NOFORMAT, SKIP, NOUNLOAD

where AltaSVHDb- database name, AltaSVHDb_monday- the name of the backup device created in step c) (will vary by day of the week):

In the previous window, click the "OK" button, as a result, the following line should appear on the "Steps" page:

In order for the database backup file to be immediately copied to another computer on the network, repeat steps f) - h), in the "Creating a task step" window, select "Operating system (CmdExec)" in the "Type" field, and specify in the "Command" field line:

XCOPY D:\MSSQL\BACKUP\AltaSVHDb_monday.bak \\BACKUP_SERVER\Folder\*.* /Y

where "D:\MSSQL\BACKUP\AltaSVHDb_monday.bak"- the path specified in step c) (will vary by day of the week), "BACKUP_SERVER"- the name of the computer to which the copy will be made, Folder- a folder on this computer (it must be shared):

Note. In order for the file copy to be successful, you need to run "SQL Server Agent" under a Windows domain account that has write permissions to the above folder (see also "SQL2005_installation.doc" or "SQL2008_installation.doc"), and configured access to SQL itself Server (see section "Configuring database access rights", enable this account on the Server Roles page, and do nothing on the User Mapping and Securable Objects pages).

On the "Schedules" page, click the "Create" button:

Enter a name in the Name field, make sure the Schedule Type field is set to Recurring Task and the Runs field is set to Weekly. Check the box next to the desired day of the week (uncheck the rest), and in the "Single task" field, specify the time when the backup process should start (usually this is done at night):

In the previous window, click the "OK" button, as a result, the following line should appear on the "Schedules" page:

Press the "OK" button.

Note. To check the operability of the created task, you need to right-click on the task of interest in the "SQL Server Agent / Tasks" section and select the "Run task at a step" item in the context menu, select the first step of this task in the window that appears and click "OK". A window will appear showing the progress of the task. If the execution of the task ends with an error, then detailed description errors can be seen by calling the "View log" item of the same context menu.

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

Introduction

This article describes the most common backup of IB 1C using MS SQL Server 2008 R2 tools, explains why you should do it this way and not otherwise, and dispels several myths. The article has a lot of links to the 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 that 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 his bowels, 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 backup tool for 1C databases using MS SQL Server as a DBMS. Why? Let's look at what methods we generally have:

How	Good	Badly	Total
Upload to dt	Very compact format.	It takes a long time to form, it requires exclusive access, it does not save some insignificant data (such as user settings in earlier versions), it takes a long time to deploy.	This is not so much a backup method, but a way to transfer data from one environment to another. Ideal for narrow channels.
copying mdf files and ldf	A very clear way for novice admins.	Requires the release of database files from locking, and this is possible if the database is disabled (the take offline command of the context menu), disconnected (detach), or the server is simply stopped. Obviously, users will not be able to work at this time.	It makes sense to apply this method if and only if a failure has already occurred, so that when trying to restore, at least be able to return to the option from which the restoration began.
Backup using OS or hypervisor	A convenient way for development and testing environments.	Not always friendly with data integrity. resource intensive way.	May have limited application for development. It has no practical meaning in the food environment.
Backup using MS SQL	Does not require downtime. Allows you to restore a consistent state to an arbitrary moment, if you take care of it in advance. Perfectly automated. Saves time and other resources.	Not very compact. Not everyone knows how to use this method to the extent necessary.	For production environments - the main tool.

The main difficulties when using backup using the built-in MS SQL tools arise due to an elementary misunderstanding of the principles of work. 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 say, I have not seen it), and the situation is even aggravated by the mythological advice of "under-guru" on the forums. I don’t know what to do with laziness, but I’ll try to explain the basics of backup.

What do we save and why?

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 full-fledged, and even with each change in the structure, the conditions for the full recovery model were violated, so you had to go to different tricks to force the backup system to execute its main function. But, since version 8 came out, DBAs have finally been able to relax. Regular backup tools allow you to create a complete and complete system of backups. Only the registration log and some little things such as setting the position of forms (in older versions) are not included in the backup, but this 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 registration log.

Why do we need a backup at all? Hm. At first glance, this is 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 appointment is the first backup myth.

Backup is the last line of defense for system integrity. If the database administrator has to restore the product system from backups, it means that a lot of blunders in the organization of work have been made with a high probability. You can not treat backup as the main way to ensure data integrity, no, it's more like a fire extinguishing system. A fire suppression 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.

In order for backup to be used only for "peaceful" purposes, use other means to ensure performance:

• Keep your servers physically safe: fires, floods, poor power, cleaners, construction workers, meteorites and wild animals are all just around the corner to destroy your server room.
• Handle 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 desirable to have a plan "what to do if everything goes wrong."
• Actively use technologies to increase the availability and reliability of the system, instead of then cleaning up 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 take on a DBA for systems that do not require 24x7)
  • Database mirroring (synchronous and asynchronous depending on availability, performance and cost requirements)
  • Delivery of transaction logs
  • Replication by means of 1C (distributed databases)

Depending on system availability requirements and the budget allocated for these purposes, it is quite possible to choose solutions that will reduce downtime and disaster recovery 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, in spite of everything, backup is still necessary. This is the same reserve parachute that you can use when all other means of escape fail. But, like a real reserve parachute, for this:

• this system must be correctly and competently configured in advance,
• a specialist using the system must have theoretical and practical skills in its application (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 FD is not a commonly used abbreviation, there will be a few more not very common abbreviations in this article) with mdf or ndf extensions. In addition to these files, there are also transaction logs (LT), which are stored in files with the ldf extension. It is not uncommon for novice administrators to be irresponsible and flippant about VT, both in terms of performance and storage reliability. This is a very gross mistake. In fact, on the contrary, if there is a reliable functioning backup system and a lot of time can be allocated for system recovery, then you can store data on a fast, but extremely unreliable RAID-0, but then the VT should be stored on a separate reliable and productive resource (although would be on RAID-1). Why is that? Let's take a closer look. Immediately make a reservation that the presentation is somewhat simplified, but enough 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 essential). MS SQL does not guarantee that immediately after executing the command to change the data, these changes will fall into 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 get to the disk before the transaction is committed. That is, in general case, at active work The FD contains scattered pieces of unfinished data and incomplete transactions for which it is not known whether they will be canceled or committed. There is a special " CHECKPOINT " command that tells the server to "right now" flush all unsaved data to disk, 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 understand that during operation, the FD is usually not in a consistent state.

To deal with this chaos, we just need VT. The following events are written to it:

• Information about the start of the transaction and its identifier.
• Information about the fact of committing or canceling a transaction.
• Information about all data changes in the FD (roughly speaking, what happened and what happened).
• Information about changing the FD itself or the database structure (increasing files, reducing 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 go from the state before this operation to the state after this operation and vice versa (the exception is the bulk-logged 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 the data really needed for recovery gets into the transaction log. In particular not information about what request text led to modifications, what execution plan this request had, what user launched it, and other information that is unnecessary for recovery is received. Some idea about the data structure of the transaction log can be given by the query

Select * from::fn_dblog(null,null)

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

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

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

Cancellation of a transaction in MS SQL Server usually lasts comparable to the total duration of data modification operations of the transaction itself. Try not to cancel transactions or decide to cancel as early as possible.

If the server unexpectedly stops working for some reason, then when it is restarted, it will analyze which data in the FD does not correspond to a consistent state (unrecorded but committed transactions and recorded but canceled transactions) and these 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 considerable time to roll back this transaction, and there is no way to interrupt this process.

What happens when the JT has reached the end of the file? It's simple - if there is free space at the beginning, then it will start writing to the free space 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, which can be many in the physical transaction file, but this is not enough for backup. If the server fails to expand the file (it runs out of disk space or it is forbidden to expand the VT in settings), then the current transaction will be canceled with error 9002.

Oops. And what needs to be done so that there is always a place in the ZhT? 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 LT, starting from the start of the earliest open transaction. This minimum is written and stored in the JT necessarily. Regardless of the weather, server settings and the desire of the admin. The server cannot allow this information to be missing. Therefore, if you open a transaction in one session and perform different actions in others, the transaction log may end unexpectedly. The earliest 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 in SQL Server:

• Simple (Simple)- only the remaining VT necessary for life is stored.
• Full (Full)- the entire VT is stored since the last backup transaction log. Pay attention, not from the moment of a full backup!
• Bulk logged- a part (usually a very small part) of operations are recorded in a very compact format (in fact, only a record that such and such a page of the data file has been changed). The rest is identical to Full.

There are several myths associated with recovery models.

• Simple allows you to reduce the load on the disk subsystem. This is not true. 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 dancing with a tambourine is loading data from an unloading in dt format and restructuring tables.
• When using the Bulk logged model, some operations 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 and it will be possible to "play" the transaction log to the end (although it is not possible at an arbitrary point in time if there are minimally logged operations).

The Bulk logged model for 1C databases is almost pointless to use, so we will not consider it further. But the choice between Full and Simple will be considered 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 the Simple and Full recovery models

There are three types of backups according to the type of formation:

• Full(Full)
• Differential(Differential, difference)
• Log(A backup copy of the transaction logs, considering 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 essentially 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). It consists of a paged copy of the used part of the data files and the active piece of the transaction log for the time while 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 (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 those made before the next full backup. Due to this, you can significantly reduce the amount of disk space for storing the backup.

Important points:

• Without a previous full backup, a differential backup is useless. Therefore, it is advisable to store them somewhere near each other.
• Each subsequent differential backup will store all the pages included in the previous differential backup made after the previous full backup (albeit with possibly 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)
• Enough to recover for a while latest full backup at this point and latest delta copy at this point. Intermediate backups are not needed for recovery (although they may be needed to choose when to restore)

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. RCRT allows you to restore the state at any subsequent time point, included in the interval of the restored backup copy, from a copy restored in NORECOVERY mode to any point in time included in the period of the restored copy of the RT. When creating a backup with standard parameters, the space in the transaction log file is freed (up to the moment of the last open transaction).

It is obvious that RKZhT does not make sense in the Simple model (then VT 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 of the backup copies of this chain, or by switching the database to Simple and vice versa.

Warning: A set of RCSTs is essentially useless unless it is a contiguous chain, and the start time of the last successful full or differential backup must be inside period of this chain.

Common misconceptions and myths:

• "TRKZHT contains transaction log data from the time of the previous full or differential backup." No, it's not. RKZHT also contains seemingly 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, it's not. Full and differential backup do not touch the RKZHT chain.
• VT needs to be periodically cleaned manually, reduced, shrinkat. No, it is not necessary and even vice versa - it is undesirable. If you release the RT between the RCRT, then the RCTC chain needed for recovery will be broken. And constant reductions / extensions 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 changing 10 GB of old data. Made the following backups

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

With this set, we can restore the data at 0:00 on any of the days 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's say we have the same set of 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 more often, then they will be smaller in size.
3. Now we can restore the system state to any point since 0:00 on February 1, when we have the earliest full copy at 12:00 on February 16.

In the simplest case, we need to restore:

1. Last full backup before restore
2. Last differential before restore
3. All RKZhT, from the moment of the last differential copy to the moment of restoration

• Full copy of F2 from 0:00 on February 8
• Differential copy D2.2 from 0:00 on 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 10th. But a significant advantage of the Full model is that we have a choice - to use the latest full or differential copy, or NOT the latest. For example, if it was discovered that the copy of D2.2 was corrupted, and we need to restore to a moment before 13:13:13 on February 10, then for the Simple model this would mean that we can only restore data to the moment D2.1. With Full - "DON" T PANIC", we have the following options:

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

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

Now 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 from a full backup on the neighboring server, leaving the ability to roll out subsequent states with differential copies or RKZHT, i.e. left in NORECOVERY mode. And every time immediately after the formation of the RKZhT, we apply it to this reserve base, leaving it in the 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 the log shipping mechanism, one of the ways to reduce downtime. If you transfer data in this way not once in a period, but constantly, then mirroring will turn out, and if the source base waits until the mirror base is updated, then this is synchronous mirroring, if it does not wait, then asynchronous.

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

• High Availability (Database Engine)
  • Understanding High Availability Solutions
  • High availability. Interaction and collaboration

Other aspects of backup

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

Filegroups

1C:Enterprise, in fact, does not know how to work with file groups. There is a single filegroup 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 individual files). This is necessary either in order to speed up access to some data (putting it on very fast media), or vice versa, sacrificing speed to put it on cheaper media (for example, little-used but voluminous data). When working with filegroups, 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 filegroups will have to be "caught up" to one moment by rolling the RKZHT.

Data files

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

Backup compression

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

If in version 2008 this feature was only for the Enterprise edition (which is very expensive), then in 2008 R2 this feature was given to 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 turn it off.

One backup file - many insides

In fact, a backup is not just a file, it is a rather complex container that can store many backups. This approach has very ancient history(I have personally observed 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 - one file" approach. For general development, it is useful to study the ability to put several backups into one file, but most likely you won’t have to use it (or if you have to, then sorting through the blockages of a would-be administrator who used this opportunity unskilledly).

Multiple mirror copies

SQL Server has another great feature. It is possible to form a backup copy in parallel to several receivers. How the simplest example, you can dump one copy on local disk and simultaneously add to a network resource. A local copy is convenient, since restoring from it is much faster, a remote copy, however, will transfer much better physical destruction main database server.

Examples of backup systems

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

Setting up a typical server redundancy through Maintenance Plans (MaintenancePlan)

This section is built 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 Maintenance Plan Wizard

Setting up server redundancy with TSQL scripts, examples of some features

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

• Use mirrored redundancy
• Use compression settings different from server settings
• Does not allow flexible response to emerging situations (no opportunities for error handling)
• Does not allow flexible use of security settings
• Maintenance plans are very inconvenient to deploy (and maintain the same) on in large numbers servers (even, perhaps, already at 3-4)

The following are typical backup commands

Full backup

Full backup with overwrite existing file(if any) and page checksum checks 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 - differential 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

Mirrored Redundancy

It is often convenient to make not one backup copy at once, but two. For example, one can lie locally on the server (so that it is at hand), and the second is immediately formed into a physically remote and protected from adverse storage 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 overlooked: the user under which the MSSQL Server process is started must have access to the "\\safe-server\backup\" resource, otherwise the copy will fail. If MSSQL Server is running on behalf of the system, then access must 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, split into 2 files, according to the striping principle. And each of them individually will be useless.

This article is devoted to solutions for MS SQL recovery. We will try to consider the main points and important details that you need to consider when planning and choosing a solution for recovering an MS SQL database.

Within MS SQL disaster recovery planning, two parameters are of particular interest: the recovery time objective (RTO) and the recovery point objective (RPO).

In other words, RPO is the period of time from the moment of the last backup to the moment of the incident, during which a non-critical amount of data (information) will be lost. RTO is the allowable time for which it is necessary to restore the service / system to working capacity from the moment of the incident. Both options have variable value and depend on the requirements for a particular system. Therefore, in order to fulfill the established RPO and RTO, it is necessary to have an appropriate backup plan. Using an example, let's analyze possible emergency incidents and try to highlight the points of failure of our SQL server and how to solve them:

• hardware failure, physical failure of the server: disks, CPU, motherboard, power supply, etc.
• software failure: operating system, database

For each designated incident, there is a whole range of measures to avoid the consequences of the incident.

HIGH AVAILABILITY MS SQL

With high requirements for RPO and RTO (seconds / minutes), the only solution to ensure MS SQL fault tolerance is the organization of High Availability server technology:

• Using the built-in tools of MS SQL and Windows Server OS, we can achieve high availability (High Availability) by implementing a failover cluster Windows Server Failover Cluster (WSFC), including using AlwaysOn technology. A failover cluster consists of at least two nodes/servers. When the active server fails, it fails over to another available server and becomes active. In this case, all services that were hosted on the server are automatically or manually transferred to another available node.
• In cases with an MS SQL virtual machine, high availability can be provided with the help of VMware HA-cluster or Hyper-V High Availability virtualization tools. In this case, when the physical server fails, it allows you to automatically start virtual machine on another cluster server.

Both methods can be implemented either separately or together, if necessary. Clustering is more designed to quickly fix a hardware failure.

Benefits of High Availability MS SQL:

• instant switching from node to node, no downtime
• without dependence on physical servers
• allows you to maintain servers without interrupting the work with the database

Disadvantages of High Availability MS SQL:

• implementation requires additional infrastructure and resources
• high price license and hardware solutions
• more complex and highly qualified service

BACKUP MS SQL

In cases where the requirements for RTO and RPO are not high and there is no need for High Availability (clustering), to ensure the fault tolerance of MS SQL databases on a physical or virtual server necessary condition is a backup. To do this, you can use the built-in functions of SQL Server or use separate specialized systems that support various MS SQL backup methods, for example:

These systems will help to avoid both hardware and software failures in the operation of the database server.

After calculating the RTO and RPO values, you can proceed to planning the SQL server configuration. To achieve these values, we can use both the high availability technologies listed above and database backups.

backup MS SQL policy

• Backups must be on different physical media with source files Database
• Use a test server (sandbox) to test the procedure for restoring backups
• Do your daily
• Do it as often as possible. They take up much less storage space and further reduce the risk of data loss
• Back up transaction logs as often as possible. Transaction logs contain everything recent activities occurred in the database. Logs can be used to restore a database to a point in time, and this is the biggest benefit. Transaction log backups can be performed while the system is running. If the frequency of new data being created in your database is very high, then you can backup the transaction log every 10 minutes, while for other databases that are less active, such backups can be performed every 30 or 60 minutes
• Back up MS SQL system databases: server, master , model and msdb . These databases are absolutely essential as they contain the system configuration as well as SQL Server job information that will need to be restored in the event full recovery systems

SETTING UP MS SQL BACKUP USING BACKUP EXEC

Backup Exec offers three MS SQL backup methods: Full, Differential, and Full Copy-only. The Full method performs a full backup of the entire database, while Differential only backs up the changed blocks in the database since the last full backup. The Full Copy-only method is identical to a full backup, except that it does not affect subsequent differential backup jobs.

Let's consider each case in more detail. To do this, we will create a new task in the system to back up the main and system databases.

Then, in the parameter settings (options), select the type of task (first set Full then Differential backup).

Backup Exec has a very important and useful feature "Consistency check before/after backup" (Consistency check before/after backup), there are four options to choose from:

• do not check
• full check, excluding indexes
• full check based on indexes
• only physical check

To set up differential backup, you must (similarly to job full backup) first add a new job Job Differential, and then on Microsoft tab SQL select one of the backup methods.

In this list, we are primarily interested in "Differential - Backup up database changes since the last full"(creating a differential backup based on a full backup). It is also possible to create a differential backup (at the block level) with subsequent conversion to a virtual machine "Differential (block-level) - Backup up database changes since the last full - use with convert to virtual machine job".

Another important parameter is "Log - Back up and truncate transaction log" for MS SQL transaction log backup.

We have covered the main points of MS SQL backup. Please note that backup is part of the overall Disaster Recovery Plan (DRP), therefore, before planning a backup, it is necessary to conduct a complete analysis of systems and infrastructure to ensure RPO and RTO. And if it is possible to perform DRP planning during system development, this will help eliminate many problems and, possibly, reduce the cost of operating the system.

The information used in the article is taken from official sources.

Let's look at how to organize the two most common SQL Server administration tasks:

• Automatic database backup;
• Removing old backups.

Planning for database backups

• Open SQL Management Studio and connect to the required database. Make sure the SQL Server Agent is running;
• Expand the node Management - Maintenance (for this you must have the role "SYSADMIN") - right-click and select "New Maintenance Plan";
• Enter a name for the new maintenance plan;
• Click on the calendar icon on the right side of the single line. In the window that opens, configure the task execution time. Choose a time when the database is less busy;
• From the Toolbox section, drag the Backup Database Task to the main area;
• Double click on Backup Database Task - the backup task settings window will open - set the desired settings;
• Click OK - now backups will be created in accordance with the scheduled time;

Deleting old backups

Since backup files will be created frequently, soon free space on your hard drive will decrease. Therefore, you will need to delete obsolete backup files. Let's continue configuring the maintenance plan:

• From the Toolbox, drag the Maintenance Cleanup Task to the main area;
• Double click Maintenance Cleanup Task to open the properties window. In it, you must define the location of the backups, their extension and determine the age of the files to be deleted. It is good practice to keep backups up to one month;
• Click OK and save the maintenance plan;
• Then you can either wait until the next maintenance plan execution time, or execute it manually (by right-clicking on the maintenance plan in Object Explorer).

We continue to talk about backup and today we will learn create an archive Microsoft bases SQL Server 2008. We will consider everything as usual with examples using both the graphical interface and using SQL query, as well as set up automatic creating a backup using a batch file.

We will not return to the question of the importance of database backup, since we have already raised this topic more than once, for example, in the materials:

And in the last article, I said that we will consider the possibility of creating an archive on the MS SQL Server 2008 DBMS, so now we will do just that.

And since there was already a lot of theory, let's immediately move on to practice, namely, to creating a backup base.

Note! As can be seen from the title of the article, we will make the archive on the Microsoft SQL 2008 DBMS using Management Studio. The server is located locally. OS Windows 7.

How to create an archive of a SQL server database

Let's decide that we will make an archive of a test database called "test". From the beginning through GUI, and in the process of this, we will write a script so that in the future we can simply run it and no longer be distracted by entering all kinds of parameters.

Open Management Studio, expand « Database» , select the desired base, right-click on it, select Tasks->Backup

You will see the window " Database backup”, where you can set archiving parameters. I just gave a name Backup set", and also changed the name of the archive and the path, since by default it will be created in the Program Files folder, for example, I had the default path

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\

For example, I changed it to C:\temp\ and named the archive test_arh.bak

Also if you go to the tab « Options», then you can set the setting to overwrite all data sets, now I will explain what it is. If you leave everything as it is, i.e. add to an existing dataset, then you will have one backup file, but with several instances of datasets, i.e. when restoring, you simply select the set you need. And if you put " Overwrite all existing backup sets”, then the set will always be the same, then in this case you will need to create archives (let's say daily ones) with different names. I set it to overwrite, because let's say, in the future, I plan to create archives for each day with the date in the name of these archives, in order to quickly copy the backup I need for a certain date to any place if necessary.

And by the way, at this point, after entering all the parameters, you can create a script in order to record it and use it later. To do this, simply click on the top Scenario».

And as a result of this action, a query window will open, in which there will be a code this scenario. We will return to it a little later, but for now, click "OK" and after the operation is completed, you will see a window in which the result of the backup will be indicated, if everything is fine, then the following message will appear

Create an archive of the SQL server database through a query

If you have done everything as above those. clicked "Script"), then you have opened a query window, which actually contains the archive creation request itself, but we will redo it a little, since I said that we plan to run it every day, so that the name is appropriate, we will write this SQL statement .

DECLARE @path AS VARCHAR(200) SET @path = N"C:\temp\test_arh_" + CONVERT(varchar(10), getdate(), 104) + ".bak" BACKUP DATABASE TO DISK = @path WITH NOFORMAT, INIT, NAME = N"Database test", SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO

And now if we run it, then we will create a database backup with the name test_arh_ The current date.bak

Automatic creation of backup on SQL server

For these purposes, MS SQL 2008 has a special feature called " Service Plans”, where you can just set up a schedule for creating a database backup, but I suggest using a bat file for these purposes to set it up in the scheduler and have it run every day and backup the database.

To do this, copy the SQL statement that we reviewed above and paste it into notepad ( I recommend Notepad++), then save with extension .sql those. this script will be executed on MS Sql 2008. Then we will have to write a batch file so that it connects to the SQL server and executes our script. Also write in notepad:

SET cur_date=%date:~6.4%%date:~3.2%%date:~0.2% osql -S localhost -i C:\temp\test.sql -o C:\temp\%cur_date %_log_sql.log -E

where, I created a cur_date variable to store the current date in it, then I connect to local server, through the utility osql, which uses ODBC and executes our script ( I called it test.sql), and also write a log, where and just we needed our variable, that's all, save with the extension .bat, we create a task in the scheduler and we can say we forget about the process of archiving the database, well, we only periodically check whether the archive has been created or not.

For the basics, this is quite enough, now you know how you can back up databases on a 2008 SQL server, in the next article we will look at how you can restore a database on MS SQL Server 2008. In the meantime, that's all! Good luck!