april 30, 2024
Description and configuration of backup for transactional databases on MS SQL Server
Introduction
In practice, database administrators often face the situation when it is necessary to restore a database. Such a need may arise either as a result of a server crash or as a result of a trivial error of a database developer who cleared tables with business-critical data with a careless query. To ensure that such situations are resolved with minimal losses, it is necessary to properly configure regular backups. And if in the case of data warehouses it is enough to have daily backups on hand, as data changes insignificantly during the day, then in the case of transactional databases it will not be enough.
The point is that transactional databases, unlike data warehouses, are characterised by continuous processing of a large number of transactions and constant changes in data. For such databases data loss for half a day will be very critical, and in some cases even data loss for a minute is unacceptable. In this situation, it is required to be able to recover the most relevant data that is as close as possible in time to the moment of the accident. In other words, it is necessary to implement a backup strategy that will restore the database to a certain arbitrary point in time and minimise data loss.
We, as a company that deals with a large number of corporate databases on MS SQL Server, were concerned about this issue, and, studying Microsoft documentation, as well as other sources, formed our own instructions on how to configure the backup of transactional databases and the recovery process at a certain point in time.
Configuring a backup task
Preparation of the database
To configure a backup to restore the database to a specific point in time, the database recovery model must be complete.
To find out the recovery model of your database, run the following script:
If your database recovery model is other than FULL, you must switch it to FULL using the following script:
Since the switch to the full recovery model will only take effect after the first data backup has been created, it is required to make a full RC immediately after the switch, e.g. with the following script:
Setting up a task to create an RC
For databases with a full recovery model, you must configure a regular backup job. The job must include the creation of full RCs and transaction log RCs. Otherwise, the transaction log will start to grow until it overflows the disc. One of the options for configuring the RC job is shown below.
To configure a backup job, you need to create a maintenance plan:
1. Connect to SQL Server via SQL Server Management Studio;
2. Open the maintenance plan editor (Management -> Maintenance Plans -> New Maintenance Plan);
3. Set up a subplane for daily full backups:
- Set a schedule for the subplan — every day at a certain time;
- Add a «BackUp Database Task» task from the toolbar. The task parameters are as follows:
- Add a «Maintenance Cleanup Task» to clean up the directory with full RoCs, depending on the selected storage depth of full RoCs. For example, if you want to store the last 6 backups, the task parameters are as follows:
The task should be executed only if the full backup task is successfully completed;
- Add a Maintenance Cleanup Task to clean up the Transaction Log backup directory depending on the selected Transaction Log RC storage depth. For example, if you need to restore data to an arbitrary point in time for the last 2 days, the task parameters would be as follows:
4. Set up a subplan to create a transactional log RC on an hourly basis:
- Set the schedule for the subplan — every day hourly. Depending on the business needs, you can backup the TJ more frequently, up to running the subplan every minute;
- Add a «BackUp Database Task» task from the toolbar. The task parameters are as follows:
Restoring the database from the RC at a certain point in time
Preparing for database recovery
To restore the database at a certain point in time, our solution offers to use an adapted version of the sp_DatabaseRestore procedure by Brent Ozar. In order to do this, you need to create the following objects in some existing database (master or special database designed for SQL server maintenance):
- Table [dbo].[CommandLog] for logging commands executed;
- Procedure [dbo].[CommandExecute] to execute TSQL commands with logging;
- Procedure [dbo].[DatabaseRestore] to restore the database to a specific point in time.
The script for creating the above objects is available at the link
You should also ensure that all full backups and transactional log backups are located in folders accessible to SQL Server. Backup names must match the YourDatabaseName_backup_YYYYYY_MM _DD_HHMMSS_NNNNNNNNNNNN template, as this is how the names are generated for RCs when using service plans.
Starting the sp_DatabaseRestore procedure
An example of running the [dbo].[DatabaseRestore] procedure to restore the database to a specific point in time (2023-08-13 13:49:00):
Note:
To check the correctness of the generated database recovery commands, it is recommended to run the procedure with the @Execute = ‘N’ parameter for the first time — in this case all the database recovery commands will be displayed in the console but will not be executed.
In the example below you can verify that the commands generated by the procedure are correct, hence you can run the procedure with the @Execute = ‘Y’ parameter to restore the YourDatabaseName database to the time 03.09.2023 13:49:00.
Description of some parameters of the procedure
- @Database — name of the source database;
- @RestoreDatabaseName — name for the restored database. By default it is NULL. If the parameter is equal to NULL, the name of the restored database will be the same as the source database name;
- @BackupPathFull — the full path to the directory where full backups are stored;
- @BackupPathLog — the full path to the directory where the transaction log backups are stored;
- @MoveFiles — parameter specifying whether the same address as the source database will be used for storing the restored database files (=0) or not (=1). The default is 0;
- @ContinueLogs — a parameter specifying whether only transaction log backups are restored in case the full backup has already been restored earlier with the NORECOVERY parameter (=1) or not (=0). The default is 0;
- @RunRecovery — parameter specifying whether it is necessary to perform a restore with the RECOVERY parameter for the restored database to be ready for use (if equal to 1, it adds the RESTORE DATABASE [YourDatabaseName] WITH RECOVERY command to the end of the script). The default is 0;
- @StopAt — a text parameter containing the date and time of the moment at which the database is restored. By default it is NULL, in this case the database is restored to the latest possible time moment;
- @Execute — a parameter specifying whether to execute the generated commands (=’Y’) or not (=’N’). The default is ‘Y’.
