Setting up regular backups of the MS SQL Server database. Backing up Microsoft SQL Server databases Ms sql server backup

Database servers are one of the key servers in any organization. They are the ones who store information and provide output upon request, and it is extremely important to preserve the database in any situation. The basic package usually includes the necessary utilities, but an administrator who has not previously encountered a database will have to spend some time understanding the peculiarities of the work in order to ensure automation.

Types of database backups

First, let's figure out what kind of backups there are. A database server is not a regular desktop application, and to ensure that all ACID (Atomic, Consistency, Isolated, Durable) properties are met, a number of technologies are used, and therefore creating and restoring a database from an archive has its own characteristics. There are three different approaches to backing up data, each with their own pros and cons.

With a logical, or SQL, backup (pg_dump, mysqldump, SQLCMD), a snapshot of the contents of the database is created, taking into account transactional integrity and saved as a file with SQL commands (you can select the entire database or individual tables), with which you can recreate the database on another server. This takes time (especially for large databases) to save and restore, so very often this operation cannot be performed and is performed during minimal load (for example, at night). During recovery, the administrator will need to run several commands to prepare everything necessary (create an empty database, accounts, etc.).

Physical backup (file system level) - copying files that the DBMS uses to store data in the database. But a simple copy ignores locks and transactions, which are likely to be incorrectly saved and broken. If you try to attach this file, it will be in an inconsistent state and will result in errors. To get an up-to-date backup, the database must be stopped (you can reduce downtime by using rsync twice - first on a running one, then on a stopped one). The disadvantage of this method is obvious - you cannot restore specific data, only the entire database. When starting a database restored from a file system archive, you will need to check its integrity. Various assistive technologies are used here. For example, in PostgreSQL there are WAL (Write Ahead Logs) proactive logging logs and a special function (Point in Time Recovery - PITR) that allows you to return to a specific database state. With their help, the third scenario is easily implemented, when a file system level backup is combined with a backup copy of WAL files. First, we restore the backup files of the file system, and then, using WAL, the database is brought to the current state. This is a slightly more complex approach for administration, but there are no problems with the integrity of the database and restoring databases to a certain time.

Logical backup is used in cases where it is necessary to make a full copy of the database one-time or in everyday use, creating a copy does not require much time or space. When unloading databases takes a lot of time, you should pay attention to physical archiving.

Barman

License: GNU GPL

Supported DBMS: PostgreSQL

PostgreSQL supports physical and logical backup capabilities, adding another WAL layer to them (see sidebar), which can be called continuous copying. But managing multiple servers using standard tools is not very convenient even for an experienced administrator, and in the event of a failure, seconds count.

Barman (backup and recovery manager) is an internal development of the 2ndQuadrant company, which provides services based on PostgreSQL. Designed for physical PostgreSQL backup (logical does not support), WAL archiving and quick recovery after failures. Supports remote backup and recovery of multiple servers, point-in-time-recovery (PITR) functions, and WAL management. SSH is used to copy and send commands to a remote host; synchronization and backup using rsync allows you to reduce traffic. Barman also integrates with standard utilities bzip2, gzip, tar and the like. In principle, you can use any compression and archiving program, integration will not take much time. Various service and diagnostic functions have been implemented to monitor the status of services and regulate bandwidth. Pre/Post scripts are supported.

Barman is written in Python, and backup policies are managed using the user-friendly barman.conf INI file, which can be located in /etc or the user's home directory. The delivery includes a ready-made template with detailed comments inside. Works only on *nix systems. To install on RHEL, CentOS and Scientific Linux, you should connect EPEL, a repository that contains additional packages. The official repository is available to Debian/Ubuntu users:

$ sudo apt-get install barman

The repository does not always have the latest version; to install it, you will have to refer to the source texts. There are few dependencies and the process is easy to understand.

Sypex Dumper

License: BSD

Supported DBMS: MySQL

MySQL comes with the mysqldump and mysqlhotcopy utilities, which allow you to easily create a database dump; they are well documented, and you can find a large number of ready-made examples and frontends on the Internet. The latter allow a beginner to quickly get started. Sypex Dumper is a PHP script that allows you to easily create and restore a copy of a MySQL database. Created to work with large databases, it works very quickly, is understandable and easy to use. Knows how to work with MySQL objects - views, procedures, functions, triggers and events.

Another plus, unlike other tools that perform transcoding in UTF-8 when exporting, in Dumper the export is performed in the native encoding. The resulting file takes up less space and the process itself is faster. One dump can contain objects with different encodings. Moreover, it is easy to import/export in several stages, stopping the process during load. When resuming, the procedure will begin from where it stopped. There are four options available for recovery:

• CREATE + INSERT - standard recovery mode;
• TRUNCATE + INSERT - less time for creating tables;
• REPLACE - we restore old data in the working database without overwriting new ones;
• INSERT IGNORE - we add deleted or new data to the database without touching existing ones.

Supports copy compression (gzip or bzip2), auto-deletion of old backups, viewing the contents of a dump file, and restoring only the table structure. There are also service functions for managing the database (creating, deleting, checking, restoring the database, optimization, cleaning tables, working with indexes, etc.), as well as a file manager that allows you to copy files to the server.

Management is carried out using a web browser, the interface using AJAX is localized out of the box and creates the impression of working with a desktop application. It is also possible to run tasks from the console and on a schedule (via cron).

For Dumper to work, you will need a classic L|WAMP server; installation is standard for all applications written in PHP (copy files and set permissions), and will not be difficult even for a beginner. The project provides detailed documentation and video tutorials demonstrating how to use Sypex Dumper.

There are two editions: Sypex Dumper (free) and Pro ($10). The second has more features, all the differences are listed on the website.

SQL Backup And FTP

License:

Supported DBMS: MS SQL Server

MS SQL Server is one of the popular solutions, and therefore occurs quite often. A backup job is created using SQL Server Management Studio, Transact-SQL itself, and SQL PowerShell module cmdlets (Backup-SqlDatabase). On the MS website you can find a huge amount of documentation that allows you to understand the process. The documentation, although complete, is very specific, and information on the Internet often contradicts each other. A beginner will actually need to practice first, “getting his head in”, so, even despite everything that has been said, third-party developers have room to expand. In addition, the free version of SQL Server Express does not have built-in backup tools. For earlier versions of MS SQL (before 2008), you can find free utilities, for example SQL Server backup, but in most cases such projects have already been commercialized, although they offer all the functionality often for a symbolic amount.

For example, the development of SQL Backup And FTP and One-Click SQL Restore follows the “set it and forget it” principle. Having a very simple and intuitive interface, they allow you to create copies of MS SQL Server (including Express) and Azure databases, save encrypted and compressed files on FTP and cloud services (Dropbox, Box, Google Drive, MS SkyDrive or Amazon S3), the result can be view immediately. It is possible to launch the process either manually or according to a schedule, send a message about the result of the task by email, or run custom scripts.

All backup options are supported: full, differential, transaction log, copying a folder with files and much more. Old backups are deleted automatically. SQL Management Studio is used to connect to the virtual host, although there may be nuances and this will not work in all such configurations. There are five versions available for download - from the free Free to the sophisticated Prof Lifetime (at the time of writing these lines it cost only $149). Free's functionality is quite sufficient for small networks with one or two SQL servers installed, all basic functions are active. The number of backup databases, the ability to send files to Google Drive and SkyDrive, and file encryption are limited. Although the interface is not localized, it is very simple and understandable even for a beginner. You just need to connect to the SQL server, after which a list of databases will be displayed, you should select the ones you need, configure access to remote resources and specify the time for the task to complete. And all this in one window.

But there is one “but”. The program itself is not intended for archive recovery. For this, a separate free utility, One-Click SQL Restore, is offered, which also understands the format created by the BACKUP DATABASE command. The administrator only needs to specify the archive and server to which to restore the data, and press one button. But in more complex scenarios you will have to use RESTORE.

Features of MS SQL Server backup

Creating a backup copy and restoring a DBMS has its own differences that need to be taken into account, especially when transferring an archive to another server. As an example, let's look at some of the nuances of MS SQL Server. To archive using Transact-SQL, use the BACKUP DATABASE command (there is also a differential DIFFERENTIAL command) and the BACKUP LOG transaction log.

If the backup is deployed on another server, you need to make sure that the same logical drives are present. As an option, you can manually specify the correct paths for the database files using the WITH MOVE option of the RESTORE DATABASE command.

A simple situation - backup and transfer of databases to other versions of SQL Server. This operation is supported, but in the case of SQL Server it will work if the version of the server on which the copy is deployed is the same or newer than the one on which it was created. Moreover, there is a limitation: no more than two versions are newer. After restoration, the database will be in compatibility mode with the version with which the transition was made, that is, new functions will not be available. This can be easily fixed by changing COMPATIBILITY_LEVEL. You can do this using GUI or SQL.

ALTER DATABASE MyDB SET COMPATIBILITY_LEVEL = 110;

You can determine which version the copy was created on by looking at the archive file header. To avoid experimentation, when upgrading to a new version of SQL Server, you should run the free Microsoft Upgrade Advisor utility.

Iperius

License: commercial, there is a Free version

Supported DBMS: Oracle 9–11, XE, MySQL, MariaDB, PostgreSQL and MS SQL Server

When you have to manage several types of DBMS, you cannot do without combines. The choice is large. For example, Iperius is a lightweight, very easy to use, yet powerful file backup program that features hot database backups without interruption or blocking. Provides full or incremental backup. Can create full disk images to automatically reinstall the entire system. Supports backup to NAS, USB devices, streamer, FTP/FTPS, Google Drive, Dropbox and SkyDrive. Supports zip compression with no limit on file size and AES256 encryption, launching external scripts and programs. Includes a very functional task scheduler, parallel or sequential execution of several tasks is possible, the result is sent by email. Numerous filters, variables for personalizing paths and settings are supported.

FTP upload capability makes it easy to update information across multiple websites. Open files are backed up using VSS (volume shadow copy) technology, which allows you to make hot backups of not only DBMS files, but also other applications. For Oracle, the RMAN (Recovery Manager) backup and recovery tool is also used. To avoid overloading the channel, it is possible to configure the bandwidth. Backup and recovery are managed using a local and web console. All functions are visible, so to set up a task you only need to understand the process; you don’t even have to look at the documentation. We just follow the wizard's instructions. You can also note the account manager, which is very convenient when you have a large number of systems.

Basic functions are offered free of charge, but the ability to back up a database is included only in the Advanced DB and Full versions. Installation from XP to Windows Server 2012 is supported.

Handy Backup

License: a commercial

Supported DBMS: Oracle, MySQL, IBM DB2 (7–9.5) and MS SQL Server

One of the most powerful relational database management systems is IBM DB2, which has unique scaling features and supports multiple platforms. It comes in several editions, which are built on the same base and differ functionally. DB2 database architecture allows you to manage almost all types of data: documents, XML, media files, and so on. The free DB2 Express-C is especially popular. Backup is very simple:

Db2 backup db sample

Or a snapshot using the Advanced Copy Services (ACS) feature:

Db2 backup db sample use snapshot

But we need to remember that in the case of snapshots, we cannot recover (db2 recover db) individual tables. There are also opportunities for automatic backup, and much more. The products are well documented, although manuals are rare on the Russian-language Internet. Also, not all custom solutions offer DB2 support.

For example, Handy Backup allows you to backup several types of database servers and save files to almost any media (hard drive, CD/DVD, cloud and network storage, FTP/S, WebDAV and others). Database backup is possible via ODBC (tables only). It is one of the few solutions that supports DB2 and also carries the "Ready for IBM DB2 Data Server Software" logo. The entire procedure is performed using a regular wizard, in which you only need to select the desired item and create a task. The setup process itself is so simple that even a beginner can figure it out. You can create several tasks that will run on a schedule. The result is recorded in a log and sent by email. There is no need to stop the service while the job is running. The archive is automatically compressed and encrypted, which ensures its security.

Two versions of Handy Backup support working with DB2 - Office Expert (local) and Server Network (network). Works on computers running Win8/7/Vista/XP or 2012/2008/2003. The deployment process itself is simple for any administrator.

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

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

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

Click "Ok"

The base will be restored successfully.

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

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

In the window that appears, enter:

USE master

RESTORE DATABASE Test_Recovery

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

WITH REPLACE

The base will be restored successfully.

In this example we used the "REPLACE" parameter:

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

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

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

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

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

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

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

Step 2. Change the text file extension to .cmd

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

Step 3. Automate this process

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Setting up MS SQL backup and database configuration

Restoring a MS SQL database from a backup

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

Options for restoring a MS SQL database from a backup

Bacula Enterprise Edition software allows users to use multiple MS SQL recovery options and apply a variety of database rollback methods. The most commonly used recovery options are described below:

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

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

LSN

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

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

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

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

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

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

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

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

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

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

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

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

MS SQL recovery scripts

Table 1: MS SQL recovery scenarios

2.3.1 Restoring a MS SQL database with the original name

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

Restoring a MS SQL backup with a new name

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

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

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

Using My Catalog

Run MS SQL recovery task:

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

Recover MS SQL to local disk

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