In general, backup means creating a copy of data that can be stored on a separate device. In this publication, we will describe what data backup is and how it is configured using SQL Server DBMS as an example.
What data backup gives you:
● A high-quality SQL database backup allows you to quickly restore data after failures due to server hardware failure, server hacking, human error, etc.
● Backups are often used routinely to correctly transfer data from one server to another.
What data backup gives you:
● A high-quality SQL database backup allows you to quickly restore data after failures due to server hardware failure, server hacking, human error, etc.
● Backups are often used routinely to correctly transfer data from one server to another.
Data upload
To load and save data in text or binary format in SQL Server, the BCP service program is used. Download is used to create backups of small tables, and if we are talking about massive databases, copying the database files is used, which will be discussed below.
How to backup MS SQL Server Database?
There are several tools for creating backups of SQL Server files. Although they are relatively simple, to ensure the integrity of the backup it is better to contact professionals, for example - DB Serv engineers, who will help with the administration and support of this DBMS. However, a general idea of how databases are backed up in SQL Server will not hurt anyone, so let's take a closer look at the main methods.
MS SQL Management Studio
You can configure the backup in the SSMS GUI. Start MS SQL Management Studio, select the required database, and right-click to open the menu, in which go to the path "Tasks" => "Create backup". In the window that appears, specify the required backup type and its path. After the backup is successfully created, you will see a corresponding notification.
The sqlcmd utility
The second method is the console utility sqlcmd, which works both in Windows and Linux. It allows you to automate the process of backing up data using the scheduler, in which you need to create a job to run according to the schedule command sqlcmd -S <server> -U <user> -P <password> -Q "BACKUP DATABASE [<database>] TO DISK = N'<file path>' <options>".
PowerShell
This method of creating backups is relevant for later versions of SQL Server, but it is recommended by professionals. To create a database backup, import import-module sqlps -DisableNameChecking. The syntax here is as follows: Backup-SqlDatabase -ServerInstance <SQL server name> -Database <base name> -BackupFile <backup file path>. Similarly, sqlcmd script can be run on a schedule by adding it to the scheduler.
Data backup methods
SQL Server offers several types of database backups. Let's say a few words about the main ones.
Full data copying
Full Backup in SQL Server means that the backup includes all data and objects of system tables, as well as the transaction log. This type of backup is considered the main one because you can deploy the database from it in a few clicks without using other copies (differential or incremental). It can be created using SSMS, Transact-SQL, and PowerShell:
● Server Management Studio. When creating a task, create a T-SQL BACKUP script by clicking the Script button and specifying its purpose.
● Transact-SQL. Execute the BACKUP DATABASE instruction, remembering to specify the name of the database backup and its desired location.
● PowerShell. Execute the Backup-SqlDatabase cmdlet, specifying the BackupAction parameter along with Database.
Selective data copying
Selective database backup is made based on a full backup. If the selected database has not been archived yet, create a full backup before creating a selective backup.
Transaction log backup (if the full recovery model is used)
The backup contains all transactions since the previous backup, after which the transaction log is released to capture subsequent transactions. Only the availability of transaction log backups will allow recovery at any point in time.
Other backup methods
There are other backup methods.
Tail-Log. Executed with the NORECOVERY option between backing up the transaction log and restoring it from the backup, so that transactions that occurred during this time interval are not lost.
Copy-only backup. A SQL Server backup that does not depend on a sequence of regular SQL Server backups.
Partial backup. A variant of partial backup, read-only file groups.
How archiving differs from backing up information
Backups are often confused with archiving, but they are two different ways of protecting data.
Backup is used to restore data as quickly as possible in case of data loss.
Archiving is to keep unused data as long and secure as possible - i.e. cold storage. The priority is not the speed of access to data, but its preservation, saving disc space and easy navigation.
The difference in priorities leads to the fact that it is better to use different media for backup and archiving. Backups are better stored in a NAS, while archives can be placed on external hard drives.
Common mistakes
There are several common mistakes made when backing up databases. Let us list the things you should not do if you want to keep your data intact.
➡️ Do not delete old database backups from the server before creating a new copy.
➡️ After backing up the database, verify that it is correct
➡️ Make sure that there is enough space on the server for the backup and the time required to create it.
➡️ When restoring from a backup, do not overwrite the existing database (unless explicitly necessary).
➡️ Do not store databases and their backups on the same server, much less on the same disc.
➡️ Do not confuse data backup with replication or any other fault-tolerant technology: these methods should complement, not replace, each other.
