Automating SQL Server backups on Windows: why you need it and how to do it

bak mssql, SQL Server

A backup is a copy of data taken and stored elsewhere so that you can restore it in case of a database breakdown. The most common causes of a breakdown include the following:

  • Hardware issue: The data in the database can get corrupted due to errors in the physical drives. However, it is not always physical damage to the server from the outside. Disks wear out, resulting in data read and write errors.
  • Software issue: As a rule, data is written to the database by a program, in which errors may occur and lead to data corruption after updates.
  • Human issue: A person with access might unintentionally damage the data. For example, they might delete data that was not supposed to be deleted. ​​
  • Hacker issue: If attackers infiltrate your system, then they will, first of all, encrypt your database and demand a ransom for decryption.

These risks look scary, but a recent backup will ensure that you sleep well at night.

General approaches to creating a SQL Server backup are discussed here. This article will explore ways to automate SQL Server backups:

How often should you back up your data?

At the very least, you need to back up your data every night. However, this is not very reliable.

Imagine the situation. One of the system administrators at the end of the day deleted an important table in the database. The problem surfaces only in the morning, after a night backup is done and ready. Thus, the most recent backup with an intact database will be more than 24 hours behind the current date. It’s better than nothing, but all yesterday’s transactions will be lost.

So, what can you do? Make differential or transaction log backups.

Differential backups contain only what has changed since the last full backup.

Transaction log backups contain only what has changed after the last full or transaction log backup. They can be done frequently, but to use them, the recovery model database must be equal to full.

The frequency of backups depends on the circumstances: the frequency of data changes, the acceptable data loss period, the backup creation time, the size of the storage for backups. General advice would be as follows:

Full backups should be done seldomly, once a day, or once a week.

Differential backups should be done from time to time, for example, every six hours.

Transaction Log backups should be done frequently, for example, every hour.

Back up your data the right way

There is more to a backup than a command to start it. In general, the backup process should look like this:

  1. Create backup.
  2. Compress backup. Backups must be compressed. It will save storage space, thereby allowing you to store more backups. As a rule, backups are very compression-friendly.
  3. Send it to another server or cloud. It is wrong to store backups on the same server as SQL Server itself. In case of a server breakdown, you will lose both the SQL Server and backups.
  4. Delete old backups from storage. If you do not delete old backups, the backup storage will overflow.
  5. Send notification with result details. You need to set up the process once and forget about it, but if problems arise, it is crucial that you find out about it.

.bat script to back up SQL Server database to a network folder

Automated execution of these steps can be done with a .bat script. The script will create the specified backup, compress it with 7zip, copy the archive to a network folder, and write the result of the script execution to the Windows event log.

At the beginning of the script, specify the data required to connect to the database and to the network folder, as well as the password for the archive encryption.

This script uses the 7zip utility. You can download it here.

set sql_server_user=sa 
set sql_server_password="sa-password" 
set network_folder=\\backup-server\BackupStorage 
set network_user=user 
set network_password=****** 
set encryption_password=****** 
set backup_folder=C:\Backup2 
set max_keep_days=30 
set seven_zip_path=C:\Program Files\7-Zip\ 
set sql_database_name=%1% 
set sql_backup_type=%2% 
set backup_name=%sql_database_name%-%sql_backup_type%-%DATE:~-4%-%DATE:~4,2%-%DATE:~7,2%-%time::=.% 
IF %sql_backup_type%==FULL ( 
sqlcmd -U %sql_server_user% -P %sql_server_password% -Q "BACKUP DATABASE [%sql_database_name%] TO DISK = '%backup_folder%\%backup_name%.bak'" 
) ELSE IF %sql_backup_type%==DIFF (
sqlcmd -U %sql_server_user% -P %sql_server_password% -Q "BACKUP DATABASE [%sql_database_name%] TO DISK = '%backup_folder%\%backup_name%.bak' WITH DIFFERENTAL" 
) ELSE IF %sql_backup_type%==LOG ( 
sqlcmd -U %sql_server_user% -P %sql_server_password% -Q "BACKUP LOG [%sql_database_name%] TO DISK = '%backup_folder%\%backup_name%.bak'" 
) ELSE (
echo Unknown backup type %sql_backup_type% 
)
if %ERRORLEVEL% neq 0 eventcreate /ID 1 /L APPLICATION /T ERROR /SO mysql-backup-script /D "Backup failed: error during dump creation" 
if %ERRORLEVEL% neq 0 eventcreate /ID 1 /L APPLICATION /T ERROR /SO mysql-backup-script /D "Backup failed: error during dump creation" "%seven_zip_path%7z" a -p%encryption_password% %backup_folder%\%backup_name%.zip %backup_folder%\%backup_name%.bak 
if %ERRORLEVEL% neq 0 eventcreate /ID 1 /L APPLICATIsON /T ERROR /SO mysql-backup-script /D " Backup failed: error during archive creation" 
del %backup_folder%\%backup_name%.bak 
net use N: %network_folder% /u:%network_user% %network_password% 
mkdir N:\Backups 
move %backup_folder%\%backup_name%.zip N:\Backups\%backup_name%.zip 
if %ERRORLEVEL% neq 0 eventcreate /ID 1 /L APPLICATION /T ERROR /SO mysql-backup-script /D "Backup failed: error during file transfer to storage" 
ForFiles /p "N:\Backups" /s /d -01 /c "cmd /c del @path & echo Deleted old: @path" 
net use N: /delete 
del %backup_name%.zip 

eventcreate /ID 1 /L APPLICATION /T INFORMATION /SO mysql-backup-script /D "Backup successful"

Errors recorded in the Windows event log are displayed in the Server Management Console. If something goes wrong, you will see a red warning.

This script takes two arguments as a parameter
1. Database name
2. Backup type (Full, Diff, LOG)

To run a full backup of the AdventureWorks database once, do the following:

sql_server_backup.bat AdventureWorks FULL

And to do DIFF backup, run the following:

sql_server_backup.bat AdventureWorks DIFF

How do you automate it?

The easiest way to automate a .bat script in Windows is to use the Windows task scheduler. For example, let’s create two tasks: create full backups every midnight and create LOG backups every hour.

The daily full backup task

  1. Press Win + R and enter taskschd.msc
  2. Click Create Basic Task in the right panel
  3. Specify the task name, it will be displayed in the list of tasks
  4. Select Daily
  5. Set a time to start the task (for a full backup, as a rule, night hours are better)
  6. Select Start a Program
  7. Specify the path to the script and launch parameters (database name and backup type AdventureWorks FULL)

To test, right-click on the task and select “Run.”

An hourly transaction log backup task

Everything is the same here, but at the penultimate step of the wizard, specify in the parameters

AdventureWorks Log

and at the last step of the wizard, check the box “Open the Properties dialog for this task when I click Finish.”

Then go to the Triggers section, select the Daily trigger and click Edit.

In the window that appears, specify that you want to repeat the task every 1 hour.

Don’t forget to test it immediately after saving.

How do you restore the data?

To restore data from a full backup, just extract the .bak file from the archive and run the following command:

RESTORE DATABASE [AdventureWorks] FROM DISK = 'c:\Backup\AdventureWorks-FULL-2021-08-12-14.28.17.20.bak'

To restore the log backup, you need the previous full backup, as well as all the LOG backups that were between the full backups, and restore the LOG backup.

Unzip them locally. Thereafter, you need to run the SQL command, which will list all the backup files necessary for recovery:

RESTORE DATABASE [AdventureWorks] FROM DISK = N'D:\Backups\AdventureWorks-FULL-2021-08-12-14.28.17.20.bak' WITH REPLACE, NORECOVERY 
RESTORE DATABASE [AdventureWorks] FROM DISK = N'D:\Backups\AdventureWorks-LOG-2021-08-12-14.28.17.20.bak' WITH NORECOVERY 
RESTORE DATABASE [AdventureWorks] FROM DISK = N'D:\Backups\AdventureWorks-LOG-2021-08-12-14.28.17.20.bak' WITH RECOVERY

SqlBak

SqlBak is a service for automated backups of databases and sending them to the cloud storage. Using it, you can create a “backup job” that will create SQL Server database backup, then compress, encrypt and send it to cloud storage. It will also delete old backups from storage and if something goes wrong, the service will send you a notification by email. A distinctive feature of this service is that it can be used to centrally manage backups on several servers simultaneously.

SqlBak works on both Windows and Linux. With it, you can automate backups not only of SQL Server but also of other mainstream databases: MySQL, PostgreSQL, and Mongo DB.

To start you have to install a tiny tool on your server and set a connection to your DBMS, you can download the latest release of SqlBak and find more details on how to set a connection on the “Download” page.

Now you have to create a backup job. To do it go to the “Dashboard” page, click on the “Add new job” button and create a new job.

At the opened window go to the “Select databases” section and choose databases that should be backed up.

Now you need to choose places where the backups should be stored. To do this, go to the “Store backups in destinations” section and click on the “Add destination…” button.

Choose a destination from the list. Assume you prefer to send the backups to your Google drive.

At the opened windows click on the “Authorize” button, log in to your Google Drive and allow SqlBak to connect your account.

After the connection, you can specify a folder where the backups will be stored. Click on the “Test” button to check the settings and “Save & Close” to save them. Note, multiple destinations can be used to store the backups.

To schedule your backup job enable the “Schedule backups” option and set it as you need. You can find more settings by clicking “Advanced settings.”

To receive the notification if the backups failed or were successful specify your email address into the appropriate boxes at the “Send email confirmation” section. Note, multiply emails can be specified, separated by a comma.

To save your backup job and run it according to the schedule click on the “Save & Exit” button at the right upper corner of the page.

Now all your backups will be run according to the schedule and saved to your Google Drive. Also, you can set Compression and Encryption for your backups, add custom scripts before or after the backups, and many more.

SQL Server Agent

This is a SQL Server component that is designed to automate maintenance tasks.

By default, SQL Server Agent is not enabled. To enable it, do the following:

  1. win + r -> services.msc
  2. find SQL Server Agent (MSSQLSERVER)
  3. r-click -> Properties
  4. Startup type: Automatic
  5. Click Start and Ok

Sql Server Agent allows you to create tasks to run SQL scripts, schedule them, and send email notifications. For regular backup, we will create a task that will create a compressed backup right in the network storage.

  1. Create a task.
  2. Name it.
  3. Go to Steps and click New.
  4. Select Transact SQL script and paste the following script and click “OK.”
    To back up your data directly into the network folder, it is necessary that the user who runs the SQL Server has access to it. 
DECLARE @fileName nvarchar(255) 
DECLARE @dbname nvarchar(255) = 'AdventureWorks' 
DECLARE @backupFolder nvarchar(255) = '\\test-network-fo\BackupStorage\Backups' 
SELECT @fileName = @backupFolder + @dbname + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(25), GETDATE(), 120), '-', ''),':',''), ' ','') + '.bak' 
BACKUP DATABASE @dbname TO DISK=@fileName WITH COMPRESSION

  • On the Schedules tab, click “New…” and set up daily backup.
  • If your database is set up to send emails, then specify the address on the Notification tab. If not, then you can get by with a message in the Windows event log.

 



The task for creating full backups is ready. Now create a task to regularly run transaction log backups the same way you set up the previous task, only change the script to this:


DECLARE @fileName nvarchar(255) 
DECLARE @dbname nvarchar(255) = 'AdventureWorks' 
DECLARE @backupFolder nvarchar(255) = 'C:\Backup2\' 
SELECT @fileName = @backupFolder + @dbname + '-LOG-' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(25), GETDATE(), 120), '-', ''),':',''), ' ','') + '.bak' 
BACKUP LOG @dbname TO DISK=@fileName WITH COMPRESSION


And set up an hourly run in schedule settings.




Run these tasks to make sure everything is configured correctly.


Restoration


You can restore the backups created this way using SQL statements, as described above in the first method, or through SSMS, by selecting the desired backup on the time interval.


Right-click the database, Tasks >Restore >Database…




Check the backups that should be restored.




 


If you want to restore the database to an older date, click Timeline and specify the date on which you want to restore the database. SSMS will select a suitable backup for you.






SQLBackupAndFTP


It is a popular utility for automated backups of SQL Server databases. Initially, it was created to transfer a SQL Server backup to FTP, however, over time, the features, and the list of destinations where you can transfer the backup has significantly expanded: network folder, AWS S3, Azure, Google Drive, OneDrive, DropBox, etc.


SQLBackupAndFTP combines the ease of customization and flexibility. You can schedule regular backups, compression, encryption, and transfer of a file to the cloud in just a few minutes. At the same time, a higher number of additional settings, as well as embedding custom .bat or .sql scripts into the process, allows you to use the application with virtually any server configuration.



	


	
	

		
			



						
						



	
		

		
Leave a Reply 





Your email address will not be published. Required fields are marked *