And also: SQL backup, 1C backup.
Server 1C contains data in the database, which is located on the SQL server. Today we are considering MS SQL 2005/2008.
To ensure that data is not lost in the event of a burnt server disk or other force majeure situations, it is necessary to make backups from the very beginning.
Of course, no one wants to make pens every day Backup SQL database 1C. There are automatic tools for this. Let's get to know them.
Configuring BackupSQL
Setting up Backup SQL for a 1C database is no different from setting up a backup for any other database.
To configure, run MS SQL Management Studio. This program is in the MS SQL program group.
Adding a 1C SQL database backup task
Tasks for automatic backup of SQL databases are located in the Management / Maintenance plans branch.
To add a new backup task, click on the Maintenance plans group right click click and select New Maintenance Plan.
Enter the name of the task. The name matters only to you. Just in case, it is better to use English characters.
Setting up a 1C SQL database backup job
The Job Editor will open. Please note that jobs can do various operations with the database, and not just backups.
The list of options for operations is displayed at the bottom left. Select Back Up Database Task by double-clicking or simply drag to the right.
Notice the arrow. You can drag and drop several different or identical operations and link them with arrows. Then several tasks will be executed at once in the sequence specified by you.
In the settings window, select the required SQL 1C databases (you can have several or one at a time).
Select the location to save the backup of the SQL 1C database. You must select a physically different hard drive. Organizationally, you can check the "Create subfolders" box.
Now let's set up the backup schedule. The default backup schedule was added by itself. But you can add multiple schedules (for example, one daily, one weekly, etc.). Click the backup schedule settings button.
The screenshot shows an example of a daily Backup SQL database 1C at 3 am.
To make the backup schedule in the list nice and understandable, you can change it.
Saving a 1C SQL database backup task
Click burn. The task will appear on the left side of the list.
It is important! Check that the Backup SQL database task was created correctly. To do this, right-click on the task and select Execute.
As a result, a backup file should appear at the specified path. If something is wrong, delete the task (Del) and start from the beginning.
After studying a lot of information from different sources, I decided to describe the setup process Reserve copy DB MS SQL Server For complete recovery models, which model to use is up to you, but on my own I will add that if your database has a large flow of information (for example, tens, hundreds or thousands of documents are created in 1 hour), then the loss of information for a day of work will be simply unacceptable, in this case only a full model will ensure the safety of your data. This article is for beginners. system administrators and contains in my opinion the minimum set of actions for 1C database backup. Installing \ Configuring the SQL server itself and deploying a database on it is not within the scope of this article.
All settings will be made using SQL Management Studio. First you need to create a Backup Device, you can not create it, but in my opinion it is much more convenient and more correct. in a snap SQL Management Studio -> Server Objects-> Backup Devices. You need to specify the name of the device and the file in which the backups will be stored (preferably with the BAK extension), then you can view the contents of the media, all backups will be listed there.
Now you can start setting up the Maintenance Plan. You can create a Maintenance Plan for all databases at once, but it is more convenient to create your own maintenance plan for each database.
Our Maintenance Plan will have three sub-plans: 1 - database backup (Full); 2 - database backup (Differential); 3 - Backup of the Transaction Log. Each subplan has its own execution schedule. Everyone adjusts the schedule according to their own discretion, in my case, full copying is done once a week on Sunday, Differential copying every day except Sunday, JT - transaction log every hour. With this backup model, you can restore the desired database to any date and time, and we save space on the hard disk. a full backup is actually done once a week, and only changes during the week.
Setting up a daily schedule. The weekly one differs only in the checked box "Sunday" and removed from "Monday" to "Saturday" 
Schedule for ZhT. Red highlights the time of saving during the day, it makes sense, for example, if users work with the database during a certain period, if the operating mode is 24x7, then we leave it by default.
The figure below shows the editor of the weekly subplan, it consists of tasks that are performed in a given sequence. The sequence is set manually, and the green arrows mean that the next task will be executed only if the previous task is successfully completed, and the blue one means that the task will be executed on any completion of the previous task. In the maintenance sub-plan editor, tasks can be added from the "Elements panel" which is located in the upper left corner when the editor is open.
Tasks. You need to go into each task and select the database for which it will be performed and a number of other settings (if any). Consider what tasks the weekly subplan of our maintenance plan contains.
1. "Check Database Integrity Task". The next task will be executed only if the database contains no errors. (Replace to back up a database with errors?)
2. "Rebuild Index" (Rebuild Index Task). It is necessary to restore (rebuild) the index every day, because when working with indexes, they are heavily fragmented, and when fragmentation is more than 25%, SQL begins to "slow down" noticeably. This operation is quite resource-intensive, so it can be done at least once a week, and in daytime subplan to replace its less resource-intensive "Index Reorganization" task.
3. "Update Statistics" (Update Statistics Task). For optimization... By the way, this task can be performed several times during the day, if your database is heavily loaded.
4. After updating the statistics, it is MANDATORY to clear the procedural cache. To do this, drag the "Execute T-SQL statement" task into the editor and in the " T-SQL statement:" write a procedure DBCC FREEPROCCACHE. But keep in mind that this procedure clears the cache of ALL databases, and we updated the statistics one by one! How to clear the procedural cache for a specific database, read. In short: DBCC FLUSHPROCINDB(DB_ID)
5. "DB backup" (Back Up Database Task). In this task, we indicate which database we are backing up, the type of backup (For a weekly subplan - Full, for a daily one - Differential, for an hourly one - Transaction Log.) Set the switch to "Back up databases in one or more files" and add the previously created backup device. In this case, ALL copies are saved in one file, which was specified when creating backup devices, if the switch is left in "Create a backup file for each database", then for each backup will be created separate file and on the Full and on the Differential and on the ZhT, which is very inconvenient for recovery, but convenient for storage. Do not forget to specify that you need to compress backups!
6. "Journal Cleanup" Clears the entries created during the execution of tasks. You can also enable the "Clean up after maintenance" task and configure it to remove text logs or obsolete backups.
Sub-plan for VT backup, consists of one task "Database backup". It is more convenient for me to save the VT not to the Backup Device, but to a separate file, which must be specified in the task settings.
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:
For each designated incident, there is a whole range of measures to avoid the consequences of the incident.
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:
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:
Disadvantages of High Availability 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 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"Checking the integrity of the database before and after the backup" (Consistency check before / after backup), there are four options to choose from:
To configure differential backup, you must (similarly to job full backup) first add a new Job Differential job, and then select one of the backup methods on the Microsoft SQL tab.
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.
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 the SQL query, and we will also 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.
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» , choose 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, you will open a query window, in which there will be a code for this script. 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
If you have done everything as above those. clicked "Script"), then you have a query window open, 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 such an 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
For these purposes, MS SQL 2008 has special opportunity entitled " 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 it! Good luck!
Let's look at how to organize the two most common SQL Server administration tasks:
Since backup files will be created frequently, you will soon have less free space on your hard drive. Therefore, you will need to delete obsolete backup files. Let's continue configuring the maintenance plan:
