Log shipping in Microsoft SQL Server is an automated solution for master-slave replication based on the transfer of transaction logs from primary to secondary servers. This process ensures continuous database cloning while minimizing the load on the primary server.
To perform a SQL Server backup is very simple — it can be done in 5 clicks via SSMS. But the topic of backups in SQL Server is deep.
Take a seat, young Padawan, for this article will be your first step toward becoming a Jedi Backup Master.
Creating backups of a SQL Server database is one of the most critical aspects of maintaining a system. There are various tools available for creating backups, such as SQL Server Management Studio, SqlBak, and SQLBackupAndFTP. However, performing backups via the command line interface can provide even more flexibility and customization options.
PowerShell and batch scripts can be used to perform SQL Server backups via the command line interface. PowerShell scripts offer advanced functionality, while batch scripts are simpler and easier to use. Command line backups provide more flexibility and customization options than GUI-based tools.
This article provides an overview of the process involved in performing SQL Server backups via the command line interface and discusses the advantages of using PowerShell and batch scripts.
If a database that should be backed up via SqlBak is not particularly large, then there is no need to set compression options in a specific way. It is recommended to use the default values.
The features below are designed to fine-tune compression for highly loaded systems and large databases.
Differential and transaction log backups are available only for local SQL Server connections. If you backup SQL Server using SqlBak, then you can add differential or transaction log backups to your backup plan in the “Advanced backup schedule” settings.
In one database management system, there can be multiple databases. When creating a backup job, you can choose which databases specifically need to be backed up. The backup of each database will be made independently from the other databases and placed in a separate archive.
SqlBak supports sending to 16 different storage types. In one job, you can specify multiple backup storage locations, and for each storage location, you can specify the duration of backup storage on it.
SQL Server backup is a page-by-page physical copy of the database files. And, generally, the size of a backup is about the size of the database. However, there are several ways to cut the backup size substantially.
Reducing the backup size has the following benefits:
The BACKUP DATABASE command syntax allows performing backups by specifying a drive, a network folder or Azure storage as the destination. Although it is not possible to set an Amazon S3 bucket as a backup save point, you can transfer the file to S3 using a batch script or a third-party utility.
With the help of SqlBak, you can backup your remote SQL Server databases according to your schedule. A standard BACKUP DATABASE command doesn’t work on a remote SQL Server — only scripts can be generated. If there is a choice between script generation or performing backups in *.bak files, it is always recommended to run the standard BACKUP DATABASE command to get *.bak files. Scripts have some drawbacks: they are larger and do not support differential and transaction log backups. A more detailed explanation about remote backups can be found in the following blog post.
