Ms sql server backup. Setting up regular backup of MS SQL Server database. Compressing backups

This article will show you how to manually make a full database backup using the "Environment Microsoft SQL Server Management Studio ".

1. Creating a backup

It's actually pretty simple. We launch the snap-in “ » (« Start» — « All programs» — « SQL Server 2008 R2» — « Microsoft SQL Server Management Studio») And enter the data for authorization.

After that, in the Object Explorer, open the tab " Database"And click right click mouse over the database for which you want to back up. In the emerging context menu choose “ Tasks» ( Tasks) — « Create a backup» ( Back Up ...) .

The window “ Database backup» ( Back Up Data Base). Let's make sure it's worth " Full» ( Full), if necessary, set a name and description, and also indicate the purpose of the backup. By default, the path on the computer hard disk to the Backup folder of the main location of the SQL server databases is selected. In order to change the location of the copy, first press " Delete» ( Remove) to remove the existing assignment, and then “ Add» ( Add…) To add a new one.

Here we set the location and name of the backup file and click " OK". You can specify several such destinations. In this case, the backup will be split into equal parts, each part in the specified file.

When all the settings are set, click " OK"And wait for the task to complete. If everything is done correctly, we will find the SQL database backup file in the specified directory.

2. Restoring a database from a backup

Recovery takes place in a similar way. V " Microsoft SQL Server Management Studio»Choose a base from which the backup was made, click on it with the right mouse button, in the context menu, select “ Tasks» ( Tasks) — « Restore» ( Restore) — « Database…» ( Database ...).

The window “ Database recovery» ( Restore database). Here, as a source, we indicate “ From device» ( From device) and select the backup file (created in step 1).

Let's set the flag “ Restore» ( Restore) opposite the selected backup. If necessary, on the tab “ Options» ( Options), you can specify additional recovery options, the meaning of which can be read.

After all the settings are made, click " OK"And wait for the message about successful database recovery.

3. Restoring a backup to another database (copying data)

If you need to load data into the database, different from the one from which the backup was made, then when loading, in addition to the actions described in paragraph 2, it is necessary on the tab " Options"(Options) set the file names of this database and set the flag" Overwrite existing database"(WITH REPLACE).

Did this article help you?

And also: SQL backup, 1C backup.

Server 1C contains data in a database located on a SQL server. Today we are considering MS SQL 2005/2008.

To prevent data loss in the event of a burned-out server disk or other force majeure situations, it is necessary to make backups from the very beginning.

Make pens every day Backup SQL databases Of course, no one wants 1C. There are automatic tools for this. Let's get to know them.

Configuring Backup SQL

Setting up Backup SQL for a 1C database is no different from setting up a backup for any other database.

Launch MS SQL Management Studio to configure. This program is in the MS SQL program group.

Adding a backup task for SQL database 1C

Automatic backup tasks for SQL databases are located in the Management / Maintenance plans branch.

To add a new backup task, right-click on the Maintenance plans group and select New Maintenance Plan.

Enter a name for the task. The name matters only to you. It's better to use English characters just in case.

Setting up a backup task for SQL database 1C

The quest editor will open. Please note - tasks can do various operations with a database, not just backups.

The list of options for operations is displayed at the bottom left. Double-click the Back Up Database Task or simply drag to the right.

Pay attention to the arrow. You can drag several different or identical operations and link them with arrows. Then several tasks will be performed at once in the sequence you have defined.

In the settings window, select the required 1C SQL databases (you can several or one at a time).

Select the location to save the 1C SQL database backup. You must select a physically different hard drive. Organizationally, you can check the "Create subfolders" checkbox.

Now let's set up the backup schedule. The backup schedule was added by default by itself. But you can add several schedules (for example, one is daily, one is weekly, etc.). Click the button to configure the backup schedule.

The screenshot shows an example of the daily SQL Backup of the 1C database at 3 nights.

To make the backup schedule in the list nicely understandable, you can change it.

Saving a backup task for SQL database 1C

Click burn. The task will appear on the left in the list.

It is important! Check that the Backup SQL database job was created correctly. To do this, right-click on the job 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 over.

Let's restore the "Test _Recovery" database to the state " t 4».

Let's start restoring the database from the full backup "Full2_Test_Recovery.bak" using "SQL Server Management Studio ". Right-click on the database " Test _ Recovery ", In the menu that appears, select" Tasks ", then" Restore ", then" Database ".

In the appeared window " Restore Database "in the" Sourse "section, select" Device ". Next “Add ", Write 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 successfully restored.

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

Right-click on the "Test_Recovery" database, select "New Query" in 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 successfully restored.

In this example, we have used the "REPLACE" parameter:

Recovery usually prevents another database from accidentally overwriting a 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 database family GUID recorded in the backup set, it will not be restored.

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

Check for recovery over an existing database from a backup created for another database.When using the REPLACE parameter during a restore, you can overwrite the existing database regardless of which databases are contained in the backup set, even if the specified data name is different from the one written in the backup set. This can lead to accidental overwriting of another database over the database.
Check for recovery on a database that uses a full or bulk-logged recovery model that has not been backed up to the tail of the log and the STOPAT option has not been applied.If you use the REPLACE option, you may lose committed data because the last logged data has not yet been backed up.
Overwrite existing files.

After studying a lot of information from different sources, I decided to describe the setup process Reserve copy MS SQL Server DB 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 per day of work will be simply unacceptable, in this case only the complete model will ensure the safety of your data. This article is intended for novice system administrators and contains in my opinion the minimum set of actions for backing up the 1C database. Installing \ Configuring the SQL server itself and deploying the 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 don't need to create it, but in my opinion it is much more convenient and correct. in 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), in the future you can view the contents of the media, all the backups will be listed there.

Now you can start setting up the Maintenance Plan. A maintenance plan can be created for all databases at once, but it is more convenient to create a maintenance plan for each database.

There will be three sub-plans in our Service Plan: 1 - DB backup (Full); 2 - DB backup (Differential); 3 - Backing up the Transaction Log. Each sub-plan has its own execution schedule. Everyone adjusts the writing at their own discretion, in my case full copy done once a week on Sunday, Differential copying every day except Sunday, ZhT - the transaction log every hour. With this model of redundancy, you can restore the desired database to any date and hour, and we save space on the hard disk since full reservations are made actually once a week, and only changes during the week.

Setting up a daily schedule. Weekly differs only in the set "Sunday" checkbox and unchecked from "Monday" to "Saturday"

Schedule for ZhT. The time of saving during the day is highlighted in red, 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 weekly sub-plan editor, it consists of tasks that are performed in a given sequence. The sequence is set manually, with green arrows indicating that the next task will be completed only if the previous task is completed successfully, and blue arrows mean that the task will be completed whenever the previous task is completed. In the maintenance sub-plan editor, tasks can be added from the Toolbox, which is 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). Let's take a look at what tasks the weekly sub-plan of our maintenance plan contains.

1. "Check Database Integrity Task". The next task will be executed only if the database contains no errors. (Replace with backing up the database with errors?)

2. "Rebuild Index Task". It is necessary to rebuild (Rebuild) the index every day, because when working with indexes, they are highly fragmented, and when more than 25% fragmentation occurs, SQL starts 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 task "Index Reorganization".

3. 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 statistics, you MUST clear the procedural cache. To do this, drag the "Execute T-SQL statement" task into the editor and write a procedure in the "T-SQL statement:" field DBCC FREEPROCCACHE... But keep in mind that this procedure clears the cache from ALL databases, and we updated the statistics one by one! We read how to clear the procedural cache for a specific database. In short: DBCC FLUSHPROCINDB (DB_ID)

5. "Back Up Database Task". In this task, we indicate which database we are backing up, the type of backup (For a weekly sub-plan - Full, for a daily sub-plan - Differential, for an hourly - Transaction log.) We put the switch in the position "Create a backup copy of databases in one or several files" and add earlier created backup device. In this case, ALL copies are saved in one file, which was specified during creation. 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 Full and on Differential and on ZhT, which is very inconvenient during recovery, but convenient during storage. Do not forget to specify that you need to compress backups!

6. "Log Cleanup" Cleans up the entries created during the execution of tasks. You can also enable the "Cleanup after maintenance" task and configure it to delete text logs or obsolete backups.

The subplan for backing up the VT, consists of one task "Backing up the database". For me, it is more convenient to save the ZhT not to the Backup device, but to a separate file, which must be specified in the task setting.

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

The Bacula Systems script for creating backups of MS SQL Server 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, use a variety of options for creating backups of MS SQL.

The MS SQL Bacula Systems backup script functions independently of the VSS. This means the MS SQL backup tool does not use VSS snapshots to create backups. Therefore, the user can set the following value “Enable VSS = no” in the Bacula FileSet. Efficient creation of backups of MS SQL Server and their recovery using this decision are achieved by using the Microsoft API for SQL Server. This enables Bacula Systems to support the security mechanisms and all types of authentication available 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 up to a point in time. By implementing full and bulk-logged recovery models, you can recover MS SQL using PITR recovery, or use LSN to recover the system to a specific state. You can restore a specific state of an MS SQL database to any specific point in time with precision to the second. In the case of a MS SQL transaction log backup, when restoring, the state of the database will be restored from the various selected backups.

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

Bacula Systems has created a plug-in for MS SQL Server backup for use with Bacula Enterprise Edition. Backup MS SQL Server with Bacula has the following features:

Supports full and differential MS SQL backups
Support for MS SQL incremental backups
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 a database from the procedure for creating backups
Support for creating read-only database backups
Restoring MS SQL backups to disk
Sending a Backup Stream Directly to the Storage Daemon
MS SQL point-in-time recovery

Overview and configuration of MS SQL 2008, 2008 R2, 2012 and 2014 backups

V this document presented solutions for Bacula Enterprise Edition 8.4 and later that are not supported early versions ON. 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 is possible 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 it 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 backup. During a differential backup, only the data that has changed since the last full backup 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, the entire database is backed up. The backup includes a portion of the transaction log for the purpose of restoring a complete database from a backup. Full database backups contain the database at the time the backup is completed. For more information, see https://msdn.microsoft.com/en- us / library / ms186289.aspx.
Backup "only for copying" (CopyOnly). Copy-only backups are MS SQL backups that are independent of the normal sequence of traditional SQL Server backups. Sometimes it is useful to create backups for special needs without affecting the overall process of backing up and restoring the database. For more information, see https://msdn.microsoft.com/en-us/library/ms191495.aspx.
VDI(Interface virtual device) Is a Microsoft technology that allows you to create named pipe between programs.
standard masks specify wildcard string sets. For example, the default production * mask would include the lines production1 and production2.
line
integer.
LSN Each entry in the MS SQL Server transaction log is identified with a unique registration number transactions (LSN). More 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 the transaction log are saved, which allows you to fully protect the MS SQL database in the event of a media failure. In case of damage to one or more files, restoring the MS SQL database from a backup will restore all committed transactions. It will also rollback all transactions in progress. 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. During the creation of a differential MS SQL backup, only the data that has changed since the last full MS SQL backup was created. For the MS SQL differential backup function, the sequence of backups is extremely important. If for some reason full backup referenced by MS SQL is not available, MS SQL Server differential backups cannot be used. Bacula's MS SQL Backup uses specific techniques to solve this problem. Therefore, in case of difficulties, the status of the differential backup of the database can be automatically upgraded to a full backup.

MS SQL transaction log backup 2008, 2008 R2, 2012 and 2014

MS SQL backup setup and database configuration

Restoring MS SQL database from backup

You can use everything standard methods starting the procedure for restoring MS SQL database from a backup. However, you should make sure that, in the event of restoring differential data, a 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 database instances.

Options for restoring MS SQL database from backup

Bacula Enterprise Edition software allows users to use many MS SQL recovery options and apply the most different 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 determine how Bacula should behave with the current database when restoring. Bacula's MS SQL Backup also allows you to use a few more options when restoring, for example:
Instance: Since MS SQL uses multiple instances, Bacula's MS SQL backup allows you to choose which instance to restore. This parameter is optional, and if it is not specified, the restore will use the value specified when creating the backup. By default, an instance named “MSSQLSERVER” is used.
Database. This option specifies the name of the database to restore and it uses the value set at the time the database was created. This parameter is optional. By default, backing up SQL Server databases uses 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 restore will use the value specified when creating the backup.
Password. The password used to connect to the MS SQL database instance. This parameter is optional, and if it is not specified, the restore will use the value specified when creating the backup.
Domain. The domain used to connect to the MS SQL database instance. This parameter is optional, and if it is not specified, the restore will use the value specified when creating the backup.
Recovery. This parameter allows you to determine whether the database will be rolled back to previous state when recovering or not. By default, when restoring the database, it will rollback to the previous state.
Stop_before_mark. Condition WITH STOPBEFOREMARK = Used to indicate that the transaction log entry immediately preceding the flag is a recovery point. The restore point can be date and time, LSN, or mark_name.
Stop_at_mark. Condition WITH STOPATMARK = Used to indicate that a flagged transaction is a recovery point. STOPATMARK moves forward to the flag and triggers a replay of the marked transaction. The restore point can be date and time, LSN, or mark_name.
Stop_at = ... The WITH STOPAT = condition 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.

MS SQL point-in-time recovery can be performed directly from the MS SQL backup plugin. You can also restore files locally and perform operations from the Microsoft SQL Server Mangement Console to be able to use more features.

LSN

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

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

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

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

Number Last LSN corresponds to the last transaction logged.

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 title, in our case 42000162001, corresponds to the last LSN number of the previous job (for creating a full or incremental backup).

Figure 2: First LSN, last LSN and LSN in file names

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

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

Or, if the last 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	Way		where = c: / tmp
Restore the original database			where = /
Recover with a new name	Name		where = newdb
Recover with a new name		Name	database = newdb
Recover with a new name and move files		Name

Table 1: MS SQL Recovery Scenarios

2.3.1 Recovering MS SQL database with original name

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

Restoring a MS SQL backup with a new name

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

If the original database is no longer available, then the parameter where, or the “Plugin Options” field can contain the name of the new database. Bacula's MS SQL backup will automatically create the database with a new name.

If the original DB is still required, the where parameter will be used to move the files to disk and the new DB will need to be named using the Plugin Options menu. In the recovery tree, select the layout.dat file.

Using the My Catalog

Run MS SQL repair task:

Using the My Catalog, start the MS SQL database repair task:

MS SQL recovery to local disk

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