As with any technology and associated files, it is of paramount importance to back data up regularly for information one wants to preserve. This holds true for databases as well, obviously. With SQL Server, backups can be done manually or through a maintenance plan. Since I am running SQL Server 2017 Express, I do not have access to the maintenance plans (that is just an example of a restriction for the Express edition; there are many others).

Nonetheless, you can use maintenance plans to schedule automated backups of your MS SQL database and its transaction logs. You can do FULL and DIFFERENTIAL backups. Here's the difference if you are not sure:

  1. FULL Backup: the entire database and all of its information is backed up regardless as to any previous backups' contents.
  2. DIFFERENTIAL Backup: only data that has changed from the last FULL backup is included in this backup set.

Let's explore how to do this in the GUI. You'll need to have SSMS (SQL Server Management Studio) open to proceed. Note that almost anything that can be done in the GUI, can be done in Transact-SQL as well, but for simplicity, I will use the GUI where possible.

First, right-click on the database you wish to back up. Navigate to "Tasks" then to "Back Up..."

BackupDB1

Click on that option and you'll be presented with a dialog window. There are many options in this window and you'll want to especially consider where the backup is going to be stored. Ideally, you'll want this backup on another logical volume on another physical disk in a SAN, NAS, or external drive. Keeping your data backups on the same drive and where live data is stored is extremely frowned upon, risky, and nearly oxymoronic when describing it as a backup. Regardless as to how long you plan on keeping backups on the same disk, they need to be rapidly offloaded to hot or warm spare storage or even cold (offline) storage.

For now, we'll assume you are taking the appropriate precautions and at least have either a secondary disk on the machine or external drive attached to the machine for your backup purposes.

Let's take a look at the dialog window in the following screenshot and see if you notice an issue:

BackupDB2

The issue is this:

BackupDB3

The back up has defaulted to the same disk. Highlight that path and click "Remove" and then click "Add..." Then navigate to a separate disk that is accessible by the server (or whatever machine is hosting the DB) and select a path for the backup.

BackupDB4

Now you can see we have a different path and thus a better option for storing our backup. We may still want to consider offloading this data elsewhere as part of your backup strategy, but that is entirely up to you and your organization.

BackupDB5

There are other options and attributes that can be adjusted for your backup, such as backup expiration (how long your backup copy is prohibited from being overwritten; NOTE: this does NOT delete backups older than the expiration policy), verifying the backup or performing a checksum before writing to media (both may be a good idea albeit at a potential performance impact when considering backup times), et cetera.

Microsoft has a wealth of knowledge about backups and even maintenance plans, which I am not covering at the moment in this series since all usage is through SQL Server Express 2017.

I do want to cover one more thing though: transaction logs.

Transaction logs come in a few "flavors". There are FULL, BULK-LOGGED, and SIMPLE recovery logs. During your backups, if you notice there is not an option to back up your transaction log, you are likely in SIMPLE recovery mode. Change your recovery mode to FULL or BULK-LOGGED and you will get an option to back those up. Keep in mind that transaction logs can get very big when not in SIMPLE recovery and should be backed up and truncated frequently.

BackupDB6

The general idea of transaction logs is to provide continuous logging of changes in the database that allow you to restore from these logs in case of failure. This is helpful if your last FULL or DIFFERENTIAL backup was done hours (or more) ago and you need to capture as much data since then as possible. SIMPLE provides no restoration capabilities whereas FULL and BULK-LOGGED can.

More information on transaction logs can be found here.

There are tons of articles and bits of information out there regarding concepts from transaction log shipping, to replication, to high availability and disaster recovery, et cetera. So if you are still looking for more, Microsoft has some outstanding explanatory articles on these subjects that can be complemented by information from other sources as well.

In essence, make sure that you keep your backups up-to-date and in a secure location! The database is arguably the most important piece of your data to keep safe as it stores the bulk of your organization's information, so don't skimp on your backup policies for production database servers!