Every DBA, even a beginner, may walk through the SQL Server backup screen multiple times per day. It is mandatory that you know every single detail of every single option you have in the most repeatable task you could do as a DBA.
In this article, I will be discussing every option available in full backup screen of SQL Server 2016.
Database files
When we talk about backing up SQL Server, we are talking about backing up the physical files that make up the data obviously.
Database backups traditionally have backed up two types of files, the MDF (main database file) and the NDF (secondary database file). You always have one MDF and 0 or more NDFs, depending on how you design your database. Full backups back up at least those two.
There is another file type LDF which is the log information file and it is backed up when you perform a transaction log backup.
Basic full backup
To perform a simple full backup, we can open SQL Server Management Studio and right-click on the database we want to back up, then choose tasks then click back up.
You will end up on the following screen:
And this screen will be our focus, next.
Back up database screen
In this screen we can see three pages (tabs):
General page
It is separated into two parts: source and destination.
In the source part you can see the following options:
Database: a combo box to select the database you want to back up. Since we have right clicked on SQL_SHACK database, it is shown by default but we can select any other database we want.
- Recovery model: actually, this cannot be changed. This is an indicator of what is available to be backed up. Only databases in full and bulk-logged recovery model can be backed up using transaction log backup type. As we are talking here about only full back up, so in all recovery models, a full backup can be done.
- Backup type:
Here you can choose the backup type you want:
- Full – backups everything in the database.
- Differential – backups only what is changed from last full backup.
- Transaction log – backups transaction file
- Copy-only backup:
This option is very important and it is not very clear to many DBAs.
Let me explain it in more details using one scenario:
Considering you have a backup maintenance plan that takes backups on the following schedule:
- Full back up – every Friday at 11:59 PM
- Differential backup – every day at 11:00 PM
- Transactional log backup – every hour
Next let’s assume, that you have a request to restore this database on another server, for example.
It is a simple task; just a basic backup and copy the file then restore it to the target server. But you must be aware that you have a backup sequence, which means that every differential back and transactional log backup is depending on the last full backup taken. And by doing that simple task you broke the chain of backups because all of the differential and transactional log backups now will depend on that simple backup you take. So for any reason, if this backup is lost and you faced any type of disaster before the next backup, you are in trouble.
Now, you will not be able to restore this database to meet you point in time objective as you only could restore the database to the time before that simple backup you had taken. And here comes the benefit of Copy-only backup option which enables you to take that simple backup anytime you need without affecting the backup sequence.
- Backup component: this option allows you to check if you want to backup the entire database or to choose specific files or filegroups to backup.
There is a very important thing to note here, though. You cannot backup files or filegroups for a database in simple recovery model.
In full or bulk-logged recovery model, you can choose any file or filegroup you want to backup.
And Microsoft has stated the reason for that as “read/write files must all be backed up together. This helps make sure that the database can be restored to a consistent point in time.” For more details check this article.
In the destination part, you can see you will define where you want to place your backup file and you have two options here.
Backup to disk: here you can choose the folder and file name for your backup on local disk or share location or your registered backup devices.
A small thing to be noted here – for backup performance and storage purposes you can split your back across multiple files.
You can get faster processing backups if the files were on different physical drives.
Also, you will get smaller file sizes in case you need to fit them on a CD or DVD or you just want to make the copy across your network easier.
Also, there is something you need to know here, as well. You can view the contents of your previous backup files to decide if you want to append to it or overwrite.
For this specific backup file, you can see that I have appended two backups to the same file two backup sets with their dates and all other details.
Backup to URL: this is used when you want to store your backup file on Azure Blob Storage.
I will discuss here what you need to perform this operation:
- Create Windows Azure Storage account
- Create root container on the windows azure storage. We can generate a Shared Access Signature token on a container, and grant access to objects on a specific container only
-
On SQL server side you need to be aware of:
- URL to the unique backup file name: even if it doesn’t exist yet, you must specify the name of the backup file in the URL like “http://SQLSHACK.blob.core.windows.net/backups/SQLSHACK_20180115.bak”
- SQL server credential: this is an object that stores authentication info required to connect to a resource outside SQL Server. The credential stores either the name of the storage account and the storage account access key values or container URL, and its shared access signature token.
Summary
SQL Server backups are the core of our job. You can easily get fired because of it. It is the first thing every DBA needs to know before starting his\her career as a DBA. I tried to keep it simple when discussing the very basic details for beginners. I hope this article has been informative for you.
References
- Back up database (general page)
- Select backup destination
- Create credential – authenticate to Azure Storage
Living in Egypt, have worked as Microsoft Senior SQL Server Database Administrator for more than 4 years.
As a DBA, I design, install, maintain and upgrade all databases (production and non-production environments), I have practical knowledge of T-SQL performance, HW performance issues, SQL Server replication, clustering solutions, and database designs for different kinds of systems. I worked on all SQL Server versions (2008, 2008R2, 2012, 2014 and 2016).
I love my job as the database is the most valuable thing in every place in the world now. That's why I won't stop learning. In my spare time, I like to read, speak, learn new things and write blogs and articles.
View all posts by Ayman Elnory
- SQL Server Query Execution Plans for beginners – NON-Clustered Index Operators - April 24, 2018
- SQL Server Query Execution Plans for beginners– Clustered Index Operators - March 5, 2018
- A walk through the SQL Server 2016 full database backup - February 12, 2018