Automate MS SQL backup configuration using .NET application

I matured a long time to write this article and post my application. I hope you will be interested.

What is this article about

It describes the way in which, using the .NET application I developed, you can distribute a backup plan and carry out all the necessary settings over the database using DBMS tools, notifying the administrator of the completion of tasks.



To the maximum I will try to describe the nuances that I had to face during the development of the application and database configuration.

For the tasks described below, you can use the master maintenance plans , but I liked this approach more. The main advantage of the method I described is that this method can be applied to all versions of MS SQL (except Express, there is a slightly different approach). The maintenance plan can be migrated , but you must have the corresponding MS SQL version and a Job will still be created to launch the maintenance plan.

')

Compare MS SQL editions and their functionality here .

Beware : perfectionists may experience burning and pain when viewing the code of my application, since it was written in haste and sharpened for a specific task.



To whom this article is suitable:

• Those who have MS SQL Express and can’t run tasks with Job
• Those who are planning to switch from MS SQL 2008 to a newer version in the near future and do not want to set up database mirroring, but set up AlwaysOn on the new version right away
• Those who do not have the means to raise more backup servers and have to do with what they have.
• Who has no tight deadlines at the time of recovery of the database. The main thing is the result
• Who is too lazy to do something
• Just curious people.

Table of contents:



Backup Theory

1. Transaction log
2. Differential copy of the database
3. System databases
4. Backup plan
5. General backup guidelines

We use the application

1. Admin Notification Setup
2. Additional notifications for admin
3. Troubleshooting DatabaseMail Settings
4. Configuring backup using SQL Standart application
5. Configuring backup using SQL Express application
6. Deleting tasks from the database
7. Deleting database copies

How to restore backups





The list of articles of habr which I used

1. Creating and storing backup copies of databases in MS SQL. Practical advice
2. Building a recovery chain for MS SQL databases
3. Setting up Database Mail in MS SQL Server 2005 and later
4. SQL Server 2008: back it up wisely. Part 1: Theory
5. All that you were shy to ask about backups of Microsoft SQL Server

Sources on github for MS SQL Standart and for MS SQL Express

If you want to add your thoughts to the code, I accept the pull request. Ready to listen to constructive criticism and finalize the application, if it is really necessary for someone.

Backup Theory

All that is described in theory, you can find yourself. Configurations that are described in this section will be automatically performed by my application when setting up a backup.

MS SQL Server supports 3 backup models.

1. Simple
2. Full recovery model
3. The incomplete full recovery model

I chose a full recovery model for the application, since I needed to be able to always restore the latest version of the database after any operation and I did not have single-step bulk data insertion operations. If you're just starting out and don't know how to choose the right one, this Microsoft article can help you.

To enable this mode, you must run the following script

ALTER DATABASE [  ] SET RECOVERY FULL; 

When switching the recovery model to full, we will have the following features:

1. The DBMS will stop automatically clearing the transaction log . The log will grow until a backup copy is made. This is an important point; the DBA needs to consider the issue of a backup plan and a log cleanup plan. UPD: thanks for the help Yggaz
2. Creating a differential backup
3. Create full backup

Below will be described some of the nuances associated with the backup transaction logs and differential copies. I have no comments on the full copy, just do it periodically and everything will be fine with you

1. Transaction log

The transaction log is a critical component of the database, and in the event of a system failure, it may be necessary to bring the database into a consistent state.



Advantages when restoring a database using the transaction log:

1. recovery of individual transactions;
2. restore all pending transactions when SQL Server starts;
3. roll forward of the restored database, file, filegroup or page until the crash, etc.

Recommendations

1. Move to a fast hard drive so that with a large workflow there are no delays during the recording.
2. You need to make backups of the transaction log at least every hour.
3. After creating a full (differential) copy of the database, all old logs can be deleted, because they lose their relevance.
4. Carefully monitor the size of the disk on which transaction logs are stored, if it runs out, it will be impossible to write new data to the database until the size of the transaction log is reduced or a new additional transaction file is added.
5. The transaction log should be regularly truncated to avoid overflowing it. UPD: As kolu4iy said , this truncation operation is slightly questionable in terms of performance, since When backing up, the transaction log is cleared inside and the DBMS starts writing a new one in it. However, you may have a situation that I described in my commentary and then this may be useful to you.
6. A situation is possible when it is impossible to immediately truncate the log. They are described in this article.
7. To obtain information about the state of the database, use the following query:
   
   

    select name,log_reuse_wait, log_reuse_wait_desc from sys.databases 

   
   
   
8. If necessary, you can get information about the latest open transactions.

    DBCC OPENTRAN (  ) WITH TABLERESULTS 

Sample SQL script to perform a transaction log backup and then truncate the file.

 BACKUP LOG [  ] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL\MSSQL\Backup\[ ].bak' WITH NOFORMAT, NOINIT, NAME = N'   ', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO USE [  ] GO DBCC SHRINKFILE (N'   ' , 25) GO 

The same operations can be done using SSMS

2.Raznostnaya copy of the database

Differential backups are based on the most recent previous full backup of data. Only changes that have been made since the last full backup were saved in the differential backup.

Recommendations:

1. Use differential database copies if creating a full database copy takes a long time
2. Periodically make a full copy of the database to reduce the amount of differential copies created.
3. After creating a complete copy of the database, all previous differential copies lose their relevance.

More details about the frequency recommendations for creating differential backups can be found here .



I will give a small example from practice, why we began to use a differential copy. Over time, our client has grown the database to such an extent that creating a full backup took 8 hours, a few more months, and perhaps by the beginning of the working day it would not have time to complete this operation. After transferring to a differential backup, we reduced the time from 8 hours to 2-4 minutes (depending on the day of the week). Once a week we made a complete copy of the database.



Sample SQL to create a backup differential database with a copy checked upon completion (different from a full copy with the DIFFERENTIAL flag, use NOFORMAT instead).

 declare @pathBackup as varchar(55) set @pathBackup = N'C:\Backup\[  ]_' + REPLACE(convert(varchar,GETDATE(), 104),'.','_') + '.bak' BACKUP DATABASE [  ] TO DISK = @pathBackup WITH DIFFERENTIAL, NOFORMAT, INIT, NAME = N'    ', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM GO declare @backupSetId as int declare @pathBackup as varchar(55) set @pathBackup = N'C:\Backup\[  ]_' + REPLACE(convert(varchar,GETDATE(), 104),'.','_') + '.bak' select @backupSetId = position from msdb..backupset where database_name=N'[  ]' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'[  ]') if @backupSetId is null begin raiserror(N' .        "[  ]"  .', 16, 1) end RESTORE VERIFYONLY FROM DISK = @pathBackup WITH FILE = @backupSetId, NOUNLOAD, NOREWIND GO 

3.System databases

In addition to the main database and its associated files, I strongly recommend making copies and system databases. Let's start with the fact that we consider which databases exist in MS SQL. There are only 5 of them:

Title	Description
Master database	This database stores all system-level data for an instance of SQL Server.
Msdb database	Used by SQL Server Agent to schedule alerts and tasks.
Model database	Used as a template for all databases created in an instance of SQL Server. Changing the size, collation, recovery model and other parameters of the model database leads to a change in the corresponding parameters of all databases created after the change.
Resource database	Database read only. Contains system objects that are included with SQL Server. System objects are physically stored in the Resource database, but are logically displayed in the sys schema of any database.
Tempdb database	Workspace for temporary objects or interaction of result sets.

You can read more about them here and here .



I chose to reserve only 2 system databases:

1. msdb - because, there are stored customized tasks and other
2. master - all SQL Server settings are stored.

This information is still not very critical and can be restored by hand, but why waste time when you can simply take it from a backup.

4. Backup plan

Based on the above, we will create our backup data backup plan. It may differ from what you need, it all depends on the requirements for restoring the database. When I was preparing a plan, I had to consider that it was necessary to restore the data as much as possible and the loss of data was no more than one hour.



We will make the following backups:

• A full copy of the main database, more than once a week is not necessary
• Difference copy of the main database every day
• Copies of the transaction log of the main database, every hour
• A copy of the master database system, once a week
• A copy of the system database msdb, once a week

As a result, we have the following backup data plan:

Day of the week	Time	Actions	Frequency	Description
Monday Friday	From 8-00 to 21-00	Backups Transaction Log	Every hour	After performing a backup copy of the database, the transaction log is compressed and truncated
Saturday Sunday	From 8-00 to 18-00
Monday Sunday	22-00	Differential copy of the main database	1 per day	After successful execution of a differential copy, all old copies of the transaction log are deleted.
Saturday	12-00	DB check	1 per day	DB check Case for integrity.
Saturday	18-00	Creating a complete copy of the database	1 per day	Upon completion of this operation is notified by mail. If the backup is successful, it is deleted. old full backup all old differential copies all old transaction logs
Monday Sunday	23-30	Create a copy of the system master database	1 per day	Only the latest copy of the database is always stored.
Sunday	12-30	Create a copy of the msdb system database	1 time per month	Only the latest copy of the database is always stored.

5. General backup guidelines

1. Use the option

    BACKUP WITH CHECKSUM 

   
   
   to make sure everything went well. The disadvantage of this solution is that for large databases, checksum verification can seriously load the system.
2. Do not back up files to the same physical disk on which the database or transaction protocol is stored.
3. If you use MS SQL 2008 or higher, I recommend that you use backup compression using SQL tools. The following code will enable default compression:

    USE master; GO EXEC sp_configure 'backup compression default', '1'; RECONFIGURE WITH OVERRIDE; 

4. keep backups for several days in case one of them gets corrupted - an old backup is better than none.
5. Use DBCC CHECKDB to check each database before copying, this will prompt you in the time of impending problems.

    DBCC CHECKDB ('  ') WITH NO_INFOMSGS, ALL_ERRORMSGS; 

   Note: for practice, we used this check only before performing a full backup.
6. Periodically update statistics and reorganize database indexes

We use the application

Several nuances on the application:

• All texts and requests in the code are placed in the resources, it was easier for me
• When entering connection parameters and other settings, they are saved to a file. For Express and Standart, different files (dbStandart, udExpress) are used. They contain the class UserData
• Some operations may require administrator privileges.
• At the moment, the connection to the database under the domain account does not work
• The program does not have a super-beautiful interface.

1. Setting up administrator notification

I was too lazy to go to the server every time and check if the task worked or if some kind of error occurred. Yes, and I wanted to be able to receive other notifications, not only about the completion of tasks.



For this purpose, DatabaseMail MS SQL is used (for the Standard version and higher)

In my application, I made a special section to automate this task.







When clicked, a form will appear to fill in the information necessary to create a mailing list:







The application is automatically configured to the standard 25 SMTP port for the address from which the letters are sent. If necessary, it can be changed in the sysmail_add_account_sp procedure

User and password are required in case the mail service has authentication configured.



The name of the operator in the system is indicated in order for us to properly create a DatabaseMail profile. Write any name that will be clear to you. Below is an example of a completed form.







Further, from this mailbox from the specified operator, we will receive notifications about the successful execution of operations.

Actions performed at this stage:

1. Change the system parameters MS SQL.
2. DatabaseMail Profile is created
3. Activated in SQL Agente profile
4. DatabaseMail Account is created
5. DatabaseMail Account is added to Database Mail Profile
6. DatabaseMail Operator is created

Described in more detail in the next article and, in part, I took from here . Naturally, these actions can be performed using SSMS .

2. Additional notifications for admin

The program has 2 tasks applied to the database:

1. check the integrity of the database. The standard DBCC CHECKDB procedure was used to check the database.
2. informing about free space in file groups.
3. The second task was implemented using a query to the dbo.sysfiles system table .
4. Here is an example of this request, which was executed to the database:

 Select NAME = left(a.NAME,15), a.FILEID, [FILE_SIZE_MB] = convert(decimal(12,2),round(a.size/128.000,2)), [SPACE_USED_MB] = convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)), [FREE_SPACE_MB] = convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) , FILENAME = a.FILENAME From dbo.sysfiles a 

The answer from the server comes to the administrator's mail in the form of html markup. This syntax is possible due to the following standard MS SQL FOR XML function.



Just as I was looking for how to convert the result of query execution into html text into a returned one, I came across the next page where a person created a whole procedure for this purpose.

These operations can be configured using the corresponding item in the program menu:







A window appears to specify the mailbox to which you want to send html report text:

3. Solving problems when setting up DatabaseMail

In MS SQL 2008, I encountered a problem when setting up a SQL Server Agent. Symptoms are the following; after tuning, it is impossible to start the SQL Agent. Basically, this is solved by installing the update on the SQL server.



Make sure that sp1 is installed, and then you can already install the update .



If these updates do not help, you need to download fix. It can be found on this site I can’t specify the final link now, in order to get to the fix package, I’ll need to answer a number of questions.

If there are problems with the DatabaseMail module. After configuring this module using the application, you need to go to the SQL Agent and view the event log. If there are errors "it is impossible to connect to the mailbox". So there is a problem, even if an email is sent through the check.



This is corrected by the following manipulations:

1. Management Studio - SQL Server Agent - Properties.
2. Alert system
3. Uncheck the box with Enable mail profile
4. Click OK
5. Log in again and check the box.
6. Reboot the SQL Server Agent.

Check account for SQL Agent service. If this is a domain account, change it to the system account or vice versa. Everything has to earn.

4. We configure backup using the SQL Standart application:

Select the version of Standart. Customize notifications. (see section, notification settings):







We connect to the database by filling in the data for the connection and specify the database for which the Job will be applied:







Choose a backup setting:







Specify the path to save copies of the database. If the specified folders do not exist, the program will try to create them (we need the appropriate rights).







We click to save and the corresponding tasks are configured. It is advisable to configure different folders for each backup, because deleting will delete all files with bak extension. (see section deleting database copies )

5. We configure backup using the application for SQL Express:

Since SQL Express is missing the SQL Agent, the task of automating backup had to be solved in another way. In the folder specified by the user, a bat file is created in which the SQL query is described, which is responsible for creating a backup. If necessary, you can edit it directly. In addition to this, the standard Windows scheduler should work, it creates a task that will run once a day at a specified time.



To do this, run the application. Choose MS SQL Express:







A form for filling the parameters appears:







Specify where our copy will be saved, and also where the bat file will be located to create a copy of the database (you do not need to specify the file name, it will be set automatically). Next, specify the connection settings and the time when you need to run the task.



The only disadvantage of this approach is that you have to store the password for connecting to the database in clear text.

6. Removal of tasks from the database.

If you need to delete all tasks from the database (for example, you wanted to change the paths to save the database). To do this, use the appropriate item in the program menu. All tasks with a certain initial prefix (in my case King) will be deleted from the SQL Agent:

7. Deleting database copies

In some tasks, the deletion of old copies of the database is configured. For this, I use the procedure master.dbo.xp_delete_file. Example of use: Removes all files with the bak extension from the specified folder, the creation date of which exceeds 14 days.

 EXECUTE master.dbo.xp_delete_file 0,"E:\backups",N'bak',dateadd(d,-14,getdate()),0; 

And here is another more detailed example and information about what parameters this function takes.

How to restore backups

Due to lack of time, the recovery module has not yet been implemented, maybe in the future I will add it, but for now just briefly describe how you can restore the database.



Using SQL script. To restore the database, use the RESTORE command.



If you just need to restore the database from a full copy, then just run the following script:

 RESTORE DATABASE [  ] FROM DISK = 'Z:\SQLServerBackups\back.bak' WITH REPLACE 

If it is necessary to restore sequentially, first a full copy, differential copies and transaction logs, then you need to write the following SQL script.

 RESTORE DATABASE TEST_DB –   FROM test_db_full WITH NORECOVERY; GO RESTORE DATABASE TEST_DB –   FROM test_db_diff WITH FILE = 1, NORECOVERY; GO RESTORE LOG TEST_DB –   №1 FROM test_db_tran_1 WITH FILE = 1, WITH NORECOVERY; GO RESTORE LOG TEST_DB –   №2 FROM test_db_tran_2 WITH FILE = 1, WITH NORECOVERY; GO RESTORE DATABASE TEST_DB WITH RECOVERY; GO 

You can also use SSMS to restore the database.